How to look up a whole column of values at once in Excel
Excel formula
=IF(A2:A8="","",XLOOKUP(A2:A8,E2:E7,F2:F7,"Not found"))Give XLOOKUP the whole column of lookup values and it returns one result per row: =IF(A2:A8="","",XLOOKUP(A2:A8,E2:E7,F2:F7,"Not found")). In Google Sheets, use ARRAYFORMULA with VLOOKUP.
- Result on the example
- a 7-row list that spills from B2 in
B2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in B2. Change any input and the result updates, or edit the formula to experiment.
How it works
When the lookup value is a range, A2:A8, XLOOKUP runs once per cell and the seven results spill down column B. The IF around it returns "" for empty rows, so blank lines in the order don't show "Not found".
Google Sheets needs ARRAYFORMULA to repeat a function down a range; VLOOKUP with IFERROR is the usual pattern there.
=IF(A2:A8="","",XLOOKUP(A2:A8,E2:E7,F2:F7,"Not found"))Result{7 rows × 1 col} 449, 39.99, "Not found", 59, …
A2:A87 rows × 1 col: "P-104", "P-101", "P-110", "P-106", …A range: 7 cells in one column.
A2:A8=""{7 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
E2:E76 rows × 1 col: "P-101", "P-102", "P-103", "P-104", …A range: 6 cells in one column.
F2:F76 rows × 1 col: $39.99, $249.00, $89.50, $449.00, …A range: 6 cells in one column.
XLOOKUP(A2:A8,E2:E7,F2:F7,"Not found"){7 rows × 1 col} 449, 39.99, "Not found", 59, …XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
A2:A87 rows × 1 col: "P-104", "P-101", "P-110", "P-106", … - lookup_array
E2:E76 rows × 1 col: "P-101", "P-102", "P-103", "P-104", … - return_array
F2:F76 rows × 1 col: $39.99, $249.00, $89.50, $449.00, … - if_not_found
"Not found"
- lookup_value
IF(A2:A8="","",XLOOKUP(A2:A8,E2:E7,F2:F7,"Not found")){7 rows × 1 col} 449, 39.99, "Not found", 59, …IF: Returns one value if a test is true, another if false.- logical_test
A2:A8=""{7 rows × 1 col} FALSE, FALSE, FALSE, FALSE, … - value_if_true
"" - value_if_false
XLOOKUP(A2:A8,E2:E7,F2:F7,"Not found"){7 rows × 1 col} 449, 39.99, "Not found", 59, …
- logical_test
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(IF(A2:A8="","",IFERROR(VLOOKUP(A2:A8,E2:F7,2,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 |
|---|---|---|
| =XLOOKUP(A2:A8,E2:E7,F2:F7,"Not found") | spills 7 × 1Different result | Without the IF, blank rows show "Not found" too. The empty row shows "Not found". |
| =VLOOKUP(A2:A8,E2:F7,2,FALSE) | spills 7 × 1Different result | VLOOKUP spills the same way in Excel 2021 and Microsoft 365, with #N/A for anything missing. #N/A for P-110 and the empty row. |
Common mistakes
- The cells below
B2must be empty, or the formula shows #SPILL!. - Don't use a whole-column lookup range like
A:Aas the lookup value: it returns a million results and is slow. Use the rows you have, or a table.
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 whole column of values at once
B2, write one formula that returns the price for every SKU in A2:A8, leaves blank rows blank, and shows "Not found" for unknown SKUs.Questions
How do I do this in Excel 2019?
Write =IFERROR(VLOOKUP(A2,$E$2:$F$7,2,FALSE),"Not found") in B2 and fill down.
How do I make it grow with new rows?
Turn the data into an Excel table, or in Google Sheets use an open range like A2:A.
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.