# How to fix VLOOKUP #N/A errors after filling the formula down

Lock the table with $ before you fill: =VLOOKUP(A2,$D$2:$E$6,2,FALSE). Without it, the table range moves down with every row.

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

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

## Example

Sample data (row 1 is the header):

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

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

Result: $89.50; $39.99; $449.00; $249.00; $39.99; $74.95

## How it works

Filled down without $, the table `D2:E6` becomes `D3:E7` in the next row, then `D4:E8`, and rows drop off the top. P-101 sits in row 2, so from the second order on it is outside the shifted range and the lookup returns #N/A. `$D$2:$E$6` stays put while `A2` moves to each order.

## Other ways

- `=XLOOKUP(A2,$D$2:$D$6,$E$2:$E$6)`: XLOOKUP needs the same $ signs on both of its ranges.
- `=VLOOKUP(A2,$D:$E,2,FALSE)`: Whole columns can't slide off the table, and they take in new rows.

## Common mistakes

- Press F4 with the range selected in the formula bar to add the $ signs.
- Lock the table, not the lookup value: `$A$2` would look up the first order on every row.
- Inside an Excel Table, structured references such as `Prices[SKU]` don't shift at all.

## Questions

**Why does the first row work but not the rest?**

The first row has the right range. Each later row has a range shifted one more row down.

**What do the $ signs do?**

A $ before the column letter or the row number stops that part from changing when the formula is copied.

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-after-filling-down