The guide · Fixing #N/A
The checklist
| Cause | How to spot it | Fix |
|---|---|---|
| A space at the end of the value | =LEN(A4) is one more than you expect | TRIM(A4) in the lookup |
| The table slid when the formula was copied | Lower rows search a table that starts lower down | Lock it: $D$2:$E$8 |
| The table stops before the last row | A newly added item is below the range | Extend the range to the end of the list |
| A number stored as text | The value sits on the left of the cell, often with a green triangle | Convert it, or look up VALUE(A2) |
| The value isn't in the first column | The table starts to the right of the lookup column | Start the table at the lookup column, or use XLOOKUP |
How to look
Click a cell that shows #N/A and look at the formula bar. Excel colors each reference and outlines the same range on the sheet. Is the item you're looking for inside the outlined table? If yes, compare the two values character by character: =A4=D5 returns FALSE when they differ by an invisible space.
Why the first row works and the others don't
A formula written in B2 with D2:E7 and filled down becomes D3:E8, D4:E9, and so on. Each row searches a smaller slice of the price list, so the items near the top of the list stop being found. The fix is dollar signs, not a bigger range.
Common mistakes
- Hiding it with IFERROR. "Not found" in every row looks tidy and is wrong. Fix the cause first; see the IFERROR challenge.
- Copying the first row's range when locking.
$D$2:$E$7still misses an item in row 8. - Retyping the data. TRIM inside the formula keeps working when the next import brings new spaces.
FAQ
- Why does VLOOKUP return #N/A when the value exists?
- The value doesn't match exactly, often because of a space at the end or a number stored as text, or the value is outside the table range, often because the range moved when the formula was copied.
- How do I remove spaces in a VLOOKUP?
- Wrap the lookup value in TRIM: =VLOOKUP(TRIM(A2),$D$2:$E$8,2,FALSE). TRIM removes spaces at the start and end and doubles in the middle.
- Why does VLOOKUP work in the first row but not when I copy it down?
- The table range isn't locked, so it moves down one row with every copy. Add dollar signs: $D$2:$E$8.
- How do I fix VLOOKUP with numbers stored as text?
- Make both sides the same type. Convert the text numbers to numbers (for example with VALUE or by multiplying by 1), or convert the lookup value with TEXT if the table holds text.