Help with function, not sure which one

[excelrookie]

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.

 

[Teethless mama]

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," ",D13,"
",E1:E3),MATCH(G1,A1:A3,)))," ",", ")

 

[excelrookie]

Thank you. I have 90 rows and 12 columns so I will work on making the
formula longer, however when i tried the short version it only had 1 of the
answers instead of two and it showed that one over and over? Any thoughts?

Thanks

 

[Teethless mama]

Increase your column width

Thank you. I have 90 rows and 12 columns so I will work on making the
formula longer, however when i tried the short version it only had 1 of the
answers instead of two and it showed that one over and over? Any thoughts?

Thanks

 

[excelrookie]

I increased the column width and that is how I saw that only 1 of the 2
correct answers was repeating over and over. Any other suggestions? You
have gotten me the closest to the correct formula, it's just not working yet

Thank you