Excel VLOOKUP #N/A Error: Why It Can't Find Your Value and How to Fix It

Your VLOOKUP returns #N/A even though you can see the matching value sitting in the lookup table.

Why: #N/A literally means “not available” — VLOOKUP searched the first column of your table and did not find an exact match for the lookup value. Usually the value really is there, but something is subtly off: VLOOKUP is in approximate-match mode, the cells contain invisible spaces, or one side is a number while the other is text. Excel treats "1001" (text) and 1001 (number) as different things.

Fix 1: Force an exact match with FALSE

This is the single most common cause. VLOOKUP’s fourth argument, range_lookup, controls matching. Leaving it out — or setting it to TRUE — tells VLOOKUP to find an approximate match, which requires the first column to be sorted and otherwise returns #N/A or, worse, the wrong row.

Set the fourth argument to FALSE for an exact match. No sorting is needed.

Wrong (approximate): =VLOOKUP(D2,A2:B7,2)
Wrong (approximate): =VLOOKUP(D2,A2:B7,2,TRUE)
Right (exact):       =VLOOKUP(D2,A2:B7,2,FALSE)

Make this your default — almost every real-world VLOOKUP wants FALSE.

Fix 2: Strip hidden spaces with TRIM

If you are certain the value exists and exact match is on, the cells likely contain leading, trailing, or hidden spaces, so "Banana " never equals "Banana".

Wrap the lookup table in TRIM to clean up extra spaces:

=VLOOKUP(D2,TRIM(A2:B7),2,FALSE)

To clean the data permanently instead: in a helper column type =TRIM(A2), fill it down, then Copy the column and Paste Special > Values back over the original. The CLEAN function removes non-printing characters the same way.

Fix 3: Match the data type (text vs number)

VLOOKUP returns #N/A when the lookup value and the source are different data types — for example you look up the number 1001, but the IDs in column A are stored as text. A tell-tale sign is numbers sitting left-aligned in their cells (text) instead of right-aligned (numbers), often with a small green triangle in the corner.

To fix it, make both sides the same type:

1. Select the column stored as text.
2. Click the warning triangle that appears and choose Convert to Number, or go to Data > Text to Columns > Finish to re-coerce the whole column.
3. Alternatively right-click the cells, choose Format Cells > Number, then re-enter the values so the format takes.

Fix 4: Show a friendly message with IFERROR

When some lookups genuinely have no match (a new item not yet in the table), trap the #N/A so reports stay clean.

=IFERROR(VLOOKUP(D2,A2:B7,2,FALSE),"Not found")

IFNA is more precise — it catches only #N/A and lets real errors like #REF! still surface:

=IFNA(VLOOKUP(D2,A2:B7,2,FALSE),"Not found")

Add error handling only after you’ve confirmed the formula works, so you don’t hide a genuine mistake behind a tidy message.

FAQ

VLOOKUP still says #N/A with FALSE and TRIM. Check the data type (Fix 3). A number-vs-text mismatch survives both exact match and TRIM because the values aren’t equal in the first place.

Should I switch to INDEX/MATCH or XLOOKUP? They help when the lookup column isn’t the leftmost one — VLOOKUP can only look right. =INDEX(B2:B7,MATCH(D2,A2:A7,0)) or XLOOKUP both look up a value regardless of column position. The #N/A causes (exact match, spaces, data types) are identical, though.

Why does approximate match sometimes return a wrong number instead of #N/A? With TRUE, VLOOKUP assumes the first column is sorted ascending and returns the largest value less than your lookup. On unsorted data that’s a silent wrong answer — another reason to always use FALSE.

Sources: Microsoft Support — How to correct a #N/A error in the VLOOKUP function · Microsoft Support — How to correct a #N/A error