Fixing Excel XLOOKUP That Returns #N/A When the Match Exists

You've double-checked the value, it's right there in the column, yet XLOOKUP keeps returning #N/A. This is one of the most frustrating Excel debugging loops because the formula syntax is usually fine — the problem is hiding in the data itself.

This guide walks through every credible cause of a spurious #N/A from XLOOKUP and gives you a concrete fix for each one.

What you'll learn

• How to detect number-as-text mismatches that fool XLOOKUP
• How to find and strip hidden spaces that break exact matches
• How to set match mode correctly for approximate, wildcard, and exact lookups
• How to confirm your lookup and return arrays are properly sized
• How to handle date values that display the same but are stored differently

Prerequisites

You need Excel 2019 or Microsoft 365 — XLOOKUP is not available in older versions. The examples assume you're using Windows Excel, but the same logic applies to Excel for Mac.

Why XLOOKUP Returns #N/A Even When the Match Looks Right

XLOOKUP uses exact match by default (match mode 0). That sounds simple, but Excel's idea of "exactly equal" is stricter than what you see on screen. Two cells can display the same characters and still fail to match if their underlying data types differ, if invisible characters are present, or if number formatting is masking the real stored value.

Before diving into individual fixes, here's the XLOOKUP signature to keep in mind:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

The optional arguments are where most misconfiguration happens. When you skip them, Excel defaults to exact match, no wildcard, search first-to-last. If your data requires something different, you must be explicit.

Fix 1: Numbers Stored as Text (The Most Common Culprit)

This is the issue behind the majority of unexpected XLOOKUP failures. A column might contain what looks like product IDs or invoice numbers — all digits — but some cells have them stored as text while others store them as true numbers. XLOOKUP's exact match will never equate the number 1042 with the text string "1042".

To confirm the mismatch, use TYPE() or ISNUMBER() on both the lookup value and a cell in the lookup array:

=ISNUMBER(A2)          ' TRUE if A2 holds a real number
=ISNUMBER(E2)          ' Check the lookup array cell

If one returns TRUE and the other FALSE, you have a type mismatch. The quickest fix is to coerce both sides to the same type. To force the lookup value to text:

=XLOOKUP(TEXT(A2,"0"), lookup_array, return_array)

To force it to a number:

=XLOOKUP(VALUE(A2), lookup_array, return_array)

If the entire lookup array is the problem — imported from a CSV or a database export — select the column, go to Data > Text to Columns, click Finish without changing anything. Excel re-evaluates the cells and converts numeric strings to real numbers. This is often faster than fixing formula after formula.

This same data-type trap affects COUNTIFS, which is covered in depth in fixing Excel COUNTIFS that returns zero with date range criteria — the diagnostic approach there translates directly.

Fix 2: Trailing or Leading Spaces in Lookup Values

Invisible spaces are the second most common cause. A value pasted from a web export, another spreadsheet, or a database query often carries a trailing space. On screen, "Widget A" and "Widget A " are indistinguishable unless you click into the cell and look at the formula bar.

Test for spaces with LEN():

=LEN(A2)    ' Should equal the visible character count
=LEN(E2)    ' Check the lookup array cell

If the lengths don't match, wrap TRIM() around the lookup value and, if necessary, create a helper column with trimmed values in your lookup array:

=XLOOKUP(TRIM(A2), lookup_array, return_array)

For non-breaking spaces (common in data copied from HTML tables), TRIM() alone is not enough. Use SUBSTITUTE() to strip character 160 first:

=XLOOKUP(TRIM(SUBSTITUTE(A2,CHAR(160)," ")), lookup_array, return_array)

If you recognize this pattern from VLOOKUP debugging, the same fix applies there too — it's documented in fixing broken VLOOKUP results when your data has trailing spaces.

Fix 3: Match Mode Is Set Incorrectly

XLOOKUP's fifth argument controls how it compares the lookup value to the array. The four modes are:

Value	Behavior
0	Exact match (default). Returns #N/A if no exact match.
-1	Exact match or next smaller item.
1	Exact match or next larger item.
2	Wildcard match — *, ?, and ~ are active.

If you're searching for a partial string and didn't set match mode to 2, XLOOKUP will look for a literal asterisk in your data and fail. Conversely, if your lookup value accidentally contains an asterisk (say, a product code like SKU*44) and match mode is 2, XLOOKUP treats it as a wildcard and may match the wrong row.

Always be explicit about the mode. For exact lookups, leave it at 0 or write it out. For partial matches:

=XLOOKUP("*"&A2&"*", lookup_array, return_array, "Not found", 2)

Fix 4: Lookup Array and Return Array Sizes Don't Match

XLOOKUP doesn't require the arrays to be the same size — but when they differ in an unexpected way, you can get #N/A or a #VALUE! error that masquerades as a lookup failure.

Check the dimensions directly:

=ROWS(E2:E100)     ' Lookup array row count
=ROWS(F2:F100)     ' Return array row count

They must be equal when both are vertical ranges. A common mistake is anchoring one array but not the other when the formula is copied down:

// Wrong: return array drifts as formula is copied
=XLOOKUP(A2, $E$2:$E$100, F2:F100)

// Correct: both arrays are fully anchored
=XLOOKUP(A2, $E$2:$E$100, $F$2:$F$100)

Use absolute references ($) for both the lookup array and return array unless you intentionally need them to shift.

Fix 5: Wildcard Characters Need Explicit Wildcard Mode

This deserves its own section because it trips up experienced users. If your lookup value contains *, ?, or ~ as literal characters (product codes, file paths, regex-like strings), XLOOKUP in default mode treats them literally — which is correct behavior. But if you enable wildcard mode (match mode 2) without realizing your data contains those characters, matches become unpredictable.

To search for a literal asterisk or question mark in wildcard mode, escape it with a tilde:

=XLOOKUP("SKU~*44", lookup_array, return_array, "Not found", 2)

This tells Excel to treat the * as a real character, not a wildcard.

Fix 6: Date Values That Look Identical But Aren't

Dates in Excel are stored as serial numbers. The display format is just formatting. Two cells that both show 01/15/2024 can store different serial numbers if one was typed manually and one came from an import with time component attached.

A date with a time component like 2024-01-15 08:30:00 has a serial number of roughly 45306.354, not the clean integer 45306 that a date-only entry would have. XLOOKUP with exact match will not find it when comparing against the clean date.

Strip the time portion with INT() on both sides:

=XLOOKUP(INT(A2), INT(lookup_array), return_array)

Note that wrapping an array inside INT() returns a calculated array — this works in Microsoft 365 and Excel 2019 with dynamic array support. If you're on an older version, build a helper column with =INT(E2) and use that as your lookup array instead.

The same kind of invisible mismatch affects INDEX MATCH, as explained in fixing Excel INDEX MATCH that returns the wrong row when data is unsorted.

Fix 7: Case Sensitivity Isn't the Problem (But Encoding Can Be)

XLOOKUP is case-insensitive by design — "apple" and "APPLE" will match. So if you've been blaming case, that's not it.

What can cause a near-invisible mismatch is character encoding. Data exported from some systems uses lookalike Unicode characters — a hyphen-minus (U+002D) versus an en-dash (U+2013), or a standard space versus a thin space. These look identical in a cell but are genuinely different characters.

To check, compare the CODE() of individual characters or use EXACT() (which is case-sensitive and encoding-aware):

=EXACT(A2, E2)    ' Returns TRUE only if every character matches exactly

If EXACT() returns FALSE on two cells that look the same, you have an encoding difference. The practical fix is to clean the data at the source — if that's not possible, use SUBSTITUTE() to replace the offending character with the expected one before passing it to XLOOKUP.

Common Pitfalls When Debugging XLOOKUP

A few habits that slow down diagnosis:

• Trusting the visual. Never assume two values are equal because they look the same. Always verify with EXACT(), LEN(), and ISNUMBER().
• Forgetting to anchor array references. When you copy a formula down, an unanchored lookup array drifts and silently changes what XLOOKUP is searching through.
• Using IFERROR to suppress the real error. Wrapping IFERROR(XLOOKUP(...), "") hides the problem. While debugging, remove IFERROR so you see the actual error type.
• Importing data without auditing types. Any time data comes from a CSV, database export, or external system, run a quick ISNUMBER() / ISTEXT() check on both the lookup value and the lookup array before building your formula.
• Confusing #N/A with #VALUE!. A #VALUE! error usually signals an array size mismatch or an invalid argument, not a lookup failure. Pay attention to the specific error code — it guides the fix.

This kind of silent data mismatch isn't unique to Excel. If you work with Python alongside your spreadsheets, the same principle applies to database lookups — see fixing Python sqlite3 queries that return no rows despite matching data for a parallel walkthrough.

Wrapping Up: A Diagnostic Checklist

When XLOOKUP returns #N/A and the match clearly exists, run through this sequence before touching your formula:

1. Check data types. Run =ISNUMBER() on both the lookup value and a sample cell in the lookup array. If they differ, coerce with VALUE() or TEXT().
2. Check lengths. Run =LEN() on both sides. A mismatch means hidden spaces — use TRIM() and, for non-breaking spaces, SUBSTITUTE(CHAR(160)).
3. Verify match mode. If you're doing a partial or wildcard match, confirm you've set match mode to 2. If you're doing exact match, confirm there's no accidental wildcard character in your data.
4. Anchor your arrays. Make sure both the lookup array and return array use absolute references ($E$2:$E$100) unless you have a specific reason not to.
5. Strip time from dates. If the lookup column is a date, wrap both the lookup value and the array in INT() to remove any time component.

Most XLOOKUP #N/A errors dissolve once you address one of these five issues. The formula itself is rarely the problem — the data almost always is.