VLOOKUP returns #N/A when Excel cannot find the lookup value in the first column of your lookup table. The formula may be written correctly, but the value still does not match because of spaces, text-versus-number differences, approximate match settings, or a table range that does not include the right column.

This guide focuses only on #N/A. For broader VLOOKUP problems such as #REF!, wrong results, or table array mistakes, start with the VLOOKUP troubleshooting guide.

1. Quick Diagnosis Table

Use this table before rewriting the formula:

Most #N/A fixes come down to one question: does the lookup value exactly match a value in the first column of the lookup table?

2. Start with a Clean Exact Match Formula

A safe VLOOKUP formula for IDs, names, invoice numbers, and most business data uses exact match:

=VLOOKUP(A2,$A$2:$D$20,3,FALSE)

The parts are:

If the fourth argument is missing or set to TRUE, VLOOKUP uses approximate match. Approximate match only works safely when the first column is sorted and the task is designed for ranges, such as tax brackets or commission bands.

3. Fix Extra Spaces and Hidden Characters

Imported data often includes leading spaces, double spaces, line breaks, or non-printing characters. These make two values look identical while Excel sees them as different.

Use helper columns to clean both sides:

=TRIM(CLEAN(A2))

Then use the cleaned helper column as the lookup value or lookup table key.

Example:

If the spaces came from a website or PDF, TRIM may not remove non-breaking spaces. Try this version:

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

For a full cleanup workflow, see Excel Data Cleaning.

4. Fix Numbers Stored as Text

VLOOKUP treats the number 1001 and the text "1001" as different values. This is common after CSV imports or copied web tables.

Use these checks:

=ISNUMBER(A2)

=ISTEXT(A2)

If the lookup value is text but the table uses numbers, convert the lookup value with VALUE:

=VLOOKUP(VALUE(A2),$A$2:$D$20,3,FALSE)

If the table key is text but the lookup value is numeric, convert the lookup value to text:

=VLOOKUP(TEXT(A2,"0"),$A$2:$D$20,3,FALSE)

Do not mix these fixes randomly. First decide whether the key column should be stored as numbers or text, then make both sides consistent.

5. Make Sure the Lookup Column Is First

VLOOKUP searches only the first column of table_array. If the value you want to find is in column B, but your range starts at column A, VLOOKUP will not find it.

For example, this formula searches column A:

=VLOOKUP(F2,$A$2:$D$20,4,FALSE)

If the IDs are actually in column B, adjust the range so column B is first:

=VLOOKUP(F2,$B$2:$D$20,3,FALSE)

If you often need to look left or choose any return column, consider XLOOKUP instead.

6. Use IFNA Only After the Lookup Is Correct

IFNA hides #N/A and replaces it with a friendly message:

=IFNA(VLOOKUP(A2,$A$2:$D$20,3,FALSE),"Not found")

This is useful when missing records are normal. For example, a product might not exist in the price table yet.

Use IFNA after checking:

1. The lookup column is first.
2. The formula uses FALSE for exact match.
3. Both sides use the same data type.
4. Extra spaces and hidden characters are cleaned.
5. The lookup table includes all expected rows.

Do not use IFNA as the first fix. It can hide real data problems.

7. Example: Fix a VLOOKUP That Should Work

Suppose you have this order list:

And this product table:

The basic formula is:

=VLOOKUP(B2,$E$2:$G$4,2,FALSE)

If P-205 returns #N/A, test it:

=B3=E3

If the result is FALSE, even though the values look the same, clean both values:

=TRIM(CLEAN(B3))

Then run the lookup against the cleaned keys.

8. When to Switch to XLOOKUP

VLOOKUP is still useful in older workbooks, but XLOOKUP is usually easier in Microsoft 365 and Excel 2021.

Equivalent XLOOKUP formula:

=XLOOKUP(A2,$A$2:$A$20,$C$2:$C$20,"Not found")

If you are choosing between lookup formulas, use VLOOKUP vs XLOOKUP vs INDEX MATCH.

9. Practice Example

Use this sample table:

Question:

Reference answers:

10. Summary

To fix VLOOKUP #N/A, start with exact match, then check the lookup column, data types, spaces, hidden characters, and table range. Use IFNA only after the formula is structurally correct. If you are using modern Excel and want a cleaner default, XLOOKUP is often the better long-term choice.

11. Related Guides

• Use the broader VLOOKUP troubleshooting guide when the error is not only #N/A.
• Review Excel formula errors for other errors such as #REF!, #VALUE!, and #NAME?.
• Learn modern lookup alternatives in Excel XLOOKUP tutorial.