Get a list of multiple names that match a single criteria in another row

**labtech** · 09-01-2012, 03:15 PM

Looking for a formula for this example below :

A B C D
1 JOHN JACK TOM JANE
2 red blue yellow red

From this table want to locate and display in box A3 the names who like "red" seperated by a comma.
So the answer would look like below

A
3 JOHN,JANE

Can someone help me with a formula to do this ?

Thanks.

**Khalid Maqbool** · 09-01-2012, 05:43 PM

Hi, Here is some help on your question
Regards
Khalid Maqbool

**labtech** · 09-01-2012, 07:43 PM

Thanks Khalid that appears to work.

My only issue is the formula is very long.To long to use in a bigger application over 700 cells and 30 workbooks
which is what i need it for.

I have done this before in Google Docs using JOIN and FILTER and the formula was very small and worked perfectly.

But now i need to change from Google Docs to Excel and the above keywords are not avaialble within Excel Formulas.
I did try to Import my Google Doc to Excel but this particular formula did not translate to Excel.

Can anyone else provide a shorter solution, i have tried playing with VLOOKUP but would not work.

Any help would be appreciated.

Labtech