XLOOKUP returns #N/A even though the value exists

1Extra spaces before or after the text

This is the most common cause, especially with data that was exported from another system or typed by hand.

How to recognise it

Compare lengths. =LEN(A5) returns 6 for Apple even though you only see 5 letters. You can also click the cell and look for the cursor sitting after a space in the formula bar.

Fix

Trim both the lookup value and the lookup column inside the formula. This works in every version that has XLOOKUP:

=XLOOKUP(TRIM(E2),TRIM(A2:A100),B2:B100)

To clean the data for good, put =TRIM(A2) in a helper column, fill it down, copy the results, and use Home > Paste > Paste Values over the original column. Then delete the helper column.

Prevent it. Clean imported lists once with TRIM before you build lookups on them. Every formula that uses the list then works without extra wrapping.

2Numbers stored as text (or the other way round)

IDs, ZIP codes and invoice numbers often arrive as text. XLOOKUP won't match the number 1002 to the text "1002".

How to recognise it

Text numbers sit on the left of the cell and usually show a small green triangle in the corner. =ISTEXT(A2) returns TRUE for them and =ISNUMBER(E2) tells you what the lookup value is.

Fix

Make the lookup value the same type as the list. If the list holds text and you're looking up a number, add an empty text string:

=XLOOKUP(E2&"",A2:A100,B2:B100)

If the list holds real numbers and the lookup value is text, turn it into a number with two minus signs:

=XLOOKUP(--E2,A2:A100,B2:B100)

To convert a whole column of text numbers, select it, click the warning icon and choose Convert to Number. Or use Data > Text to Columns and click Finish straight away.

Codes with leading zeros, like 00123, should stay text on both sides. Converting them to numbers drops the zeros.

Prevent it. Decide once whether a column of codes is text or numbers, and format it before you type or paste into it.

3Non-breaking spaces and other invisible characters

Text copied from web pages, PDFs and some accounting systems often contains a non-breaking space, character 160. It looks like a normal space, but TRIM doesn't remove it.

How to recognise it

TRIM didn't fix it, yet =LEN(A5) is still too long. =CODE(RIGHT(A5)) returns 160 when the last character is a non-breaking space.

Fix

Swap character 160 for a normal space, then trim:

=XLOOKUP(TRIM(E2),TRIM(SUBSTITUTE(A2:A100,CHAR(160)," ")),B2:B100)

For line breaks and other non-printing characters (codes 0 to 31), add CLEAN. A helper column that fixes all three looks like this:

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

You can also use Find & Replace (Ctrl+H): click in Find what, hold Alt and type 0160 on the number pad, leave Replace with empty, and click Replace All.

4The lookup range is wrong or moved when you copied the formula

How to recognise it

Click the formula cell. Excel outlines each range in color. Check that lookup_array is the column that contains the value, not the column next to it. If only the lower rows show #N/A after you filled the formula down, the ranges have slid down with it.

Fix

Lock the ranges with $ signs (press F4 while the cursor is in a reference):

=XLOOKUP(E2,$A$2:$A$100,$B$2:$B$100)

Or turn the list into a table with Insert > Table and use the column names, which never slide:

=XLOOKUP(E2,Products[Item],Products[Price])

If lookup_array and return_array are different sizes, XLOOKUP returns #VALUE!, not #N/A.

5match_mode or search_mode is set to something unexpected

XLOOKUP does an exact match unless you tell it otherwise. If the formula has a fifth or sixth argument, check it.

How to recognise it

Look for a formula like =XLOOKUP(E2,A2:A100,B2:B100,,1) or one that ends in ,2) or ,-2).

• match_mode -1 or 1 returns the next smaller or larger item when there's no exact match, so you get a wrong value instead of an error.
• search_mode 2 or -2 is a binary search. It needs data sorted ascending (2) or descending (-2). On unsorted data it can miss values that are there and return #N/A.

Fix

Remove the extra arguments, or set match_mode to 0 for an exact match:

=XLOOKUP(E2,A2:A100,B2:B100,"Not found",0)

6The value really isn't in the list

Sometimes the list just doesn't have it: a typo, "Co." instead of "Company", or a new item that hasn't been added yet.

Fix

Use the if_not_found argument so missing items show a clear message instead of an error:

=XLOOKUP(E2,A2:A100,B2:B100,"Not found")

To find close matches, search with wildcards (match_mode 2). This finds the first item that contains the text in E2:

=XLOOKUP("*"&E2&"*",A2:A100,B2:B100,"Not found",2)