How to return all matches of a lookup in Excel
Excel formula
=FILTER(A2:A11,B2:B11=G1,"No orders")VLOOKUP and XLOOKUP stop at the first match. Use FILTER to return every one: =FILTER(A2:A11,B2:B11=G1,"No orders").
- Result on the example
- a 4-row list that spills from G2 in
G2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
How it works
FILTER keeps the rows of A2:A11 where B2:B11=G1 is TRUE and spills them down from G2, so one formula returns all four Fabrikam orders. The third argument is shown when nothing matches.
Google Sheets' FILTER treats extra arguments as more conditions, so the Sheets version uses IFERROR for the message.
=FILTER(A2:A11,B2:B11=G1,"No orders")Result{4 rows × 1 col} "SO-5102", "SO-5104", "SO-5107", "SO-5110"
A2:A1110 rows × 1 col: "SO-5101", "SO-5102", "SO-5103", "SO-5104", …A range: 10 cells in one column.
B2:B1110 rows × 1 col: "Contoso", "Fabrikam", "Northwind", "Fabrikam", …A range: 10 cells in one column.
G1"Fabrikam"The value in G1.
B2:B11=G1{10 rows × 1 col} FALSE, TRUE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
FILTER(A2:A11,B2:B11=G1,"No orders"){4 rows × 1 col} "SO-5102", "SO-5104", "SO-5107", "SO-5110"FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:A1110 rows × 1 col: "SO-5101", "SO-5102", "SO-5103", "SO-5104", … - condition1
B2:B11=G1{10 rows × 1 col} FALSE, TRUE, FALSE, TRUE, … - condition2, ...
"No orders"
- range
In Google Sheets
Google Sheets needs a different formula: =IFERROR(FILTER(A2:A11,B2:B11=G1),"No orders"). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =TEXTJOIN(", ",TRUE,FILTER(A2:A11,B2:B11=G1)) | SO-5102, SO-5104, SO-5107, SO-5110Different result | Puts all the matches in one cell, separated by commas. All the order numbers in one cell. |
| =INDEX(A2:A11,SMALL(IF(B2:B11=G1,ROW(A2:A11)-ROW(A2)+1),1)) | SO-5102Different result | For Excel 2019 and earlier: replace the 1 with ROWS($G$2:G2), fill down, and wrap in IFERROR to blank the extra rows. One match per cell: the first one here. |
Common mistakes
- The cells below the formula must be empty, or FILTER shows #SPILL!.
- FILTER needs Excel 2021, Microsoft 365 or Google Sheets.
- Return more columns by filtering a wider range:
=FILTER(A2:D11,B2:B11=G1).
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Return all matches of a lookup
G2, return every order number for the customer in G1, one per row.Questions
How do I return the matches side by side instead?
Wrap the result in TOROW: =TOROW(FILTER(A2:A11,B2:B11=G1)).
How do I return all matches for two criteria?
Multiply the tests: =FILTER(A2:A11,(B2:B11=G1)*(D2:D11>200)).
How do I get only the 2nd match?
Pick it from the list: =INDEX(FILTER(A2:A11,B2:B11=G1),2).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.