Hi all, first off thanks in advance for your help
I have a situation where I would like to return multiple values based on a certain criteria. Here is the table below:
C D E F
2: Sam David Frank
3: Mango X
4: Apple X X
5: Pear X
6: Grapes X X X
I have a worksheet which has 1 column with values Mango, Apple, Mango, Grapes, Pear, Apple etc. in separate rows. Based on value in that column, I should be able to look up that value in above table and whichever column for that value has "X", I need to extract the names (Sam, David, Frank).
Example: If I select "Apple", my output should be Sam and David. If I select "Grapes", my output should be Sam, David, Frank
So far I am able to only come up with the below which is sort of hard coded to select values for "Apple" that is $D$4:$F$4. Is there a way to get rid of the hard coding and automate it?
=INDEX($D$2:$F$2,SMALL(IF($D$4:$F$4="X",COLUMN($D$2:$F$2)-COLUMN($D$2)+1),COLUMNS($D2:D2)))
Bookmarks