Tutorial · November 8, 2026
Why XLOOKUP says #N/A when the value is right there
You paste invoice IDs from the finance system, write a perfectly normal XLOOKUP, and half the rows come back #N/A. You can see 10483 in both tables. Excel insists it isn't there.
Excel isn't wrong. The two values only look the same. Here are the three usual reasons, how to tell which one you have, and a formula that copes with all three.
The three invisible differences
| What you see | What Excel sees | Where it comes from |
|---|---|---|
| 10483 | the text "10483" | CSV and ERP exports, copy-paste from web pages |
| 10483 | "10483 " with a trailing space | Exports that pad fixed-width fields |
| 10483 | "10483" + a non-breaking space (character 160) | Web pages, PDFs, some accounting systems |
A number and a piece of text that look identical are never equal to XLOOKUP. Neither are two IDs where one has an extra space.
Find out which one you have (10 seconds)
Put these next to the failing cell (say it's A2):
=ISTEXT(A2)— TRUE means the "number" is stored as text. You'll often see a small green triangle in the cell's corner too.=LEN(A2)— if 10483 gives 6, there's an extra character.=UNICODE(RIGHT(A2))— 32 is a normal space, 160 is a non-breaking space.
Do the same on the matching cell in the lookup table. The difference will show up in one of the three.
The fix that handles all three
Make both sides plain, trimmed text before comparing:
=XLOOKUP( TRIM(SUBSTITUTE(A2, UNICHAR(160), " ")), TRIM(SUBSTITUTE(Invoices!$A$2:$A$11, UNICHAR(160), " ")), Invoices!$B$2:$B$11, "Not found")
How it works:
SUBSTITUTE(…, UNICHAR(160), " ")turns non-breaking spaces into normal spaces. TRIM and CLEAN don't remove them on their own, which is why so many fixes don't work. (You'll often seeCHAR(160)online. It works on Windows but not on Excel for Mac, where CHAR uses a different character set. UNICHAR works on both.)TRIM(…)removes spaces at the start and end, and also turns numbers into text. Now both sides are text, so 10483 and "10483" match.- Doing the same cleanup to the lookup column means you don't have to edit the source table.
"Not found"is XLOOKUP's built-in fallback. Use it instead of wrapping the formula in IFERROR, which would also hide real mistakes like a deleted column.
Before → after on the sample data: 4 of 10 payments matched before. All 10 match after.
Better still: fix the data once
The formula is a patch. If the same export arrives every month, clean it at the source:
- Numbers stored as text: select the column → Data → Text to Columns → Finish. Excel re-reads the values as numbers.
- Spaces: use Power Query (Data → From Table/Range), select the column → Transform → Format → Trim, then set the column type to Whole Number. Next month, just click Refresh.
If you're on Excel 2016 or 2019
XLOOKUP needs Microsoft 365 or Excel 2021. The same idea works with INDEX/MATCH:
=INDEX(Invoices!$B$2:$B$11, MATCH(TRIM(SUBSTITUTE(A2,UNICHAR(160)," ")), TRIM(SUBSTITUTE(Invoices!$A$2:$A$11,UNICHAR(160)," ")), 0))
In older versions, confirm it with Ctrl+Shift+Enter because it works on a whole column at once.
Try this: a colleague's lookup doesn't return #N/A. It returns the wrong customer's amount for a few rows. What's the most likely cause? Hit reply with your guess. The answer is in next week's issue.