How to use VLOOKUP with ARRAYFORMULA in Google Sheets
Google Sheets formula
=ARRAYFORMULA(IF(B2:B9="","",IFNA(VLOOKUP(B2:B9,E2:F6,2,FALSE),"Not found")))Give VLOOKUP the whole column of lookup values inside ARRAYFORMULA: =ARRAYFORMULA(IF(B2:B9="","",IFNA(VLOOKUP(B2:B9,E2:F6,2,FALSE),"Not found"))).
- Result on the example
- a 8-row list that spills from C2 in
C2 - Excel
- See below
See it work
The formula is already in C2. Change any input and the result updates, or edit the formula to experiment.
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.
=ARRAYFORMULA(IF(B2:B9="","",IFNA(VLOOKUP(B2:B9,E2:F6,2,FALSE),"Not found")))Result{8 rows × 1 col} 449, 39.99, "Not found", 89.5, …
B2:B98 rows × 1 col: "P-104", "P-101", "P-110", "P-103", …A range: 8 cells in one column.
B2:B9=""{8 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
E2:F65 rows × 2 cols: "P-101", $39.99, "P-102", $249.00, …A range: 5 rows × 2 columns.
VLOOKUP(B2:B9,E2:F6,2,FALSE){8 rows × 1 col} 449, 39.99, #N/A, 89.5, …VLOOKUP: Finds a value in the first column and returns a value from the same row. Use FALSE for exact.- lookup_value
B2:B98 rows × 1 col: "P-104", "P-101", "P-110", "P-103", … - table_array
E2:F65 rows × 2 cols: "P-101", $39.99, "P-102", $249.00, … - col_index_num
2 - range_lookup
FALSE
- lookup_value
IFNA(VLOOKUP(B2:B9,E2:F6,2,FALSE),"Not found"){8 rows × 1 col} 449, 39.99, "Not found", 89.5, …IFNA: Returns a fallback only for #N/A.- value
VLOOKUP(B2:B9,E2:F6,2,FALSE){8 rows × 1 col} 449, 39.99, #N/A, 89.5, … - value_if_na
"Not found"not used
- value
IF(B2:B9="","",IFNA(VLOOKUP(B2:B9,E2:F6,2,FALSE),"Not found")){8 rows × 1 col} 449, 39.99, "Not found", 89.5, …IF: Returns one value if a test is true, another if false.- logical_test
B2:B9=""{8 rows × 1 col} FALSE, FALSE, FALSE, FALSE, … - value_if_true
"" - value_if_false
IFNA(VLOOKUP(B2:B9,E2:F6,2,FALSE),"Not found"){8 rows × 1 col} 449, 39.99, "Not found", 89.5, …
- logical_test
ARRAYFORMULA(IF(B2:B9="","",IFNA(VLOOKUP(B2:B9,E2:F6,2,FALSE),"Not found"))){8 rows × 1 col} 449, 39.99, "Not found", 89.5, …ARRAYFORMULA: Runs a formula over whole ranges and returns all the results.- array_formula
IF(B2:B9="","",IFNA(VLOOKUP(B2:B9,E2:F6,2,FALSE),"Not found")){8 rows × 1 col} 449, 39.99, "Not found", 89.5, …
- array_formula
In Excel
=XLOOKUP(B2:B7,E2:E6,F2:F6,"Not found").Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =ARRAYFORMULA(IFNA(VLOOKUP(B2:B9,E2:F6,2,FALSE),"Not found")) | spills 8 × 1Different result | 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"))Excel only | spills 8 × 1 | 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.
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.
Use VLOOKUP with ARRAYFORMULA
C2, write one formula that looks up the price of every SKU in B2:B9 from the table in E2:F6, shows "Not found" for unknown SKUs and leaves empty rows blank.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))).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.