# How to return all matches of a lookup in Excel

VLOOKUP and XLOOKUP stop at the first match. Use FILTER to return every one: `=FILTER(A2:A11,B2:B11=G1,"No orders")`.

**Excel:** `=FILTER(A2:A11,B2:B11=G1,"No orders")` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=IFERROR(FILTER(A2:A11,B2:B11=G1),"No orders")`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Order | Customer | Product | Amount |  | Customer | Fabrikam |
| SO-5101 | Contoso | Desk lamp | 120 |  | Orders |  |
| SO-5102 | Fabrikam | Office chair | 498 |  |  |  |
| SO-5103 | Northwind | Monitor arm | 179 |  |  |  |
| SO-5104 | Fabrikam | Standing desk | 898 |  |  |  |
| SO-5105 | Contoso | Webcam | 118 |  |  |  |
| SO-5106 | Litware | Keyboard | 225 |  |  |  |
| SO-5107 | Fabrikam | Headset | 258 |  |  |  |
| SO-5108 | Northwind | Desk lamp | 80 |  |  |  |
| SO-5109 | Contoso | Laptop stand | 90 |  |  |  |
| SO-5110 | Fabrikam | Webcam | 59 |  |  |  |

Formula in G2: `=FILTER(A2:A11,B2:B11=G1,"No orders")`

Result (spills 4 × 1): SO-5102; SO-5104; SO-5107; SO-5110

## 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.

## Other ways

- `=TEXTJOIN(", ",TRUE,FILTER(A2:A11,B2:B11=G1))`: 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))`: 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)`.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/return-all-matches-of-a-lookup