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

"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?