XLOOKUP Returns #N/A on Values That Look Identical: Hidden Characters and Type Mismatches

XLOOKUP returns #N/A on a value that exists because it compares keys exactly and never converts data types. The usual causes are a number stored as text, a trailing or non-breaking space (CHAR(160)), or a leading zero stripped during import. Test each key with ISNUMBER, LEN, and UNICODE, then normalize both columns with TRIM, SUBSTITUTE, and VALUE, or run a one-time VBA macro.

Why exact-match lookups fail on identical-looking keys

XLOOKUP defaults to exact matching (match_mode 0). It compares the underlying cell value, not what the grid displays. The text "1001" and the number 1001 render the same way but are different data types, so the comparison fails.

The same rule applies to invisible characters. The string "INV-2041" followed by a non-breaking space is a different string from "INV-2041". Excel does not warn about this, because the lookup ran correctly and simply found no equal value.

XLOOKUP is case-insensitive, so "inv-2041" and "INV-2041" do match. If a pair of keys differs only by case, case is not your problem.

Diagnosing the mismatch

Start with the AI Excel Formula Generator and ask for a set of diagnostic columns instead of writing them by hand.

Input prompt: “For the key in A2 and the matching key in F2, show whether A2 is a number, the length of A2, the length of A2 after TRIM, whether A2 and F2 are exactly equal, and the character code of the last character of A2.”

Expected output:

=ISNUMBER(A2)
=LEN(A2)
=LEN(TRIM(A2))
=EXACT(A2,F2)
=UNICODE(RIGHT(A2,1))

Here is what those formulas return when A2 holds INV-2041 followed by a hidden non-breaking space, and F2 holds INV-2041:

Check	Result	Reading
ISNUMBER(A2)	FALSE	Expected for a text ID
LEN(A2)	9	One more than the 8 visible characters
LEN(F2)	8	The clean key
LEN(TRIM(A2))	9	TRIM removed nothing
EXACT(A2,F2)	FALSE	The strings differ
UNICODE(RIGHT(A2,1))	160	Non-breaking space

Each check has a blind spot, so run them together:

Check	Exposes	Blind spot
ISNUMBER	Text-vs-number mismatch	Hidden characters
LEN(A2)<>LEN(F2)	Any extra character, visible or not	Which character it is
LEN(A2)<>LEN(TRIM(A2))	Regular extra spaces	Non-breaking spaces (TRIM ignores them)
EXACT	Character differences	Types, because it converts numbers to text first
UNICODE(RIGHT())	The identity of a suspicious character	Characters in the middle of the string

EXACT(1001,"1001") returns TRUE even though XLOOKUP fails on the same pair. That is why ISNUMBER belongs in every diagnosis.

Causes

Non-breaking spaces (CHAR(160))

HTML pages use   to hold layout spacing, and copy-paste from web tables, CRM exports, and BI tools carries it into cells. The non-breaking space is Unicode code point 160, and TRIM only removes the standard space (code 32). CLEAN only strips codes 0 to 31, so it misses code 160 as well.

Zero-width spaces (code 8203), tabs, and line feeds from CSV files cause the same failure. None of them show up visually.

Leading zeros

ZIP codes, SKUs, and account numbers often start with zero. When a CSV opens in Excel, a column of 02134 values is converted to the number 2134 unless the column is imported as text. If the other table stores "02134" as text, no match exists.

Excel also stores numbers as 64-bit floating-point values with 15 significant digits. An identifier longer than 15 digits, imported as a number, loses its trailing digits and becomes a different key. Such IDs must stay text.

Apostrophe-prefixed numbers

A leading apostrophe typed before a number ('1001) is not part of the cell’s content. It is a “quote prefix” flag that tells Excel to store the entry as text, and it appears only in the formula bar. LEN does not count it, which makes the problem hard to spot. The clue is the green “Number Stored as Text” triangle and a left-aligned numeric value.

Formula-based fixes

Clean the text key

Order matters. Convert the non-breaking space to a normal space first, strip control characters, then trim. Open the AI Excel Formula Generator and describe the whole lookup.

Input prompt: “Look up the order ID in A2 against Orders!A2:A900 and return the customer name from Orders!C2:C900. Ignore non-breaking spaces, tabs, and extra spaces on both sides. Show ‘Not found’ if there is no match.”

Expected output:

=XLOOKUP(
  TRIM(CLEAN(SUBSTITUTE($A2,CHAR(160)," "))),
  TRIM(CLEAN(SUBSTITUTE(Orders!$A$2:$A$900,CHAR(160)," "))),
  Orders!$C$2:$C$900,
  "Not found")

With A2 = INV-2041 plus a hidden non-breaking space and a matching row for INV-2041 in the Orders sheet, the formula returns the customer name instead of #N/A. Use bounded ranges as shown. The cleaning is recomputed on every calculation, and full-column references make that slow.

For zero-width spaces, add a second SUBSTITUTE(…,UNICHAR(8203),"") layer. UNICHAR(160) is safer than CHAR(160) on older Mac builds of Excel, where the ANSI code page differs.

Fix text-vs-number mismatches

Choose the direction based on whether leading zeros carry meaning:

• Zeros are meaningful (ZIP codes, SKUs): pad the number into text. =XLOOKUP(TEXT($A2,"00000"), Orders!$B$2:$B$900, Orders!$C$2:$C$900, "Not found") turns 2134 into "02134".
• Zeros are noise (plain numeric IDs): convert text to numbers. VALUE("00123") returns 123, and VALUE also handles apostrophe-prefixed numbers. Apply it to both sides if either side may be text.
• Quick coercion: A2&"" turns any value into text, and --A2 or VALUE(A2) turns numeric text into a number.

VALUE("") and VALUE("ABC-1") return #VALUE!. If a column mixes numeric and alphanumeric keys, coerce to text instead of number.

if_not_found versus IFERROR

XLOOKUP’s fourth argument, if_not_found, replaces only the #N/A that results from no match. IFERROR replaces every error type, including #REF! from a deleted column and #VALUE! from a failed VALUE call. That hides real faults.

Use if_not_found for the lookup itself. Return a labeled string like "Not found" instead of 0 or "", so downstream totals cannot silently absorb failed matches. Reserve IFERROR for cleanup steps where a specific, harmless failure is expected.

Bulk cleaning with a macro

When the same key column arrives dirty every week, cleaning inside every lookup wastes calculation time. Normalize the column once instead. Describe the job to the Excel VBA Macro Creator.

Input prompt: “Normalize the selected key column: replace non-breaking and zero-width spaces, trim extra spaces, then store the keys as text. Refuse to run on formulas. Report how many cells changed and the distinct key count before and after.”

Expected output: a routine like this one. Review it before running.

Option Explicit

' True  = store keys as text (keeps leading zeros)
' False = convert digit-only keys (15 digits max) to numbers
Private Const KEYS_AS_TEXT As Boolean = True

Public Sub NormalizeKeyColumn()
    Dim rng As Range, data As Variant
    Dim r As Long, n As Long
    Dim original As String, cleaned As String
    Dim oldType As Integer
    Dim processed As Long, changed As Long
    Dim hasFormulas As Boolean
    Dim before As Object, after As Object

    On Error Resume Next
    Set rng = Application.InputBox("Select the key cells (no header row):", _
                                   "Normalize keys", Selection.Address, Type:=8)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub
    If rng.Areas.Count > 1 Or rng.Columns.Count <> 1 Then
        MsgBox "Select a single contiguous column.", vbExclamation
        Exit Sub
    End If

    Set rng = Intersect(rng, rng.Worksheet.UsedRange)
    If rng Is Nothing Then Exit Sub

    If IsNull(rng.HasFormula) Then
        hasFormulas = True
    Else
        hasFormulas = rng.HasFormula
    End If
    If hasFormulas Then
        MsgBox "The selection contains formulas. Paste values first.", vbExclamation
        Exit Sub
    End If

    n = rng.Rows.Count
    If n = 1 Then
        ReDim data(1 To 1, 1 To 1)
        data(1, 1) = rng.Value2
    Else
        data = rng.Value2
    End If

    Set before = CreateObject("Scripting.Dictionary")
    Set after = CreateObject("Scripting.Dictionary")
    before.CompareMode = vbTextCompare   ' matches XLOOKUP's case-insensitivity
    after.CompareMode = vbTextCompare

    For r = 1 To n
        If Not IsError(data(r, 1)) Then
            If Len(CStr(data(r, 1))) > 0 Then
                processed = processed + 1
                oldType = VarType(data(r, 1))
                original = CStr(data(r, 1))

                cleaned = Replace(original, ChrW(160), " ")
                cleaned = Replace(cleaned, ChrW(8203), "")
                cleaned = Replace(cleaned, vbTab, " ")
                cleaned = Replace(cleaned, vbCr, " ")
                cleaned = Replace(cleaned, vbLf, " ")
                cleaned = Application.WorksheetFunction.Trim(cleaned)

                If KEYS_AS_TEXT Then
                    data(r, 1) = cleaned
                ElseIf Len(cleaned) > 0 And Len(cleaned) <= 15 _
                       And Not cleaned Like "*[!0-9]*" Then
                    data(r, 1) = CDbl(cleaned)
                Else
                    data(r, 1) = cleaned
                End If

                If original <> cleaned Or oldType <> VarType(data(r, 1)) Then
                    changed = changed + 1
                End If
                before(original) = True
                after(CStr(data(r, 1))) = True
            End If
        End If
    Next r

    If KEYS_AS_TEXT Then rng.NumberFormat = "@" Else rng.NumberFormat = "General"
    rng.Value2 = data

    MsgBox "Cells processed: " & processed & vbCrLf & _
           "Cells changed: " & changed & vbCrLf & _
           "Distinct keys before: " & before.Count & vbCrLf & _
           "Distinct keys after: " & after.Count & vbCrLf & vbCrLf & _
           IIf(after.Count < before.Count, _
               "WARNING: distinct keys dropped. Different values now collide " & _
               "(hidden characters or leading zeros). Review before using as a key.", _
               "Distinct count unchanged."), _
           vbInformation, "Normalize keys"
End Sub

Run it on both key columns, one at a time, with the same KEYS_AS_TEXT setting. Duplicate the sheet first, because macros clear Excel’s undo stack. The Scripting.Dictionary object is available on Windows only.

Sanity checks after the macro

Run these on the cleaned key column (A2:A500 in the examples):

• No leftover non-breaking spaces: =SUMPRODUCT(--ISNUMBER(FIND(UNICHAR(160),A2:A500))) should return 0.
• Type consistency: =SUMPRODUCT(--ISNUMBER(A2:A500)) should return 0 in text mode, or the count of non-blank cells in number mode.
• Match rate: =SUMPRODUCT(--ISNUMBER(MATCH(A2:A500,Orders!A2:A900,0)))/COUNTA(A2:A500) gives the share of keys that now find a partner. Compare it with the share you expect from the source systems.

If the same check runs every month, log the match rate and paste it into the Data Trend Analyzer. With monthly values of 99.1%, 98.9%, 99.3%, 72.4%, and 72.0%, the expected output is a level shift flagged at the fourth month, a drop of roughly 27 points. A step change like that points to a source-system change, such as a new export format, rather than random dirty rows.

Edge cases to watch

Date columns stored as serial numbers will be converted to text of the serial value (for example, 46000) if you run the macro in text mode. Do not run it on dates.

A falling distinct count after normalization means two different raw keys were the same key all along. "A-100 " and "A-100" now collide, and XLOOKUP returns the first match. Decide which record wins before trusting the lookup.

Identifiers longer than 15 digits must stay text. Once Excel converts one to a number, the trailing digits are already gone and no formula can restore them.

Final tip: To check a single stubborn key, enter =UNICODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)) in a cell on Excel 365. It spills the code of every character in the key, so a stray 160 or 8203 stands out among the normal 48 to 90 codes.