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$10 00=$A$1,
ROW($A$1:$A$1000)),ROW(A1))))

--
Jacob (MVP - Excel)


"cadwaja" wrote:

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?