VLOOKUP almost always fails for one of a handful of reasons: an approximate match, a text-vs-number mismatch, hidden spaces, an unlocked range, the wrong column number, or the lookup value not being in the first column. Work down this checklist — or paste the broken formula into ExcelGPT and ask "why isn't this working?" to get the fix instantly.
Do it with AI insteadForce an exact match
Most #N/A errors come from a missing last argument. End the formula with , FALSE) (or 0) so VLOOKUP stops doing approximate matches.
Fix text-vs-number mismatches
If the key is a number on one side but text on the other, VLOOKUP won't match. Convert with VALUE() / TEXT(), or use Data ▸ Text to Columns to clean the column.
Strip hidden spaces
Leading/trailing spaces break matches. Wrap the lookup value in TRIM: =VLOOKUP(TRIM(A2), …).
Lock the table range
When you fill the formula down, an unlocked range shifts. Press F4 to make it absolute: $A$2:$D$100.
Check the column number
col_index_num counts from the table's first column, not the sheet's. A number bigger than the table width returns #REF!.
Make sure the key is in the first column
VLOOKUP only searches the first column of the range and looks right. If your key is to the right of the answer, switch to =XLOOKUP(A2, B:B, C:C) or INDEX/MATCH.
=IFNA(VLOOKUP(TRIM(A2), $A$2:$D$100, 4, FALSE), "Not found")Almost always a hidden mismatch: extra spaces, a number stored as text, or an approximate match. Try =VLOOKUP(TRIM(A2), range, n, FALSE) and make sure both sides are the same data type.
Either the last argument is TRUE/omitted (approximate match) or the table range isn't locked and shifted when you filled down. Add FALSE and press F4 to lock the range.
The range isn't absolute — as you fill down it moves below your data. Lock it with $ (F4) so every row searches the full table.