Help with function, not sure which one

  • Thread starter Thread starter excelrookie
  • Start date Start date
E

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.
 
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,)))," ",", ")
 
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
 
Increase your column width


excelrookie said:
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
Click to expand...
 
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
 
Back
Top