How to Fix #N/A Errors in VLOOKUP and XLOOKUP
TL;DR: #N/A means a lookup couldn't find the value. The usual causes are extra spaces, numbers stored as text or the wrong range. Fix the data first, then wrap the formula in IFNA or IFERROR to show a friendly message.
#N/A means the lookup value was not found. Check these causes in order.
- Extra spaces: wrap with TRIM:
=VLOOKUP(TRIM(E2), A:C, 3, FALSE) - Numbers stored as text: one side is "123" and the other is 123. Convert with VALUE() or multiply by 1, or use Data > Text to Columns.
- Approximate match by accident: missing the FALSE argument in VLOOKUP.
- Range not locked: when copying down, use $A$2:$C$100 so the table does not shift.
- Value really missing: show a friendly message instead:
=IFNA(VLOOKUP(E2, $A$2:$C$100, 3, FALSE), "Not found")- Hidden characters from web or system exports: use CLEAN() together with TRIM().
The same fixes apply to INDEX MATCH lookups.