Assuming your data in A1:E3 and the person name in "one word"

Criteria in G1

In H1: =SUBSTITUTE(TRIM(INDEX(CONCATENATE(B1:B3," ",C1:C3," ",D1:D3,"
",E1:E3),MATCH(G1,A1:A3,)))," ",", ")



"excelrookie" wrote:

I don't want to use a macro...is it possible to do the following with
functions?

New York Joe Tim Kim Jane
San Diego Tim Jane
St Louis Joe Tim Kim

If I enter New York I want excel to return Joe, Tim, Kim, Jane in a cell by
looking at the table above which will be in the same worksheet
If I enter St Louis I want it to Return Joe, Tim, Kim in a cell by looking
at the above
etc.

Thanks so much I am having a hard time deciding where to start.