VLOOKUP or XLOOKUP Returns #N/A Even Though the Values Look Identical

You type SKU-101 in one cell and the price list has SKU-101, yet VLOOKUP returns #N/A. The two values look identical, so the formula seems broken. It usually is not. In our test file, five of seven lookups returned #N/A. Four were caused by hidden differences between the two values, and one was correct because the value was not in the list. XLOOKUP failed on the same five rows.

This guide shows how to find which hidden difference you have, using three short helper formulas, and how to fix it by cleaning both sides before you match. It also shows the one case where #N/A is correct and nothing needs fixing.

Applies to: Microsoft documents VLOOKUP, TRIM, UNICHAR, and UNICODE for Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. Microsoft says XLOOKUP is not available in Excel 2016 and Excel 2019. Tested in the Excel desktop app with an English interface on Korean-language Windows. We did not test other Excel versions, so results may differ there.

The screenshots use a made-up order list (Orders sheet, 7 rows) and a made-up price list (PriceList sheet, 6 rows). All values are fictional.

What You See: #N/A on Values That Look the Same

The formula in column C is a normal exact-match lookup:

=VLOOKUP(B2,PriceList!$A$2:$B$7,2,FALSE)

Only SKU-100 and SKU-103 returned a price. A-1002, A-1003, A-1005, A-1006, and A-1007 returned #N/A.

Microsoft’s VLOOKUP page says that FALSE means exact match and that #N/A means the exact value was not found in the first column. It also warns that leading or trailing spaces and nonprinting characters in the lookup data can cause unexpected results, and it advises against storing numbers as text. That matches what we found, but the page does not tell you which problem you have. For that you need to measure the cells.

Diagnose Before You Fix

Add three helper columns next to the lookup result:

=LEN(B2)
=IF(ISTEXT(B2),UNICODE(RIGHT(B2,1)),"")
=ISNUMBER(B2)

LEN counts characters, the second formula returns the code of the last character, and ISNUMBER tells you whether the cell holds a number or text.

Here is how to read the results from our test:

Row	What you see	LEN	Last character code	ISNUMBER	What it means
A-1002	SKU-101	8	32	FALSE	The text is one character longer than it looks. Code 32 is a regular space at the end.
A-1003	SKU-102	8	160	FALSE	Also one character too long. Code 160 is a non-breaking space at the end.
A-1005	SKU-999	7	57	FALSE	Nothing hidden. The last character is the digit 9.
A-1006	2001	4	(blank)	TRUE	A real number. The price list stores 2001 as text.
A-1007	2002	4	50	FALSE	Text. The price list stores 2002 as a number.

In the first screenshot, 2001 is right-aligned and 2002 is left-aligned. Numbers align right by default and text aligns left, which is a quick visual hint, but the ISNUMBER column is the reliable check.

TRIM does not remove the code 160 character on its own, according to Microsoft. For why, and for a CHAR(160) pitfall on some systems, see our guide to why TRIM isn’t removing non-breaking spaces.

Fix: Clean Both Sides, Then Match

The idea is to build a clean version of the key on both sheets, with the same rules, and match on the clean versions. This formula goes in a helper column on Orders (column G, for the value in B2):

=IF(ISNUMBER(B2),B2&"",TRIM(SUBSTITUTE(B2,UNICHAR(160)," ")))

It turns numbers into text, replaces non-breaking spaces with regular spaces, and then trims the ends. Put the same formula in a helper column next to the price list (column C, for the value in A2), then look up the clean key:

=IFERROR(INDEX(PriceList!$B$2:$B$7,MATCH(G2,PriceList!$C$2:$C$7,0)),"not found")

In our test, six of seven rows found their price. A-1002 returned 11.5, A-1003 returned 12.25, A-1006 returned 5, and A-1007 returned 6. A-1005 still returned “not found”, which is correct because SKU-999 is not in the price list.

Three notes on this fix:

• We made both sides text. We did not test converting both sides to numbers instead.
• UNICHAR(160) worked in our Excel environment. We did not test older versions, and Microsoft’s UNICHAR page lists Excel 2016 and later.
• IFERROR returns “not found” for any error, so check that the clean columns look right before you trust the result.

XLOOKUP Fails the Same Way

In our test we also ran:

=XLOOKUP(B2,PriceList!$A$2:$A$7,PriceList!$B$2:$B$7,"not found")

It returned a price only for A-1001 (10) and A-1004 (9.99). The other five rows returned “not found”, the same five rows where VLOOKUP failed. Microsoft documents XLOOKUP exact match as its default, so the same hidden differences break it.

The same clean keys fix XLOOKUP. We pointed it at the two clean-key columns:

=XLOOKUP(G2,PriceList!$C$2:$C$7,PriceList!$B$2:$B$7,"not found")

In our test it returned 10, 11.5, 12.25, 9.99, “not found”, 5, and 6 for the seven rows. That is the same result as the INDEX and MATCH version, including “not found” for SKU-999. We did not capture a screenshot of this extra check. If you use Excel 2016 or 2019, XLOOKUP is not available according to Microsoft, so the INDEX and MATCH fix is the one to use.

When #N/A Is Correct

Not every #N/A is a hidden character. A-1005 showed nothing unusual in the helper columns, and after cleaning it was still not found. When LEN matches what you see and ISNUMBER agrees on both sides, the value is probably missing from the list. In that case, fix the list, not the formula. To check which items from one list are missing from another, see our guide on comparing two lists in Excel.

If a value is missing because the same label is spelled several ways (for example USA and U.S.), see our guide on how to replace inconsistent labels with one standard value using a lookup table.

If many of your numbers are stored as text and you need real numbers, see our guide on converting numbers stored as text in bulk. For lookup keys such as product codes with leading zeros or 16 or more digits, keep them as text instead.

If the leading zeros are lost when you open a CSV, see our guide on keeping leading zeros in ZIP codes and ID numbers.

Which Version of Excel Do You Need?

Microsoft lists Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016 for VLOOKUP, TRIM, UNICHAR, and UNICODE. Microsoft’s XLOOKUP page lists newer versions and states that XLOOKUP is not available in Excel 2016 and Excel 2019. We tested only one desktop Excel build (English interface, Korean-language Windows), so results and button names in other versions may differ.

One-line Summary

When VLOOKUP or XLOOKUP returns #N/A on values that look identical, compare LEN, the last character code, and ISNUMBER on both sides, then match on clean keys made with the same IF, TRIM, and SUBSTITUTE(…, UNICHAR(160), " ") formula. If a value is truly missing, fix the list.

Sources

• VLOOKUP function — Microsoft Support. Backs the exact-match behavior of FALSE, #N/A when the exact value is not found, and the warning about spaces, nonprinting characters, and numbers stored as text.
• XLOOKUP function — Microsoft Support. Backs the exact-match default, the if_not_found argument, and the statement that XLOOKUP is not available in Excel 2016 and Excel 2019.
• TRIM function — Microsoft Support. Backs the statement that TRIM does not remove the non-breaking space (character 160) by itself.
• UNICHAR function and UNICODE function — Microsoft Support. Back the function behavior and the Applies-to lists.
• Note: the #N/A results, the LEN, last-character-code, and ISNUMBER values, the clean-key results, and the XLOOKUP results come from our own reproduction in the Excel desktop app, not from these pages.