The VLOOKUP + $ guide
=VLOOKUP(B2, $F$2:$G$7, 2, FALSE) filled down a column: the code in B2 moves to B3, B4 and so on, and the table stays where it is because of the $ signs. Without them, the table slides down one row per row, and the top of the list drops out.
Why the first rows work and the rest don't
Fill =VLOOKUP(B2, F2:G7, 2, FALSE) down and Excel moves every reference one row per row. In D5 the formula reads VLOOKUP(B5, F5:G10, 2, FALSE). The code in B5 is right, but the price list now starts at row 5. Any product listed in rows 2 to 4 is out of reach, so it gives #N/A. The rows near the top still find their codes, which is why the mistake often gets through: the sheet looks half right.
Lock the table, not the lookup value
The table should stay put and the lookup value should move. So the table gets $ before the column and the row, and the lookup value gets none:
=VLOOKUP(B2, $F$2:$G$7, 2, FALSE)
Type the range, then press F4 (⌘+T in Excel for Mac) to add the $ signs. Pressing it again cycles through F$2, $F2 and back to F2. Lock it with $ covers how the $ works.
Half-locked ranges
$F2:$G7locks the columns only. Filled down, the rows still move, so it fails just like F2:G7.F$2:G$7locks the rows only. Filled down, that's enough. Filled right, it would move.$B$2as the lookup value locks the wrong thing: every row looks up the first order's code and shows the same price.
Whole columns
In Excel, =VLOOKUP(B2, F:G, 2, FALSE) also works when filled down: a whole column can't slide. It's handy when the price list grows. The practice sheet here needs the cell range.
Common mistakes
- #N/A from a certain row down. The table isn't locked. Click a cell lower down and look at its range.
- Every row shows the same answer. The lookup value is locked too.
- Leaving out FALSE. VLOOKUP then assumes the codes are sorted, and on an unsorted list it returns wrong prices without an error.
- A table that stops short. A range that ends a row early leaves the last product out.
Single lookups first: VLOOKUP practice. When a lookup still says #N/A: the #N/A mistake hunt.
FAQ
- Why does my VLOOKUP work in the first row but not when I drag it down?
- The table range moves one row for every row you fill. Lock it with $, for example $F$2:$G$7, so every row searches the whole table.
- How do I lock the table array in VLOOKUP?
- Select the range inside the formula and press F4 (Command+T in Excel for Mac). Excel adds a $ before each column letter and row number.
- Should the lookup value have $ signs too?
- No. The lookup value should move down with each row so that each row looks up its own code. With $B$2, every row returns the first row's answer.
- Can I use whole columns in VLOOKUP?
- Yes. =VLOOKUP(B2,F:G,2,FALSE) works in Excel and doesn't move when filled down, because a whole column can't slide.



































































