XLOOKUP Wildcard Match Example

XLOOKUP’s wildcard match lets you find a value when you only know part of the lookup text, using * for any sequence and ? for a single character. You enter the partial text with a wildcard as the lookup value and set the match mode to 2. This article shows a concrete example, explains the syntax, and covers common pitfalls.

How Wildcard Matching Works

Wildcards in XLOOKUP work like in other Excel functions:

* matches any number of characters (including zero).
? matches exactly one character.
~ escapes the next character, so ~* finds a literal asterisk.

Match mode 2 is case-insensitive, so “pro” matches “PRO” and “Pro”. If you need case-sensitive, use -1 (though that is rarely needed). The search always returns the first match in the lookup array, unless you set search_mode to -1 for last-to-first.

Choosing the Right Match Mode

XLOOKUP has several match modes, and picking the wrong one is a common mistake:

0 – exact match (default). Use when you need an exact ID or code.
1 – approximate match. Use with sorted numeric data (e.g., tax brackets).
2 – wildcard match. Use when you only know part of the text.
-1 – wildcard match, case-sensitive.

If you forget to set match_mode to 2, XLOOKUP treats the asterisk as a literal character and returns “Not found” (or #N/A).

Common Problems and Fixes

#N/A error – no match found. Check that the lookup value contains the correct wildcard and that the lookup array is correct.
Wrong match – XLOOKUP returns the first match. If you need the last, use search_mode -1.
Literal asterisk – if your text actually contains “*”, use ~* to escape it.
Case sensitivity – match mode 2 ignores case. For case-sensitive, use -1.
Leading/trailing spaces – wildcards do not trim spaces; use TRIM on the lookup value if needed.

Comparing XLOOKUP Wildcards to VLOOKUP and INDEX/MATCH

VLOOKUP also supports wildcards when you set range_lookup to FALSE, but it only searches the first column. INDEX/MATCH with wildcards requires an array formula in older Excel. XLOOKUP is simpler because it handles wildcards natively and can return values from any column, not just to the right. For example, to look up a product name and return the quantity from a column left of the lookup column, XLOOKUP does it directly; VLOOKUP cannot.

When to Use Wildcards vs. Other Lookup Methods

Wildcards are ideal when your data has inconsistent formatting (e.g., “North Region” vs “Region North”) or when you only have a partial code. However, if you need to match multiple criteria (e.g., product AND region), wildcards alone won’t work; you’d need to concatenate columns or use FILTER. Also, for very large datasets, wildcard matching can be slower than exact match, so consider adding a helper column with exact keys if performance matters.

Frequently Asked Questions

Does XLOOKUP wildcard match ignore case?
Yes, with match_mode 2 it is case-insensitive. For case-sensitive, use -1.

Can I use a cell reference as the wildcard pattern?
Yes, you can concatenate: "*"&A1&"*" – this is common when the partial text is in a cell.

Why does my wildcard formula return #N/A even though the text exists?
Check for leading/trailing spaces, or that the wildcard is placed correctly. Also ensure match_mode is 2, not 0.

How do I find the last match with wildcards?
Set search_mode to -1: =XLOOKUP("*Pro*", A2:A10, B2:B10, , 2, -1).

Can I use wildcards with numbers?
Yes, but Excel treats numbers as text in the lookup. For example, "*12*" matches 123, 512, etc., but be careful with numeric formatting.

Is there a way to match multiple wildcards in one lookup?
Yes, you can combine them, e.g., "*North*East*" matches any text containing “North” then “East” in that order.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →