Excel XLOOKUP with Wildcard: How to Use It

Formulas & functions · Updated November 22, 2026

Yes, XLOOKUP supports wildcards, and you use them exactly like you would with VLOOKUP: put an asterisk (*) for any number of characters, a question mark (?) for a single character, or a tilde (~) to treat the next character literally. The key is to set the match_mode argument to 2, which tells XLOOKUP to interpret wildcards; by default (match_mode 0) it looks for an exact match and treats * and ? as literal text.

Why Wildcards Matter in Lookups

Real-world data is rarely tidy. You might have a product list where the same item appears as “Blue Widget 12”, “Widget Blue 12”, or “BLUE WIDGET – 12”. A normal exact-match lookup fails on those variations. Wildcards let you match a pattern instead of a full string, so you can find the row you need without cleaning every cell first. This is especially useful in inventory sheets, customer lists, and any dataset where prefixes or suffixes vary.

How XLOOKUP Wildcard Syntax Works

The full XLOOKUP syntax is:

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

For wildcards, the fifth argument (match_mode) must be 2. The lookup_value itself contains the wildcard characters. For example, with product names in A2:A20 and prices in B2:B20, this formula finds the price of the first product that starts with “Blue”:

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

If the first matching cell is A5, the formula returns the value from B5. Note that wildcard matching is case-insensitive, so “blue*” matches “Blue Widget” as well.

Wildcard Characters You Can Use

  • Asterisk (*) – matches any sequence of characters, including none. Example: "*Widget*" finds any cell containing “Widget”.
  • Question mark (?) – matches exactly one character. Example: "W?dget" matches “Widget” and “Wodget” but not “Widgget”.
  • Tilde (~) – escapes the next character so it is treated literally. To match a literal asterisk, use "~*"; to match a literal question mark, use "~?".

These are the same wildcards Excel uses in filtering and in the Find dialog. They are not regular expressions; you cannot use +, [, or . as pattern operators.

How to Set Match Mode Correctly

Match mode accepts four values:

  • 0 – exact match (default). Wildcards are treated as literal characters.
  • -1 – exact match or next smaller item (requires sorted data).
  • 1 – exact match or next larger item (requires sorted data).
  • 2 – wildcard match.

If you forget to set match_mode to 2, XLOOKUP will look for the literal text “Blue*” and return the not_found text (or #N/A if you omit it). A common mistake is to put the wildcard in the lookup_array instead of the lookup_value—that does not work. The wildcard must be in the first argument.

Real-World Example: Finding a Partial Product Code

Suppose you have a parts list in A2:A100 (part codes like “BRK-001A”) and descriptions in B2:B100. You want the description for any part that contains “BRK” and ends with “A”. Use:

=XLOOKUP("*BRK*A", A2:A100, B2:B100, "No match", 2)

If the first part matching that pattern is “BRK-045A” in A10, the formula returns B10. If no match exists, it returns “No match”. This is far simpler than using INDEX/MATCH with SEARCH or ISNUMBER.

Comparison: XLOOKUP vs. VLOOKUP with Wildcards

VLOOKUP also supports wildcards, but you do not need to set a match mode—wildcards are always active in VLOOKUP’s approximate match (TRUE) and exact match (FALSE) modes. However, VLOOKUP can only look to the right of the lookup column, and it returns the first match in the table. XLOOKUP improves on this in three ways:

  • It can return a value from any column, left or right, without reordering data.
  • It has a built-in if_not_found argument, so you don’t need IFERROR.
  • It supports both wildcard and exact modes explicitly, so you can control when wildcards are interpreted.

If you are on Excel 2021 or Microsoft 365, XLOOKUP is the better choice. For older versions (Excel 2019 and earlier), VLOOKUP or INDEX/MATCH with wildcards still works.

Common Problems and How to Fix Them

  • #N/A error – The pattern does not match any cell. Check for extra spaces or inconsistent capitalization (though wildcard matching is case-insensitive, it is not space-insensitive).
  • Returns wrong value – XLOOKUP returns the first match in the lookup array. If your data is not sorted, that may not be the value you expect. Use search_mode if needed, or sort your data.
  • Literal asterisk not found – Use "~*" in the lookup_value to search for a literal asterisk character.
  • Wildcards not working at all – Ensure match_mode is 2. Double-check that you are not using a comma instead of a semicolon if your regional settings use semicolons as argument separators.

Alternatives to Wildcard XLOOKUP

If you need more complex pattern matching, consider these options:

  • FILTER + ISNUMBER(SEARCH()) – For multiple matches or to return all rows, use =FILTER(B2:B20, ISNUMBER(SEARCH("widget", A2:A20))). SEARCH is case-insensitive and supports wildcards only if you use it with wildcard characters, but it is not as direct.
  • INDEX/MATCH with wildcard – In older Excel, =INDEX(B2:B20, MATCH("*widget*", A2:A20, 0)) works exactly like XLOOKUP’s wildcard mode.
  • Regular expressions – Excel does not have native regex functions, but you can use VBA or Power Query for advanced pattern matching. Power Query’s Table.SelectRows with Text.Contains is a no-code alternative for complex filters.

Frequently Asked Questions

Can I use a cell reference as the lookup_value with wildcards?

Yes. If you put a value like “Blue” in D1, you can use =XLOOKUP("*"&D1&"*", A2:A20, B2:B20, "Not found", 2) to find any cell containing the text in D1. Just concatenate the asterisks with the cell reference using the ampersand.

Does XLOOKUP wildcard work with numbers?

Wildcards are text-based, so they only work when the lookup_value is text. If your lookup column contains numbers, you can still use wildcards by converting the lookup_value to text, but it is usually simpler to use exact match. For example, =XLOOKUP("12*", TEXT(A2:A20,"0"), B2:B20, "Not found", 2) would require an array formula, which is not practical. Stick to text columns for wildcard lookups.

How do I find the second match with XLOOKUP wildcard?

XLOOKUP returns only the first match. To get the second, you can use FILTER to return all matches and then index into that array: =INDEX(FILTER(B2:B20, ISNUMBER(SEARCH("widget", A2:A20))), 2). This is a more robust approach for multiple matches.

Is wildcard matching case-sensitive?

No, wildcard matching in XLOOKUP is case-insensitive, just like VLOOKUP and MATCH. If you need case-sensitive matching, you must use EXACT in an array formula or use Power Query.

Does XLOOKUP wildcard work 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. Just ensure your version supports XLOOKUP (Excel 2021 or Microsoft 365).

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.