# How to look up a list of values at once in Excel

Give XLOOKUP the whole list as its lookup value: `=XLOOKUP(E2:E6,A2:A9,C2:C9,"Not found")` returns one result per item and spills them down.

**Excel:** `=XLOOKUP(E2:E6,A2:A9,C2:C9,"Not found")` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=ARRAYFORMULA(IFNA(VLOOKUP(E2:E6,A2:C9,3,FALSE),"Not found"))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| SKU | Product | Price |  | Order line | Price |
| P-101 | Desk lamp | 39.99 |  | P-104 |  |
| P-102 | Office chair | 249 |  | P-101 |  |
| P-103 | Monitor arm | 89.5 |  | P-110 |  |
| P-104 | Standing desk | 449 |  | P-107 |  |
| P-105 | Keyboard | 74.95 |  | P-104 |  |
| P-106 | Webcam | 59 |  |  |  |
| P-107 | Headset | 129 |  |  |  |
| P-108 | Laptop stand | 45 |  |  |  |

Formula in F2: `=XLOOKUP(E2:E6,A2:A9,C2:C9,"Not found")`

Result (spills 5 × 1): $449.00; $39.99; Not found; $129.00; $449.00

## How it works

When the lookup value is a range, XLOOKUP runs once for each item and returns a column of results that spills down beside the order lines. The fourth argument covers the unknown SKU, P-110, so one bad code doesn't break the rest. Google Sheets needs ARRAYFORMULA around the lookup to return more than one result.

## Other ways

- `=IFNA(VLOOKUP(E2:E6,A2:C9,3,FALSE),"Not found")`: VLOOKUP spills the same way in Excel 2021 and later.
- `=IFNA(INDEX(C2:C9,MATCH(E2:E6,A2:A9,0)),"Not found")`: INDEX and MATCH with a list of lookup values, for those who prefer it.

## Common mistakes

- Writing `=XLOOKUP(E2,A2:A9,C2:C9)` and filling down also works, but a range in the lookup value means one formula to maintain.
- Locking the lookup range with $ is not needed in the spilled version, since nothing is copied. It still is in the fill-down version.
- In Excel 2019 and earlier, a range as the lookup value returns one result per cell only when entered as an array formula; fill the single-cell formula down instead.

## Questions

**How do I return two columns for each item?**

Widen the return range: `=XLOOKUP(E2:E6,A2:A9,B2:C9)` spills the product and the price.

**How do I total the order instead?**

Wrap it in SUM: `=SUM(XLOOKUP(E2:E6,A2:A9,C2:C9,0))`, with 0 for unknown SKUs.

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/look-up-a-list-of-values-at-once