Multiple LOOKUP in EXCEL



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.


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:

Put in B6:
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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question