Diagnose XLOOKUP #N/A: Text Numbers and Unwanted Spaces

Diagnose the key before changing its type

Two common causes of failed exact lookups are a mismatch between numeric and text identifiers, and unwanted spaces. Diagnose the actual key and candidate source row before converting data: identifiers such as 001004 may be distinct from 1004 even when they look numerically related.

TRIM removes ordinary leading and trailing spaces and collapses repeated internal spaces in Excel. It does not remove nonbreaking spaces by itself. Cleaning only the query does not clean the lookup column. The numeric fallback below is appropriate only when numeric conversion is an intentional identifier policy; it can collapse leading zeros or interpret numeric notation.

Two-stage lookup when numeric normalization is intentional

The formula replaces CHAR(160) nonbreaking spaces with ordinary spaces, trims the query, and rejects an empty key. It looks for the cleaned text first, then tries a numeric conversion only if that conversion succeeds. Missing keys return Key Not Found.

This example assumes the lookup column is already clean and return values contain no #N/A errors. XLOOKUP returns the first matching row and is not case-sensitive in ordinary exact-match mode. It does not aggregate duplicate keys. IFNA cannot distinguish a missing key from a matched source cell containing #N/A, so audit source errors before using this fallback.

Use numeric fallback only when your identifier policy considers 01004 and 1004 equivalent. VALUE can also interpret other numeric notation according to the engine and locale. If leading zeros, internal repeated spaces or letter case carry meaning, preserve them and use a different matching policy. Never use this conversion as a universal repair for account or product identifiers.

=IF(COUNTBLANK(D2)>0,"Enter a key",LET(keyText,TRIM(SUBSTITUTE(D2,CHAR(160)," ")),IF(keyText="","Enter a key",IFNA(XLOOKUP(keyText,A2:A5,B2:B5),IF(IFERROR(ISNUMBER(VALUE(keyText)),FALSE),XLOOKUP(VALUE(keyText),A2:A5,B2:B5,"Key Not Found"),"Key Not Found")))))

Diagnostic Check: Detect Text-vs-Number Type Mismatch

TYPE compares the query with A2 only. In this mixed-type example it is not a diagnosis of the entire column or proof that a desired match has the wrong type. Inspect the relevant candidate row. Numeric strings entered directly may be parsed as numbers; use a text-producing formula such as ="1008" when testing a true text key.

=IF(TYPE(D2) = TYPE(A2), "Types Match", "Type Mismatch: Text vs Number")

Reproduce a genuine text-number mismatch

Set D2 to ="1008" to produce a text value, then check ISNUMBER(D2): it is FALSE even though the displayed characters are digits. The numeric fallback finds Laser Mouse. Typing 1008 directly may instead create a numeric cell before the formula runs.

Try ="01004": this policy intentionally returns Widget Pro. Then set A5 to ="01004"; the text-first match returns USB-C Cable instead. This demonstrates why conversion rules must be explicit and why mixed identifier types can be ambiguous.

Further reading

• Microsoft — XLOOKUP function
• Microsoft — TRIM and nonbreaking spaces