Why Your XLOOKUP Returns #N/A for Partial Matches

by

Why Your XLOOKUP Returns #N/A for Partial Matches

You replaced every VLOOKUP in last quarter’s customer churn model with XLOOKUP — Microsoft’s recommended modern alternative. The migration was clean. Until your manager asked you to find “any customer whose name *contains* the word ‘Industries’.” XLOOKUP returns #N/A. You try wildcards: =XLOOKUP("*Industries*", A:A, B:B). Still #N/A. You try the asterisks separately. Still #N/A. Stack Overflow answers swear XLOOKUP supports wildcards. Microsoft’s own documentation shows the syntax. And yet nothing matches. The fifth argument — match_mode — is what gives XLOOKUP its wildcard powers, and it is 0 (exact match only) by default. Until you change it, every partial match silently fails.

Before You Start: The 60-Second Diagnostic

Three checks:

  • Identify the missing argument: Look at your XLOOKUP. If it has fewer than five arguments, you are using exact match by default.
  • Test with explicit wildcards: A wildcard search without match_mode = 2 will always fail, even with * in the search string.
  • Check if the value contains wildcards literally: If the cell contains an actual * character (like in product codes), XLOOKUP needs special handling.

Step-by-Step Solution

H2: Set the Correct match_mode

XLOOKUP’s full syntax is:

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

The match_mode argument values:

  • 0 (default) — Exact match. Wildcards are NOT recognized.
  • -1 — Exact or next smaller.
  • 1 — Exact or next larger.
  • 2 — Wildcard match. * matches any string, ? matches any single character.

To find any customer name containing “Industries”:

=XLOOKUP("*Industries*", A:A, B:B, "Not found", 2)

The 2 at the end is essential.

H2: Handle Wildcards in the Lookup Value Itself

If your search string contains a literal asterisk (like SKU “PRD*ABC*123”), XLOOKUP in wildcard mode will treat each * as a pattern. Escape them with ~:

=XLOOKUP("PRD~*ABC~*123", A:A, B:B, "Not found", 2)

The tilde tells XLOOKUP to interpret the next character literally.

H2: Case-Sensitive Partial Matching

XLOOKUP wildcard mode is case-*insensitive* — “industries” matches “INDUSTRIES” matches “Industries”. If you need case-sensitive matching, XLOOKUP cannot do it natively. Use this combination:

=INDEX(B:B, MATCH(TRUE, EXACT(LEFT(A:A, LEN("Industries")), "Industries"), 0))

Or, in Excel 365, use FILTER:

=FILTER(B:B, ISNUMBER(FIND("Industries", A:A)))

FIND is case-sensitive; SEARCH is not. Choose accordingly.

H2: Fuzzy Matching for Misspellings

Neither XLOOKUP nor wildcards handle typos. For approximate matches (e.g., “Industies” should match “Industries”), use Power Query’s Fuzzy Merge feature:

  1. Data → Get & Transform → From Table/Range for both your lookup and source tables.
  2. In the query editor, Home → Merge Queries → Merge.
  3. Check Use fuzzy matching to perform the merge and set the similarity threshold (0.8 = 80% match required).

This is the only built-in Excel mechanism for typo-tolerant lookups.

Information Gain Box: The Hidden Number-as-Text Wildcard Trap

Here is what no tutorial covers: XLOOKUP wildcards only work on text data — but the wildcard mode silently accepts numeric inputs and returns #N/A without explaining why. If your lookup column contains numbers (even numbers that look like text-codes like 1099), wildcard matching cannot pattern-match them because Excel sees them as numeric values, not strings. The fix is to force-cast both sides to text:

=XLOOKUP("*99*", TEXT(A:A, "@"), B:B, "Not found", 2)

The TEXT(A:A, "@") converts the entire lookup column to text on the fly. Without this cast, wildcard searches against numeric columns return zero matches even when the pattern is obviously present. This single behavior explains the most common “XLOOKUP wildcard does not work” reports.

Comparison Table: Wrong Way vs. Correct Way

Goal Wrong Way (Returns #N/A) Correct Way
Exact match =XLOOKUP(A2, B:B, C:C) ✓ works Same — default mode is exact
Wildcard match =XLOOKUP("*A*", B:B, C:C) =XLOOKUP("*A*", B:B, C:C, "Not found", 2)
Literal * in lookup =XLOOKUP("PRD*A", B:B, C:C, , 2) (treats * as wildcard) =XLOOKUP("PRD~*A", B:B, C:C, , 2) (tilde-escape)
Case-sensitive partial XLOOKUP cannot do this =FILTER(B:B, ISNUMBER(FIND("term", A:A)))
Numeric column wildcard =XLOOKUP("*99*", A:A, B:B, , 2) =XLOOKUP("*99*", TEXT(A:A, "@"), B:B, , 2)
Fuzzy/typo match XLOOKUP cannot do this Power Query Fuzzy Merge with similarity threshold
Multiple matches XLOOKUP returns first only Switch to FILTER for all matches

Original Image Descriptions

Screenshot 1: Show an XLOOKUP formula =XLOOKUP("*Industries*", A:A, B:B) returning #N/A. Display the four-argument signature in the function helper tooltip. Draw a red circle around the missing fifth argument position in the tooltip and a red arrow pointing from the tooltip to the #N/A result. Add a red annotation: “Missing match_mode = 2 → wildcards ignored.”

Screenshot 2: Show the corrected formula =XLOOKUP("*Industries*", A:A, B:B, "Not found", 2) returning the first matching row. Highlight the 2 at the end of the formula. Draw a red circle around the 2 and a red arrow to the working result. Add a red annotation: “The fifth argument turns on wildcards.”

Frequently Asked Questions

Q: Why does XLOOKUP not enable wildcards by default like SEARCH does?
A: XLOOKUP’s design prioritizes exact-match safety. Wildcard matching can return unintended results — for example, searching for “Acme” in wildcard mode would match “Acme Inc.”, “Acme Corp.”, and any other variant. Microsoft chose exact-match-by-default so users explicitly opt into approximate behavior with match_mode = 2. This is the same reason VLOOKUP requires FALSE for exact matching.

Q: Can I do a partial match without wildcards using XLOOKUP?
A: Not directly — XLOOKUP needs either an exact value or a wildcard pattern with match_mode = 2. For “contains” searches without typing *, the cleaner solution is =FILTER(B:B, ISNUMBER(SEARCH("substring", A:A))) which returns all matching rows. SEARCH is case-insensitive; use FIND if case matters.

Q: My XLOOKUP wildcard works in one workbook but fails in another with the same data. Why?
A: Almost always a data type difference. The working workbook has the lookup column stored as text; the broken one has it as numbers (despite looking identical). Run =ISTEXT(A2) on each to confirm. Wrap the lookup column in TEXT(A:A, "@") to force consistent string interpretation across both files.