How to Fix the VLOOKUP #N/A Error in Excel

#N/A is the most common VLOOKUP error. It means Excel searched the first column of your table and genuinely couldn't find a match — or it looks like there's a match but something is preventing it from connecting. Here are the six causes, in order of how often they actually happen.

1. Extra spaces (most common)

Your lookup value says "Apple" but the table has "Apple " (with a trailing space). They look identical but they're not.

Fix: Test a cleaned helper value or wrap the lookup value in TRIM:

Clean the source deliberately as well. Do not use Find & Replace to delete every space: valid values such as person names, company names, and North Carolina contain meaningful spaces. TRIM removes leading and trailing ordinary spaces while preserving one space between words; non-breaking characters may need targeted cleanup.

2. Text vs. number mismatch

Your lookup value is the number 42 but the table stores it as the text "42". They look the same in the cell but VLOOKUP treats them as different.

Check: Select a cell from each column and look at the alignment. Numbers align right by default, text aligns left.

Fix (number stored as text → convert to number):

Fix (number in lookup → match text in table):

3. Lookup value genuinely doesn't exist

The value in A2 simply isn't in the first column of your table. Check by using MATCH alone to confirm:

If this also returns #N/A, the value isn't there. Check for typos or whether you're looking in the right table.

4. Approximate match instead of exact match

If the fourth VLOOKUP argument is TRUE (or omitted), Excel uses approximate match — which returns wrong results when your data isn't sorted in ascending order.

Fix: Always add FALSE as the fourth argument:

5. Lookup column isn't the first column

VLOOKUP must search the leftmost column of your table range. If you're searching column C but your range starts at column B, Excel is looking in the wrong place.

Fix: Either start your range at column C, or switch to XLOOKUP or INDEX MATCH, which have no left-column requirement.

6. Wildcards needed for partial matches

You're looking up "Apple" but the table has "Apple Inc.". Exact match will fail.

Fix: Use a wildcard:

Show an expected missing result clearly

If some missing rows are expected, use IFNA so other formula errors remain visible, or use XLOOKUP's built-in fallback:

Do not hide every #N/A until you confirm it represents an allowed missing record rather than a broken key or wrong source range.

Do this with AI

Debugging VLOOKUP errors usually means checking four or five things manually across two different columns. Just describe the situation:

"My VLOOKUP is returning #N/A for half the rows — the lookup values are order IDs and the table is on the Products sheet"

Griddy diagnoses the mismatch and fixes the formula.

Related guides: VLOOKUP returns the wrong value and XLOOKUP vs VLOOKUP.

Sources

• Microsoft Support: How to correct a #N/A error
• Microsoft Support: VLOOKUP function and common problems