Troubleshooting “Data Type” Mismatches in XMATCH

An #N/A error in XMATCH indicates that the function cannot find your search value within the target lookup array. While missing data is the obvious explanation, the most frequent technical culprit is a data type mismatch. In Excel, two values that appear identical on your screen—such as the number 5041 and the text string "5041"—have different binary structures. Because XMATCH strictly defaults to exact matching without implicit type conversion, any discrepancy between numbers, text, dates, booleans, or rich entities causes the lookup to fail.

Fast Fix

Coerce your lookup values to match the target array’s data type. If searching for text stored as numbers, wrap the lookup value in double unary operators: =XMATCH(--A2, B2:B100). If searching for numbers stored as text, append an empty string: =XMATCH(A2 & "", B2:B100). Strip non-breaking spaces using =TRIM(CLEAN(A2)).

---

Diagnostic Snapshot

Indicator	Detail
Visible Error	#N/A
Function Syntax	=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
Default Behavior	match_mode = 0 (Strict exact match; no automatic type coercion)
Primary Root Cause	Numeric text vs. true integer/floating-point values
Secondary Root Cause	Hidden formatting artifacts (trailing whitespace, CHAR(160) non-breaking spaces)
Tertiary Root Cause	Querying a Rich Data Type entity directly instead of its scalar property

---

The Anatomy of Data Type Collisions in XMATCH

Unlike legacy lookup functions that occasionally accepted loose type boundaries under approximate matching, XMATCH evaluates data types strictly:

Visual Display:    [ 1042 ]    vs.    [ 1042 ]
Underlying Type:    NUMBER             TEXT
Binary Evaluation: 0x40C054...        0x31 0x30 0x34 0x32
Result in XMATCH:  NO MATCH ──> Returns #N/A

Even if cell A2 displays 1042 and cell B15 displays 1042, XMATCH(A2, B2:B20) will return #N/A if one is formatted as an integer and the other is a text string resulting from a database export, CSV import, or text manipulation formula.

---

Step-by-Step Diagnostic Procedures

Step 1: Use Diagnostic Functions to Confirm the Mismatch

Do not rely on cell alignment or ribbon formatting to determine data types. Test both cells directly:

1. In an empty cell, enter: =TYPE(A2) (where A2 is your lookup value).
2. In another cell, enter: =TYPE(B2) (where B2 is the value in your lookup array).
3. Compare the return codes:
   • 1 = Number
   • 2 = Text
   • 4 = Boolean (TRUE / FALSE)
   • 16 = Error code
   • 128 = Compound / Rich Data Type entity

If one cell returns 1 and the other returns 2, you have confirmed a numeric-versus-text mismatch.

Step 2: Resolve Numeric and Text Type Conflicts

Once the mismatch direction is identified, normalize the lookup value within the formula:

• When lookup_value is Text and lookup_array is Numeric:
  Force the text string into a number using the double unary minus operator () or VALUE():
  =XMATCH(--A2, B2:B100)
• When lookup_value is Numeric and lookup_array is Text:
  Coerce the number into a text string by concatenating an empty string:
  =XMATCH(A2 & "", B2:B100)
• When the lookup_array contains Mixed Types:
  If an imported column contains both true numbers and text numbers unpredictably, clean the array dynamically using MAP or coerce both sides within an XLOOKUP or XMATCH wrapper. For handling exact versus approximate lookup mechanics, see #N/A Errors in XLOOKUP vs VLOOKUP (The “Surgical” Difference).

Step 3: Remove Invisible Formatting and Web Spaces

Pasted data from ERP systems, web tables, or PDF converters frequently includes non-printing characters that prevent exact matches.

• Standard spaces (CHAR(32)) can be stripped with TRIM.
• Web exports frequently introduce non-breaking spaces (CHAR(160)), which TRIM ignores.

Sanitize the lookup value directly inside XMATCH:
=XMATCH(TRIM(SUBSTITUTE(A2, CHAR(160), " ")), B2:B100)

If the entire lookup column contains non-breaking spaces, apply the substitution across the lookup vector:
=XMATCH(A2, TRIM(SUBSTITUTE(B2:B100, CHAR(160), " ")))

Step 4: Audit Date Serials vs. Text Dates

Excel stores dates as sequential serial integers (e.g., January 1, 2026 is stored as 46023). If you import dates formatted as literal text strings like "2026-01-01":

• =ISNUMBER(A2) returns FALSE.
• An XMATCH searching for a serial date against text dates will fail with #N/A.

Resolution:
Use DATEVALUE to convert text dates into Excel serial numbers:
=XMATCH(A2, DATEVALUE(B2:B100))

Step 5: Rich Data Types and Linked Entity Mismatches

When using Microsoft 365 linked data types (Stocks, Geography, or organizational Power BI entities), attempting to match a plain text string against an entity cell triggers #N/A.

• If cell B2 is a Stock entity for Microsoft, the cell holds a rich data record, not the string "MSFT".
• Querying =XMATCH("MSFT", B2:B10) returns #N/A because a string cannot equal an entity object.

Resolution:
Extract the scalar property from the entity array using dot notation:
=XMATCH("MSFT", B2:B10.TickerSymbol)

If the rich data type fails to expose the requested field or returns broken metadata, consult #FIELD! Error in Rich Data Types.

---

Advanced Case: Wildcards with Text Numbers

A common trap occurs when users attempt to use wildcards (* or ?) on numbers.

• Setting match_mode = 2 enables wildcard matching in XMATCH.
• However, wildcards only function on text strings. If you pass =XMATCH("10*", B2:B100, 2) against a column of true numbers, Excel will never find a match, returning #N/A.
• To use wildcards against numeric data, you must explicitly cast the lookup array to text:
  =XMATCH("10*", B2:B100 & "", 2)

If an XMATCH formula is nested inside an array generator that spills unexpectedly or gets obstructed by neighboring cells, see #SPILL! Error – How to find the obstructing cell. If syntax validation stops you from entering the formula, refer to How to Fix “The formula contains an unrecognized text”.