Why VLOOKUP returns #N/A when copied down
Formula (Excel and Google Sheets)
=VLOOKUP(A2,$E$2:$F$8,2,FALSE)The table range moves as you copy the formula. Lock it with $: =VLOOKUP(A2,$E$2:$F$8,2,FALSE).
- Result on the example
- one result per row in
B2, filled down toB7 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.
How it works
A relative range like E2:F8 shifts one row each time the formula is copied down: in B4 it becomes E4:F10, which no longer includes Austin, so the lookup fails. The $ signs in $E$2:$F$8 make the table absolute, while A2 stays relative so each row looks up its own store.
=VLOOKUP(A2,$E$2:$F$8,2,FALSE)Result"Sofia Ruiz"
A2"Denver"The value in A2.
$E$2:$F$87 rows × 2 cols: "Austin", "Rachel Kim", "Boise", "Mark Ellis", …A range: 7 rows × 2 columns.
VLOOKUP(A2,$E$2:$F$8,2,FALSE)"Sofia Ruiz"VLOOKUP: Finds a value in the first column and returns a value from the same row. Use FALSE for exact.- lookup_value
A2"Denver" - table_array
$E$2:$F$87 rows × 2 cols: "Austin", "Rachel Kim", "Boise", "Mark Ellis", … - col_index_num
2 - range_lookup
FALSE
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =VLOOKUP(A2,$E$2:$F$8,2,FALSE). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8) | Sofia Ruiz, Gail Moss, Rachel Kim… | The same fix applies to XLOOKUP: lock both ranges. |
| =VLOOKUP(A2,E2:F8,2,FALSE) | Sofia Ruiz, Gail Moss, #N/A…Different result | Shown for contrast: the unlocked range, filled down. #N/A in B4, B6 and B7, where the shifted range has slid past the store. |
Common mistakes
- Press F4 after selecting a reference in the formula bar to add the
$signs. - Locking the lookup value too (
$A$2) makes every row look up the first store. Only the table should be locked. - Whole columns,
$E:$F, or an Excel table name avoid the problem and pick up new rows.
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.
Why VLOOKUP returns #N/A when copied down
B2, return the manager of the store in A2 from the table in E2:F8, written so it still works when you fill it down to B7.Questions
Why does it work in the first row only?
The first row uses the range you typed. Every row below uses a shifted copy of it.
Do I need $ in Google Sheets too?
Yes. Google Sheets shifts relative references the same way.
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.