Category: Formulas

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.

Excel’s UNIQUE() function is the simplest way to pull a list of distinct values from a range — but it isn’t available in every version. If you’re on Excel 2019 or Excel 2016, UNIQUE() will return a #NAME? error, and you’ll need a different approach: a legacy array formula that does the same job without it.

Applies to: UNIQUE() is available in Excel for Microsoft 365, Excel 2024, and Excel 2021. The fallback in this article is for Excel 2019 and Excel 2016, which do not have UNIQUE().

Check Your Version First

Before reaching for the fallback below, confirm you actually need it. Microsoft’s official documentation for UNIQUE() lists Microsoft 365, Excel 2024, and Excel 2021 as supported versions — UNIQUE() works natively in Excel 2021, not just in Microsoft 365. If your formula bar shows #NAME? on Excel 2021, that’s more likely an unrelated account or installation issue than a genuine version gap; it’s worth checking your Microsoft 365/Office installation status before concluding your version doesn’t support it. The fallback below is specifically for Excel 2019 and 2016, where UNIQUE() is genuinely not present. If you want to remove duplicate rows instead of listing distinct values, see our guide on removing duplicates with Power Query.

The Legacy Array Formula

This array formula extracts unique values from a range, in the order they first appear, without UNIQUE():

=IFERROR(INDEX($A$2:$A$9, MATCH(0, COUNTIF($C$1:C1, $A$2:$A$9), 0)), "")

Enter this in the first result cell (for example, C2), and confirm it with Ctrl+Shift+Enter instead of just Enter — this is a legacy array formula (also called a CSE formula), and it will not calculate correctly if entered as a normal formula. Excel will show the formula wrapped in curly braces { } in the formula bar once it’s entered correctly (you don’t type the braces yourself). Then fill it down as far as your data could plausibly have unique values.

This project directly reproduced this formula in Excel: entering it in C2 and filling down through C6, against a source list in A2:A9 containing Apple, Banana, Apple, Cherry, Banana, Date, Elderberry, Cherry (eight values, with Apple, Banana, and Cherry each appearing twice), produced exactly Apple, Banana, Cherry, Date, Elderberry — the five distinct values, in the order they first appeared in the source list. IFERROR returns "" once every unique value has been extracted, so filling down further than needed doesn’t produce error values in the extra cells.

How the Formula Works

Read it from the outside in:

• COUNTIF($C$1:C1, $A$2:$A$9) — for each value in the source range, counts how many times it has already appeared in the results column so far (the range $C$1:C1 grows as you fill the formula down, since only the first reference is locked with $).
• MATCH(0, ..., 0) — finds the position of the first value in the source range whose count-so-far is 0, meaning it hasn’t been picked yet.
• INDEX($A$2:$A$9, ...) — returns the actual value at that position.
• IFERROR(..., "") — once every value has been picked at least once, MATCH can’t find another 0 and returns an error; IFERROR catches that and shows a blank instead of an error value.

Each row’s formula depends on the results already filled in above it (through the growing $C$1:C1 reference), which is why it has to be entered as an array formula and filled down in order, top to bottom, rather than pasted into a block of cells all at once.

One-line Summary

UNIQUE() works natively in Excel 2021, Excel 2024, and Microsoft 365 — the fallback below is only for Excel 2019 and 2016. There, use =IFERROR(INDEX($A$2:$A$9, MATCH(0, COUNTIF($C$1:C1, $A$2:$A$9), 0)), ""), entered with Ctrl+Shift+Enter and filled down, to extract unique values in the order they first appear.

Sources

• UNIQUE function — Microsoft Support. Backs the version scope: Microsoft 365, Excel 2024, and Excel 2021 support UNIQUE() natively.
• INDEX function, MATCH function, Use the COUNTIF function — Microsoft Support. Back the individual behavior of each function used in the legacy array formula.
• The specific array-formula pattern combining INDEX/MATCH/COUNTIF/IFERROR to extract unique values is a well-known Excel technique, not itself the subject of a single Microsoft documentation page; this project independently reproduced it in Excel against the exact dataset described above (Ctrl+Shift+Enter, filled down C2:C6), confirmed in the Pilot E entry of the project decision log, and the output matched the expected unique list exactly.