How to look up a list of values at once in Excel
Excel formula
=XLOOKUP(E2:E6,A2:A9,C2:C9,"Not found")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.
- Result on the example
- a 5-row list that spills from F2 in
F2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
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.
=XLOOKUP(E2:E6,A2:A9,C2:C9,"Not found")Result{5 rows × 1 col} 449, 39.99, "Not found", 129, …
E2:E65 rows × 1 col: "P-104", "P-101", "P-110", "P-107", …A range: 5 cells in one column.
A2:A98 rows × 1 col: "P-101", "P-102", "P-103", "P-104", …A range: 8 cells in one column.
C2:C98 rows × 1 col: $39.99, $249.00, $89.50, $449.00, …A range: 8 cells in one column.
XLOOKUP(E2:E6,A2:A9,C2:C9,"Not found"){5 rows × 1 col} 449, 39.99, "Not found", 129, …XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
E2:E65 rows × 1 col: "P-104", "P-101", "P-110", "P-107", … - lookup_array
A2:A98 rows × 1 col: "P-101", "P-102", "P-103", "P-104", … - return_array
C2:C98 rows × 1 col: $39.99, $249.00, $89.50, $449.00, … - if_not_found
"Not found"
- lookup_value
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(IFNA(VLOOKUP(E2:E6,A2:C9,3,FALSE),"Not found")). 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 |
|---|---|---|
| =IFNA(VLOOKUP(E2:E6,A2:C9,3,FALSE),"Not found") | spills 5 × 1 | VLOOKUP spills the same way in Excel 2021 and later. |
| =IFNA(INDEX(C2:C9,MATCH(E2:E6,A2:A9,0)),"Not found") | spills 5 × 1 | 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.
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.
Look up a list of values at once
F2, write one formula that returns the price of every SKU in E2:E6, with "Not found" for SKUs that aren't in the price list.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.
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.