lookup with multiple responses

[cadwaja]

I'm trying to find a way to have a formula return the name of a person where
each of them is assigned to the same activity but my excel hasn't recognized
vlookups and I want it automated and the formulas behind are way too complex
for advanced searches

Example:
bob painting
jane gym
fred gym
susy math
george math
peter gym
michelle painting
sarah math

I want to search painting or gym or math and have it return
painting bob
michelle
gym jane
fred
peter
math susy
george
sarah

Thoughts?

 

[Jacob Skaria]

With your data in Sheet1 ColA/ColB..

In Sheet2 cell A1 enter the activity

In cell B1 apply the below formula and copy down as required...

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

=IF(COUNTIF(Sheet1!$B$1:$B$1000,$A$1)<ROW(A1),"",
INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$B$1:$B$1000=$A$1,
ROW($A$1:$A$1000)),ROW(A1))))

 

[Tom Hutchins]

Jacob gave you a beautiful formula which answered your question. I just
wanted to mention that you could easily get output like your example for all
activities at once by creating a pivot table with Activity as the first row
field and Name as the second row field (no column or data fields).

Hutch