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

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.

**Excel:** `=IF(A2:A8="","",XLOOKUP(A2:A8,E2:E7,F2:F7,"Not found"))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=ARRAYFORMULA(IF(A2:A8="","",IFERROR(VLOOKUP(A2:A8,E2:F7,2,FALSE),"Not found")))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| SKU | Price |  |  | SKU | Price |
| P-104 |  |  |  | P-101 | 39.99 |
| P-101 |  |  |  | P-102 | 249 |
| P-110 |  |  |  | P-103 | 89.5 |
| P-106 |  |  |  | P-104 | 449 |
|  |  |  |  | P-105 | 74.95 |
| P-102 |  |  |  | P-106 | 59 |
| P-104 |  |  |  |  |  |

Formula in B2: `=IF(A2:A8="","",XLOOKUP(A2:A8,E2:E7,F2:F7,"Not found"))`

Result (spills 7 × 1): $449.00; $39.99; Not found; $59.00; ; $249.00; $449.00

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

## Other ways

- `=XLOOKUP(A2:A8,E2:E7,F2:F7,"Not found")`: Without the IF, blank rows show "Not found" too. (The empty row shows "Not found".)
- `=VLOOKUP(A2:A8,E2:F7,2,FALSE)`: 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 `B2` must be empty, or the formula shows #SPILL!.
- Don't use a whole-column lookup range like `A:A` as the lookup value: it returns a million results and is slow. Use the rows you have, or a table.

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

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/lookup-whole-column-at-once