Why XLOOKUP misses a value that looks identical

Inspect the key before changing the formula

A visible match can hide a different underlying value. PEN-BLK, PEN-BLK , and a key ending in a nonbreaking space look almost identical in a narrow column. Treat them as separate values until you know how the source should encode the key.

The first three sample rows represent an exact key, a trailing ASCII space, and a trailing nonbreaking space. Their expected cleaned value is PEN-BLK. The fourth key, 00123, must remain text. Converting it to 123 changes the identifier.

Use small checks to reveal the difference

If the key is in A2, inspect its length and final character. A trailing ASCII space has character code 32. A nonbreaking space has code 160. The following checks are intended for non-empty cells.

=LEN(A2)
=UNICODE(RIGHT(A2,1))
=ISTEXT(A2)
=ISNUMBER(A2)

TRIM removes ordinary spaces, but Microsoft's documentation notes that it does not remove the nonbreaking-space character by itself. Do not repeatedly nest cleanup functions until a match appears. First identify the unwanted character and decide whether removing it is legitimate for the key.

Keep normalization in a separate column

For this sample's SKU keys, replacing a nonbreaking space with a normal space and trimming is appropriate:

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

Keep A as the original key and put the cleaned key in another column. TRIM also reduces repeated interior spaces. That behavior may be wrong for identifiers where spaces are significant. A source-specific cleanup rule is safer than a universal “clean all keys” rule.

For a lookup table with cleaned keys in F2:F5 and prices in G2:G5, an exact lookup can use:

=XLOOKUP(B2,$F$2:$F$5,$G$2:$G$5,"Not found",0)

Check the lookup table for duplicate keys before accepting the first result. If one SKU has conflicting prices, choosing the first value hides a data-quality problem. XLOOKUP availability differs across Excel versions, so check Microsoft's supported-version list.

Verify the mapping without erasing exceptions

The first three practice keys should map to the same cleaned SKU. The identifier 00123 should remain five characters. Include a genuinely absent key and confirm it produces “Not found.” Keep a zero price distinct from an empty price and a missing match.

The browser comparison tool uses exact, case-sensitive text matching. Excel lookup matching behavior is different, so a browser comparison and a worksheet lookup are not interchangeable validation methods. Use each tool's stated rules and investigate differences.

Sources & further reading

• XLOOKUP function — Microsoft
• TRIM function — Microsoft

The sample data and worked calculations are original instructional examples. Spreadsheet menus and supported features depend on your installed version. See the verification method for the checks performed.