# Why VLOOKUP returns #N/A when copied down

The table range moves as you copy the formula. Lock it with `$`: `=VLOOKUP(A2,$E$2:$F$8,2,FALSE)`.

**Excel:** `=VLOOKUP(A2,$E$2:$F$8,2,FALSE)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Store | Manager |  |  | Store | Manager |
| Denver |  |  |  | Austin | Rachel Kim |
| Tulsa |  |  |  | Boise | Mark Ellis |
| Austin |  |  |  | Denver | Sofia Ruiz |
| Reno |  |  |  | Fresno | Dan Cole |
| Boise |  |  |  | Omaha | Lena Fox |
| Austin |  |  |  | Reno | Ivan Petrov |
|  |  |  |  | Tulsa | Gail Moss |

Formula in B2, filled to B7: `=VLOOKUP(A2,$E$2:$F$8,2,FALSE)`

Result: Sofia Ruiz; Gail Moss; Rachel Kim; Ivan Petrov; Mark Ellis; Rachel Kim

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

## Other ways

- `=XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)`: The same fix applies to XLOOKUP: lock both ranges.
- `=VLOOKUP(A2,E2:F8,2,FALSE)`: 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.

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

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-na-when-copied-down