Excel: XLOOKUP returns #N/A

Hi, it's BlueByte. When XLOOKUP returns #N/A, the formula itself is fine — it searched the lookup range and simply didn't find the value you asked for. The symptom is a cell showing #N/A (or a whole spilled column of them) where you expected a matched result. We'll walk through what #N/A means here, the handful of reasons a lookup finds nothing, how to confirm which one you hit, fix each cause, and how to show a useful message instead of the raw error.

What #N/A from XLOOKUP means

#N/A means "no value is available" — the lookup ran and found no match. By default XLOOKUP uses exact matching, and Microsoft's documentation is explicit: if a valid match is not found and if_not_found is omitted, #N/A is returned. The full signature is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

With match_mode omitted it defaults to 0 (exact match), so if nothing in lookup_array equals lookup_value exactly, you get #N/A. That is different from a structurally broken formula — this one ran correctly and is telling you the value isn't there.

The reasons an exact-match lookup finds nothing

An exact-match #N/A comes from one of these:

• The value genuinely isn't in the lookup range.
• The value is there but has extra spaces or non-printing characters on one side, so the two strings aren't equal.
• A number is stored as text on one side and as a real number on the other — "12345" never equals 12345.
• You pointed lookup_array at the wrong column, or the range doesn't cover every row.

Each of these breaks the exact-match test that match_mode 0 performs; none is a bug in XLOOKUP.

Confirm which reason with a few quick checks

Don't guess — a couple of helper formulas tell the causes apart. Check whether the value exists at all, and whether the text lengths differ (a length gap means hidden spaces):

=COUNTIF(A:A, D2)
=LEN(D2) & " vs " & LEN(A2)

If COUNTIF returns 0, the value truly isn't found (or a type/space mismatch is hiding it). If the two LEN values differ for what looks like the same text, you have stray spaces. Then check the data type on each side:

=ISNUMBER(D2) & " / " & ISNUMBER(A2)

If one is TRUE and the other FALSE, you're comparing a number to text — that's your #N/A.

Fix it by cleaning the lookup value

For stray spaces or non-printing characters, wrap the lookup value in TRIM (removes extra spaces) and, if needed, CLEAN (removes non-printable characters):

=XLOOKUP(TRIM(D2), A2:A100, B2:B100)

Expected result: the lookup that returned #N/A now returns the matched value, because TRIM(D2) equals the stored key exactly. If the source data carries the spaces, clean that column once rather than wrapping every formula.

Fix a number-stored-as-text mismatch

If one side is text and the other a real number, make them the same type. To coerce a text lookup value to a number:

=XLOOKUP(VALUE(D2), A2:A100, B2:B100)

If instead the keys are text and your lookup value is numeric, coerce the other way with D2 & "". The durable fix is to store the key column consistently — select it and run Data ▸ Text to Columns ▸ Finish to convert text-numbers to real numbers in place.

Show a friendly message instead of #N/A

Once you've confirmed a blank result is legitimate — the value really can be missing — don't leave a raw #N/A on the sheet. XLOOKUP has a built-in if_not_found argument for exactly this:

=XLOOKUP(D2, A2:A100, B2:B100, "Not found")

That returns Not found instead of #N/A when there's no match. For an existing formula you'd rather not rewrite, wrap it in IFNA, which traps only #N/A and passes every other result through unchanged:

=IFNA(XLOOKUP(D2, A2:A100, B2:B100), "Not found")

Prefer IFNA over IFERROR here: IFERROR also hides #REF!, #VALUE!, and #DIV/0!, which would mask a genuine formula bug.

A real case: an order ID that looks equal but isn't

You're matching order IDs. =XLOOKUP(D2, A:A, B:B) returns #N/A, yet D2 and A47 both read ORD-1001. You run =LEN(D2)&" vs "&LEN(A47) and get 8 vs 9 — A47 picked up a trailing space from an export. You add a helper column =TRIM(A2), point the lookup at that cleaned column, and the #N/A becomes the correct customer name. The length check now reads 8 vs 8, confirming the keys finally match.

Verify the match and prevent it recurring

Confirm the fix with the same checks you started with — COUNTIF should now return 1 (or more) and the two LEN values should agree. To keep it from returning: hold key columns in one consistent data type, TRIM imported data as it comes in, and add a permanent if_not_found message so a future missing value shows text rather than a red error. If your keys are clean, leave match_mode at the default 0 and rely on exact matching instead of approximation.

How #N/A differs from #REF! and a silent VLOOKUP

#N/A means the lookup ran and found nothing. #REF! is different: a cell reference in the formula points at something that no longer exists — a deleted row or column — so the formula can't resolve its own inputs. And watch for the opposite of #N/A: VLOOKUP with its fourth argument left as TRUE (approximate match) returns the nearest value instead of #N/A when there's no exact match — a wrong answer with no error at all. XLOOKUP defaulting to exact match is safer precisely because it surfaces #N/A instead of quietly guessing.