Excel XLOOKUP Wildcard Match: How to Use It

Formulas & functions · Updated November 22, 2026

Yes, Excel’s XLOOKUP function supports wildcard matching when you set its match_mode argument to 2. In the simplest form, you write =XLOOKUP("*"&A1&"*", B2:B100, C2:C100, , 2) to find the first value in B2:B100 that contains the text in A1, and return the corresponding value from C2:C100. This article explains how wildcards work with XLOOKUP, how to choose the right match mode, and how to avoid common pitfalls.

What Are Wildcards in Excel?

Wildcards are special characters that stand in for other characters in a text string. Excel recognizes three wildcards:

  • Asterisk (*) – matches any sequence of characters, including zero characters. For example, "*cat*" matches “cat”, “category”, and “concatenate”.
  • Question mark (?) – matches exactly one character. For example, "b?t" matches “bat”, “bet”, and “bit”, but not “beet”.
  • Tilde (~) – escapes the next character, so you can search for a literal asterisk or question mark. For example, "*~?*" matches any text that contains a question mark.

Wildcards are not limited to XLOOKUP; they also work in VLOOKUP, HLOOKUP, MATCH, SUMIF, COUNTIF, and other functions that accept text criteria.

How XLOOKUP Handles Wildcards

XLOOKUP has a fourth argument, match_mode, which controls how it matches the lookup value. The default is 0 (exact match). To enable wildcards, you set match_mode to 2. The syntax is:

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

When match_mode is 2, XLOOKUP treats the lookup_value as a wildcard pattern. It searches the lookup_array for the first cell that matches the pattern, using the same wildcard rules as other Excel functions. The function returns the corresponding value from the return_array. If no match is found, you can supply an optional if_not_found value (the third argument) to display instead of the #N/A error.

For example, suppose you have product names in A2:A20 and prices in B2:B20. To find the price of the first product that contains the word “pro”, you would use:

=XLOOKUP("*pro*", A2:A20, B2:B20, "Not found", 2)

This returns the price from the first row where the product name includes “pro”. If no such product exists, it returns “Not found”.

How to Use a Cell Reference as the Wildcard Pattern

Often you want the wildcard pattern to come from a cell, so you can change it without editing the formula. To do this, concatenate the wildcard characters with the cell reference using the ampersand (&). For example, if cell D1 contains the text “pro”, the formula becomes:

=XLOOKUP("*"&D1&"*", A2:A20, B2:B20, "Not found", 2)

This matches any product name that contains the text in D1. If you want to match names that start with the text, use D1&"*"; to end with, use "*"&D1. For an exact match ignoring case, you can use match_mode 0 (exact) which is case-insensitive by default.

Choosing the Right Match Mode

XLOOKUP offers four match modes:

  • 0 – Exact match (default). Looks for an exact match, with no wildcards. Good for IDs, codes, or when you need precise equality.
  • -1 – Exact match or next smaller. Finds the largest value in the lookup_array that is less than or equal to the lookup value. Requires the lookup_array to be sorted in ascending order.
  • 1 – Exact match or next larger. Finds the smallest value that is greater than or equal to the lookup value. Requires the lookup_array to be sorted in ascending order.
  • 2 – Wildcard match. Enables wildcards in the lookup_value. This mode does not require sorting.

For most text lookups, you will use 0 or 2. Use 0 when you need an exact match, and 2 when you need a partial match. Note that wildcard matching is case-insensitive; XLOOKUP does not distinguish between uppercase and lowercase letters.

Common Problems and How to Avoid Them

Wildcard matching can lead to unexpected results if you are not careful. Here are the most common pitfalls:

  • Accidental wildcard characters in the lookup value – If your lookup value contains a literal asterisk or question mark, Excel treats it as a wildcard. To search for a literal asterisk, prefix it with a tilde: "*~**" matches text that contains an asterisk. For a literal question mark, use "*~?*".
  • Multiple matches – XLOOKUP returns the first match in the lookup_array. If you need all matches, use the FILTER function instead (available in Excel 2021 and Microsoft 365).
  • Case sensitivity – Wildcard matching is case-insensitive. If you need case-sensitive matching, you must use the EXACT function in combination with an array formula, or use the newer XMATCH with a custom match mode? Actually, XLOOKUP does not offer case-sensitive wildcard matching. For case-sensitive partial matches, you can use SUMPRODUCT or FILTER with ISNUMBER(SEARCH(...)) for case-insensitive, or FIND for case-sensitive.
  • Leading/trailing spaces – Wildcards match spaces as characters. If your data has extra spaces, the pattern may not match. Use the TRIM function on the data or the lookup value.
  • Empty cells – A pattern like "*" matches any non-empty cell. If you want to match empty cells, you cannot use a wildcard; use an exact match with an empty string "".

XLOOKUP vs. VLOOKUP with Wildcards

VLOOKUP also supports wildcards when its range_lookup argument is FALSE (exact match) – but only if the lookup value contains a wildcard character. In practice, VLOOKUP with wildcards works similarly to XLOOKUP with match_mode 2, but XLOOKUP has several advantages:

  • XLOOKUP can return a value from any column to the left or right, while VLOOKUP only returns from columns to the right of the lookup column.
  • XLOOKUP defaults to exact match, so you do not need to remember to set range_lookup to FALSE.
  • XLOOKUP has a built-in if_not_found argument, so you can avoid the #N/A error.
  • XLOOKUP is faster and does not require the lookup column to be the first column in the table.

If you are using Excel 2021 or Microsoft 365, XLOOKUP is the recommended function. For older versions, VLOOKUP with wildcards is still an option, but you must ensure the lookup column is the leftmost column in the table array.

Practical Examples

Let’s walk through a few realistic scenarios.

Example 1: Find a price by partial product name
Assume product names in A2:A20, prices in B2:B20. To find the price of the first product that contains “widget”:

=XLOOKUP("*widget*", A2:A20, B2:B20, "No such product", 2)

If “Super Widget” is in A5 and its price is 19.99 in B5, the formula returns 19.99.

Example 2: Use a cell for the search term
Put the search term in D1. To find the price of the first product that starts with the text in D1:

=XLOOKUP(D1&"*", A2:A20, B2:B20, "No match", 2)

If D1 contains “Super”, it matches “Super Widget” and returns its price.

Example 3: Find a name that matches a pattern with a single character
Suppose you have employee IDs like “AB1”, “AB2”, “AB3”. To find the first ID that matches “AB?” (any two letters followed by any digit):

=XLOOKUP("AB?", A2:A20, B2:B20, "Not found", 2)

This matches “AB1” if it exists.

Frequently Asked Questions

Can I use wildcards with XLOOKUP in Excel for the web or Mac?
Yes, XLOOKUP with wildcards works in Excel for Microsoft 365 on Windows, Mac, and Excel for the web. The syntax is identical. The only difference is that some older Mac versions may require the full function name; but as of recent versions, it is fully supported.

Why does my XLOOKUP with wildcards return #N/A even though I see a match?
Check for extra spaces, non-printable characters, or the wrong match_mode. Also ensure the lookup_array is a single row or column. If you used a cell reference for the pattern, make sure the cell contains text, not a number. Numbers are treated as text in wildcard matching, but if the cell contains a number, it may not match text that looks like a number.

Is XLOOKUP wildcard matching case-sensitive?
No, it is case-insensitive. If you need case-sensitive matching, you can use FILTER with ISNUMBER(FIND(...)) in Microsoft 365, or use an array formula with MATCH and EXACT in older versions.

Can I use wildcards with XLOOKUP to return multiple matches?
No, XLOOKUP returns only the first match. To return all matches, use the FILTER function, which is available in Excel 2021 and Microsoft 365. For example, =FILTER(B2:B20, ISNUMBER(SEARCH("widget", A2:A20))) returns all prices for products containing “widget”.

What is the difference between match_mode 2 and using wildcards in VLOOKUP?
In VLOOKUP, wildcards are only recognized when the range_lookup argument is FALSE (exact match). In XLOOKUP, you explicitly set match_mode to 2. Both are case-insensitive and return the first match. XLOOKUP is more flexible because it can return from any column and has a built-in not-found value.

How do I search for a literal asterisk or question mark with XLOOKUP?
Prefix the wildcard character with a tilde (~). For example, to find a product name that contains an asterisk, use "*~**". To find a product name that ends with a question mark, use "*~?". The tilde tells Excel to treat the next character literally.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.