Multiple LOOKUP in EXCEL

[drdegn]

I need to lookup multiple values in Excel, but LOOKUP only returns one value.
See my example where 3 persons perform activity "D":

Column: A B C D E F G H
Row 1: Person: JB JA OA EB AM CJ TG
Row 2: Activity: F D A D K D S

Question: Search for 'D' in row 2
Answer: Result from row 1: JA, EB, CJ

But how can I ask Excel to find these 3 persons, performing activity 'D'
I prefer the result in one cell separated by commas.

 

[Max]

Here's one play to achieve the "horizontal" filter ..

Source table as posted in rows 1 & 2, data from col B across
Assume the desired activity letter will be input in A5: D (say)
Put in B5:
=IF(COLUMNS($A:A)>COUNT(6:6),"",INDEX(1:1,SMALL(6:6,COLUMNS($A:A))))

Put in B6:
=IF($A$5="","",IF(B2=$A$5,COLUMN(),""))
Leave A6 empty. Select B5:B6, copy across to IV6. In B5 across will appear
the required results from row 1, viz.: JA, EB, CJ ... with all results neatly
bunched to the left.