Why does VLOOKUP return #N/A when the value exists?

Because the two values are not equal to Excel even though they look equal to you. The usual causes are a trailing space, a number stored as text on one side, a non-breaking space pasted from a web page, or a difference in case-insensitive but character-different lookalikes.

How do I check whether two cells really match?

Put =A2=B2 in a spare cell. TRUE means they match and the problem is elsewhere. FALSE with identical-looking text means whitespace or type. =LEN(A2) and =LEN(B2) will differ if there is a hidden character.

How do I fix numbers stored as text in a lookup?

Convert one side so both are the same type. Multiply by 1, use VALUE, or use Text to Columns with Finish on the column. To make a lookup tolerate both, wrap the key: IFERROR(lookup on the number, lookup on the text).

Should I just wrap the lookup in IFERROR?

Only after you know why it failed. IFERROR turns a visible not-found into a blank or a zero, and a zero that means not found will be summed with real zeros later.

← All Kits · Excel Kit

The Lookup Returns #N/A and the Value Is Right There: Six Causes

Michael Nocito · Updated August 2026 · Every number on this page was worked before it was published

The value is sitting in the other table. You can see it. The lookup says #N/A. This is the most reported problem in Excel, and it is almost always the same short list of causes, none of which is Excel being wrong.

What you do: run the equality test first. =A2=B2 in a spare cell tells you in one second whether this is a data problem or a formula problem, and it splits the six causes into two groups.

The short version. #N/A means not found. Your job is to find out what makes the two values different.

The two-minute diagnosis

Test	Formula	What it tells you
Do they match?	`=A2=B2`	FALSE on identical-looking text means hidden characters or type
Same length?	`=LEN(A2)&" / "&LEN(B2)`	A difference of 1 is nearly always a trailing space
Same type?	`=ISTEXT(A2)&" / "&ISTEXT(B2)`	One TRUE and one FALSE is the number-as-text case
What is the last character?	`=CODE(RIGHT(A2,1))`	32 is a space, 160 is a non-breaking space, 10 is a line feed
Is it in the list at all?	`=COUNTIF(range,A2)`	0 confirms not found, more than 1 means duplicates

The six causes

1. Trailing or leading spaces

By far the most common. "North " is not "North". LEN shows 6 against 5. Fix the data with TRIM in a helper column, then paste values over the original. Fixing it in the formula, =XLOOKUP(TRIM(A2),...), only cleans one side and leaves the underlying data still dirty for the next person.

2. Non-breaking spaces from a web page or PDF

Character 160 looks exactly like a space and TRIM does not remove it, because TRIM only handles character 32. This is the one that makes people think Excel is broken.

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

Run that on both sides of the lookup whenever the data came from a browser, an email or a PDF.

3. Numbers stored as text

The lookup key is 1024 as a number and the table holds "1024" as text, or the reverse. They are not equal and never will be. The tell is alignment: text left-aligns, numbers right-align, in a column you have not manually aligned. There is often a small green triangle too.

Convert one side properly: select the column, Data, Text to Columns, then Finish. That re-parses every cell with no other change. Or multiply by 1 in a helper column. To make a formula survive both, try each in turn:

=IFERROR(INDEX(Names,MATCH(A2,Ids,0)),
         INDEX(Names,MATCH(TEXT(A2,"0"),Ids,0)))

4. The lookup range does not include the key column

A VLOOKUP searches only the first column of the range it is given. If your key is in column C and the range starts at B, the key column is not being searched. XLOOKUP and INDEX MATCH do not have this problem because the lookup array is named separately, which is one of the strongest arguments for using them.

5. Approximate match on an unsorted list

The fourth argument of VLOOKUP defaults to TRUE, which is approximate match, and approximate match on an unsorted list returns wrong answers and occasional #N/A in no predictable pattern. Always type the FALSE:

=VLOOKUP(A2, Table, 3, FALSE)

The equivalent slip in XLOOKUP is a fifth argument of -1 or 1 where you wanted 0.

6. Relative references sliding as you fill down

The formula in row 2 looks at $B$2:$D$400, but the copy in row 300 is looking at B300:D698 because the range was written without dollar signs. Rows at the top find their values and rows further down return #N/A, which is a distinctive pattern: the errors all appear together at the bottom.

Lock the range with F4, or convert the source to a table and use the table name, which never slides.

The pattern of the errors is a clue. All rows failing means the type or the range is wrong. Scattered failures mean dirty data on those keys. Failures only at the bottom mean an unlocked range. Failures only for new values mean the value genuinely is not there, which is a finding rather than a bug.

When #N/A is the correct answer

Sometimes the key really is missing: a new customer, a discontinued product, a typo in the source system. That is information, and it should be reported rather than hidden. Handle it explicitly:

=XLOOKUP(A2, Ids, Names, "NOT IN MASTER")

A visible label can be filtered, counted and sent back to whoever owns the data. A zero cannot, and it will be summed into a total by somebody who does not know it was a placeholder. That distinction is the whole argument in IFERROR.

How to apply this to your own work

Put the equality test in a spare cell before you change anything. It saves the twenty minutes people spend rewriting a formula that was already correct.
Clean keys on import, not in formulas. TRIM plus the CHAR(160) substitution, once, on the way in.
Standardise key types at the source. Decide whether IDs are text or numbers and make every table agree.
Convert lookup sources to tables so ranges cannot slide.
Count your not-found rows and report the count. It is a data quality measure that somebody upstream can act on.

The one habit to keep

Never wrap an error you have not explained. IFERROR is a way of saying "I know why this fails and what should happen instead". Used before you know, it is a way of turning a question into a number.

How many of the zeros in your last report were really not-founds?

Every test here was run before it was published. CHAR(160) is the non-breaking space that TRIM leaves in place; CODE on the last character is what identifies it.

#N/A means not found. It almost never means missing.

Excel for Analysts is 378 pages that read every formula and dialog one at a time, so the workbook stops being a place where numbers appear and becomes one you can check.

Excel for Analysts, $19 →

Diagnose the cause, do not wrap it in IFERROR.

IFERROR explains when hiding an error is right and when it is a cover-up, and cleaning messy data handles the invisible characters behind most of these. The Excel Kit has the drills.

Read Cleaning Messy Data →