VLOOKUP can fail with #N/A, #REF, #VALUE, or a wrong result even when the data looks right. Most problems come from lookup range position, mismatched data types, hidden spaces, or a missing exact-match argument.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, Excel 2019, Mac, or Excel Online when VLOOKUP results do not match what you expect.
Fix a #N/A error
#N/A usually means Excel could not find an exact match in the first column of the lookup range.
- Remove extra spaces with TRIM, such as =VLOOKUP(TRIM(A2),B:D,2,FALSE).
- Make sure text and numbers match. A number stored as text will not match a true number. Use Data > Text to Columns > Finish to convert many text-numbers quickly.
- Confirm the lookup value is in the leftmost column of the table array. VLOOKUP cannot search a column to the right and return a column to the left.
- Add FALSE or 0 as the last argument for exact match. Leaving it blank makes Excel use approximate matching.
Fix #REF, #VALUE, or wrong results
These point to formula structure rather than a missing value.
- #REF: the column index number is larger than the number of columns in the lookup range.
- #VALUE: the column index is less than 1 or is not a whole number.
- Wrong result: the exact-match argument is probably missing, so Excel is returning the nearest approximate match.
- Copied formulas break lower down: lock the lookup range with dollar signs, such as $B$2:$D$100.
More control
VLOOKUP is not case-sensitive
Smith and smith are treated as the same value. For case-sensitive matching, use EXACT with INDEX/MATCH.
Wildcards can help partial matches
With exact match set to FALSE? No: use FALSE for exact-match mode with wildcards, such as =VLOOKUP(“Smi*”,B:D,2,FALSE), to find the first value beginning with Smi.
Use INDEX/MATCH or XLOOKUP for fewer limits
INDEX/MATCH can look left or right. Microsoft 365 users can often replace VLOOKUP with XLOOKUP, such as =XLOOKUP(A2,B:B,D:D,”Not found”).
Sources
- Microsoft Support – VLOOKUP function (2025)
- Microsoft Support – How to correct a #N/A error (2025)
- Microsoft Support – VLOOKUP examples (2024)