XLOOKUP returns #N/A when the value exists: the fixes

F2:  A-1042
B5:  A-1042            <- the same code, visibly in the lookup column

G2:  =XLOOKUP(F2,B2:B11,D2:D11)
     #N/A

Why it happens

The XLOOKUP documentation states that match_mode 0 is "Exact match. If none found, return #N/A. This is the default", and that when no valid match is found and if_not_found is missing, #N/A is returned. So the error is not a malfunction: XLOOKUP compared every cell of lookup_array with lookup_value and none was exactly equal.

Microsoft's #N/A troubleshooting page lists the reasons a value that looks present still fails:

• Different data types. "The lookup value and the source data are different data types", for example a number looked up in a column where the numbers are stored as text.
• Extra spacing in the cells. Leading or trailing spaces make two values differ.

Top ten ways to clean your data adds the less visible cases: text can contain space characters with Unicode values 32 and 160, and nonprinting characters with values 0 to 31, 127, 129, 141, 143, 144 and 157. "Because these characters are not easily noticed, the unexpected results may be difficult to understand."

Two of those are not removed by the obvious tools. The TRIM documentation says TRIM was designed for the 7-bit ASCII space (value 32) and "does not remove this nonbreaking space character" (value 160), which is common in text copied from web pages. The CLEAN documentation says CLEAN removes values 0 to 31 but not 127, 129, 141, 143, 144 and 157.

The optional arguments can also cause it. With search_mode 2 or -2, XLOOKUP performs a binary search that relies on lookup_array being sorted; the documentation warns that "if not sorted, invalid results will be returned". With match_mode 2, the characters *, ? and ~ have special meaning, so a lookup value that contains them is no longer read literally.

How to fix it

1. Measure both values. Put these next to the failing formula, replacing B5 with the cell that should have matched:

   =LEN(F2)
   =LEN(B5)
   =ISNUMBER(F2)
   =ISNUMBER(B5)
   

   Different lengths mean extra characters. One TRUE and one FALSE from ISNUMBER means a type mismatch. The IS functions do not convert their argument, so a number stored as text returns FALSE.

2. Identify the hidden character. If the lengths differ, read the code of the first and last characters:

   =UNICODE(LEFT(B5,1))
   =UNICODE(RIGHT(B5,1))
   

   32 is an ordinary space, 160 a non-breaking space, and 0 to 31 a nonprinting character.

3. Clean the lookup column. In a helper column, combine the three functions Microsoft names for this, TRIM, CLEAN and SUBSTITUTE (here SUBSTITUTE replaces value 160 first):

   =TRIM(CLEAN(SUBSTITUTE(B2,UNICHAR(160)," ")))
   

   Fill it down, then copy the helper column and paste it over the original with Paste Special > Values, as described in Convert numbers stored as text to numbers. Apply the same formula to the lookup value if that is the dirty side.

   If you cannot change the source data, clean inside the formula instead. Microsoft's #N/A page shows TRIM applied to a whole range inside a lookup, but TRIM alone only removes ordinary spaces, not the non-breaking space (value 160) or nonprinting characters. The XLOOKUP version with the full clean-up is:

   =XLOOKUP(TRIM(CLEAN(SUBSTITUTE(F2,UNICHAR(160)," "))),TRIM(CLEAN(SUBSTITUTE($B$2:$B$11,UNICHAR(160)," "))),$D$2:$D$11)
   

4. Make the data types match. If the lookup column holds numbers stored as text, select it, select the error indicator and choose Convert to Number, or convert it with VALUE in a helper column. If only the lookup value is text, convert it in the formula:

   =XLOOKUP(VALUE(F2),$B$2:$B$11,$D$2:$D$11)
   

   VALUE returns #VALUE! if the text is not in a number, date or time format Excel recognises. The #N/A page suggests checking formats with Format Cells > Number (Ctrl+1) and, to force a format change on an entire column, applying the format and then using Data > Text to Columns > Finish. Changing the format alone does not convert text already in the cell (Convert numbers stored as text).

5. Reset the optional arguments. Leave match_mode out (exact match) and search_mode out (first to last) while you debug. If you need wildcards, keep match_mode 2 and type ~*, ~? or ~~ to look up a literal asterisk, question mark or tilde.

6. Add if_not_found last. Once real matches work, use it only for values that are genuinely absent:

   =XLOOKUP(F2,$B$2:$B$11,$D$2:$D$11,"Not in list")
   

#N/A only on the rows you filled down

If the first formula works and some copies below it return #N/A for values that exist, the ranges moved. A relative reference changes when you copy the formula: B2:B11 filled down one row becomes B3:B12, so the rows above slide out of the search. Select the range in the formula bar and press F4 to make it absolute ($B$2:$B$11), do the same for return_array, and fill down again. Keep both ranges over the same rows, as the examples in the XLOOKUP documentation do, so the item returned sits on the row that matched.

XLOOKUP can also return several items from one formula (the documentation's second example returns a name and a department together). Those results spill into the neighbouring cells; if something blocks them, you get a different error, explained in #SPILL! error in Excel.

Check that it worked

• LEN returns the same number for the lookup value and the matching cell, and ISNUMBER returns the same result for both.
• With if_not_found removed, the formula returns the expected item from return_array.
• To see each step, select the cell and use Formulas > Formula Auditing > Evaluate Formula, then Evaluate, which the XLOOKUP documentation points to for following a calculation.
• Spot-check a value that is really missing: it should still return #N/A, or your if_not_found text.

If the same workbook also uses VLOOKUP, the spaces and type mismatches above break it the same way, along with failure modes of its own, covered in VLOOKUP returns #N/A or the wrong value. If your lookup table is pulled from another Google Sheets file and the import itself fails, start with IMPORTRANGE: You need to connect these sheets. In every case the question is the same: are the two values exactly identical to Excel, not just to your eye? Once LEN and ISNUMBER agree, XLOOKUP will find the match.