0

This is partly for fun, and I hope it doesn't get closed on subjectivity grounds because although "which solution is the most elegant" is subjective, "the current solution is convoluted and ugly" is plain for all to see.

Anyway, I have a list in sheet1 like so:

*Thing*            *type of thing*
Apple
Orange
Larceny
Cake
Banana
Murder

and in sheet 2 like this:

              fruit    confectionary      crime
Apple           x                                
Orange          x                                
Larceny                                     x    
Cake                         x                   
Banana          x                                
Murder                                      x   

and I want to to return col B in Sheet 1 like so

*Thing*               *type of thing*                                   
Apple                 fruit                        
Orange                fruit                        
Larceny               crime                        
Cake                  confectinary               
Banana                fruit                        
Murder                crime                        

I will post my current solution as an answer. It works, I'll give it that...

How would you approach this problem?

2

Here is my solution. Index+match can be used to look up values in a table both ways. This tweaks the normal solution so it will find the x and return the column header.

=INDEX($E$1:$G$1,MATCH("x",OFFSET($E$1:$G$1,MATCH(A2,$D$2:$D$7,0),),0))

enter image description here

I put everything on one sheet so it is easier to see and also makes the function a bit shorter.

As per Barry's comment below a better option would be to avoid the offset with another index function so it is not volatile.

=INDEX($E$1:$G$1,MATCH("x",INDEX($E$2:$G$7,MATCH(A2,$D$2:$D$7,0),0),0))
| improve this answer | |
  • Very good solution!. Also though in my example there was a 1 to 1 correspondence to the lists, in the real example there wasn't. Fortunately, you were smart enough to write a formula that doesn't depend on this :) – Some_Guy May 28 '15 at 17:04
  • As a side note, I'm relatively new here. Any idea why my question wasn't received so well? – Some_Guy May 28 '15 at 17:05
  • I would guess it is probably that the work wasn't shown in the question. People are expected to show work and normally it is in the original post. Sometimes they will add their own answer if they figure it out later or by working with people on the forum. It was just one person though. Maybe it was the part about subjective analysis. Anyway I thought it was fine. – gtwebb May 28 '15 at 17:46
  • 2
    You can avoid volatile OFFSET by using another INDEX function, e.g. =INDEX($E$1:$G$1,MATCH("x",INDEX($E$2:$G$7,MATCH(A2,$D$2:$D$7,0),0),0)) – barry houdini May 28 '15 at 20:14
  • gtwebb@ I'll bare that in mind. I was going to do that in mind but superuser suggested I answer my own question in a different box on same form where I submitted it , and I thought, okay, a bit weird but fine... Thanks for the great contributions everyone :) – Some_Guy May 28 '15 at 21:50
0

For my current solution I have

=MATCH(A1,Sheet1!A1:A10,0)

in column B, returning the row containing the thing in sheet2. So far so good. But to then return the position of the the x within that row in sheet 2, and give the correct word, I have this monstrosity, using an R1C1 referencing concatenated string inside an indirect function to convert the row number into a reference I can actually use:

=IF(INDIRECT("sheet2!R"&B1&"C2",0)="X","fruit",IF(INDIRECT("sheet2!R"&B1&"C3",0)="X","confectionary",IF(INDIRECT("sheet2!R"&B1&"C4",0)="X","crime","ERROR")))

The string then evaluates to "sheet2R[rownumber]C[column]", which feeds to the indirect (converts to a normal reference) which then goes to the if statement to covert the presence of the x into the relevant word

| improve this answer | |
0

Here's my preferred solution, similar concept to gtwebb's (and assuming your data is aligned as in his screenshot) but it doesn't use OFFSET (which I believe should only be used when there are no other options because it's volatile).

=INDEX($E$1:$G$1,MATCH("x",$E2:$G2,0))

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.