Lookup
Return Multiple Results from Lookup
Return multiple matching results from a lookup, not just the first match. Essential when one lookup value has multiple corresponding entries.
Excel Formula
=FILTER(B2:B100, A2:A100=D2)Step-by-Step Explanation
1
FILTER returns all rows that match a condition
2
B2:B100 is the column of values to return
3
A2:A100=D2 is the condition to match
4
Returns an array of all matching values
5
Results spill into multiple cells automatically
Example
| Department (A) | Employee (B) |
|---|---|
| Sales | John |
| Marketing | Sarah |
| Sales | Mike |
| Sales | Lisa |
Result: =FILTER(B2:B5,A2:A5="Sales") → John, Mike, Lisa (in separate cells)
Common Variations
Multiple criteria
=FILTER(C2:C100,(A2:A100=F2)*(B2:B100=G2))Filter with AND conditions
OR criteria
=FILTER(B2:B100,(A2:A100="Sales")+(A2:A100="Marketing"))Match either condition
With TEXTJOIN
=TEXTJOIN(", ",TRUE,FILTER(B2:B100,A2:A100=D2))All results in one cell
Need a Custom Version?
Use our AI generator to create a formula tailored to your specific data and requirements.
Try It with AI →