# How to use VLOOKUP with ARRAYFORMULA in Google Sheets

Give VLOOKUP the whole column of lookup values inside ARRAYFORMULA: `=ARRAYFORMULA(IF(B2:B9="","",IFNA(VLOOKUP(B2:B9,E2:F6,2,FALSE),"Not found")))`.

**Google Sheets:** `=ARRAYFORMULA(IF(B2:B9="","",IFNA(VLOOKUP(B2:B9,E2:F6,2,FALSE),"Not found")))`

**Excel:** In Excel 2021 and later, give XLOOKUP the range of SKUs and it spills: `=XLOOKUP(B2:B7,E2:E6,F2:F6,"Not found")`.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Order | SKU | Price |  | SKU | Price |
| SO-501 | P-104 |  |  | P-101 | 39.99 |
| SO-502 | P-101 |  |  | P-102 | 249 |
| SO-503 | P-110 |  |  | P-103 | 89.5 |
| SO-504 | P-103 |  |  | P-104 | 449 |
| SO-505 | P-104 |  |  | P-105 | 74.95 |
| SO-506 | P-102 |  |  |  |  |
|  |  |  |  |  |  |
|  |  |  |  |  |  |

Formula in C2: `=ARRAYFORMULA(IF(B2:B9="","",IFNA(VLOOKUP(B2:B9,E2:F6,2,FALSE),"Not found")))`

Result (spills 8 × 1): $449.00; $39.99; Not found; $89.50; $449.00; $249.00; ; 

## How it works

Inside ARRAYFORMULA, VLOOKUP runs once for each SKU in `B2:B9` and returns a column of prices. IFNA replaces the #N/A of the unknown P-110 with a message, and the outer IF keeps the empty rows blank instead of showing "Not found" for them.

## Other ways

- `=ARRAYFORMULA(IFNA(VLOOKUP(B2:B9,E2:F6,2,FALSE),"Not found"))`: Without the blank test, the empty rows show the message too. (Not found on the two empty rows.)
- `=IF(B2:B9="","",XLOOKUP(B2:B9,E2:E6,F2:F6,"Not found"))`: The Excel version with XLOOKUP, which spills on its own.

## Common mistakes

- Without ARRAYFORMULA, a range as the first argument returns only one price.
- The lookup table range must stay fixed, `E2:F6`, not an open range that overlaps the result column.
- IFERROR instead of IFNA also hides a wrong column number (#REF!), which you'd want to see.

## Questions

**How do I return two columns per lookup?**

Give VLOOKUP an array of column numbers: `=ARRAYFORMULA(VLOOKUP(B2:B9,E2:G6,{2,3},FALSE))`.

**Can I use an open range like B2:B?**

Yes, with the blank test so the empty rows stay empty: `=ARRAYFORMULA(IF(B2:B="","",VLOOKUP(B2:B,E2:F6,2,FALSE)))`.

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/vlookup-with-arrayformula