XLOOKUP Wildcard Contains Substring: How to Use

Yes, XLOOKUP supports wildcards and can find a cell that contains a substring. You set the match mode argument to 2 (wildcard match), then use an asterisk (*) before and after your search text, like =XLOOKUP("*"&A1&"*", lookup_range, return_range, , 2). This returns the first value in the return range whose corresponding lookup cell contains the substring in A1.

Background: Why Wildcards in XLOOKUP?

Before XLOOKUP, Excel users relied on VLOOKUP or INDEX/MATCH for lookups. VLOOKUP had wildcard support but only searched the first column and required a sorted range for approximate matches. XLOOKUP, introduced in Excel 365 and Excel 2021, was designed to overcome these limits. It searches left or right, returns multiple values, and has a dedicated match mode argument. Wildcard matching is one of those match modes, giving you a simple way to do “contains” lookups that previously required array formulas or helper columns.

Wildcards are special characters that stand in for other characters. In Excel, there are three: * (any number of characters), ? (exactly one character), and ~ (escape the next wildcard character). When you use them in XLOOKUP with match mode 2, you can match partial text, patterns, or even specific character positions.

How XLOOKUP Wildcard Syntax Works

The full XLOOKUP syntax is:

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

The fifth argument, match_mode, is what controls wildcards. Set it to 2 to enable wildcard matching. The lookup_value can contain wildcards directly, or you can combine it with cell references using the ampersand (&) operator.

For a substring match, you put an asterisk on both sides of the search text. For example, if cell B2 contains “cat”, the lookup value becomes "*cat*". This tells Excel to find any cell that contains “cat” anywhere in the text. The first match in the lookup array is returned, so the order of your data matters.

Here’s a concrete example. Suppose column A has:

• A2: “black cat”
• A3: “catapult”
• A4: “dog”

And column B has the corresponding values: 10, 20, 30. The formula =XLOOKUP("*cat*", A2:A4, B2:B4, , 2) returns 10, because “black cat” is the first cell containing “cat”. If you wanted the second match, you’d need to change the search order or use a different approach.

When to Use Wildcard vs. Exact Match

Exact match (match_mode 0) is the default and works for most lookups. Use it when your lookup value must match the entire cell content, like an ID or a product code. Wildcard match is for when you need flexibility: partial text, variable prefixes or suffixes, or when the data has inconsistent formatting.

Common use cases for wildcard contains:

• Finding a product by a partial name (e.g., “XL” to match “T-Shirt XL”)
• Searching for a name when you only know part of it
• Matching entries that start with a certain code, like "AB*"
• Finding entries that end with a suffix, like "*2024"
• Matching a pattern with single-character wildcards, like "A?C" to match “ABC”, “A1C”, etc.

Remember that wildcard matching is case-insensitive by default. If you need case-sensitive matching, you’ll need a different approach, like using EXACT with an array formula.

Common Problems and Solutions

One frequent issue is that wildcards don’t work when the lookup value is a cell reference that contains a wildcard character itself. If you want to search for a literal asterisk, you must prefix it with a tilde (~*). For example, to find a cell containing “*”, use "*~**" – the first and last asterisks are wildcards, the middle one is escaped.

Another problem is that XLOOKUP returns the first match, but you might want all matches. XLOOKUP only returns one value (or an array if you give it a return array of multiple columns). To get all matches, you’d need to use FILTER, which is a different function.

If you get a #VALUE! error, check that your match_mode is actually set to 2. If you get #N/A, no cell matches the pattern. Also, be careful with the order of arguments – the if_not_found argument comes before match_mode, so if you want to skip if_not_found, you must include an empty argument (a comma with nothing between) as shown in the examples.

Wildcards in XLOOKUP can also be slow on very large datasets because they prevent some optimizations. If you have hundreds of thousands of rows, consider using a helper column with ISNUMBER(SEARCH(…)) and then a regular lookup.

Comparison: XLOOKUP vs. VLOOKUP and INDEX/MATCH for Substrings

Before XLOOKUP, the standard way to do a substring lookup was an array formula like =INDEX(return_range, MATCH(TRUE, ISNUMBER(SEARCH("*"&A1&"*", lookup_range)), 0)), which required Ctrl+Shift+Enter in older Excel. VLOOKUP with wildcards only worked if the lookup column was the first column, and it couldn’t look to the left. INDEX/MATCH was more flexible but still required array entry for substring searches.

XLOOKUP simplifies this dramatically. It’s a single formula, no array entry needed, and it works in any direction. It also handles not-found cases gracefully with the if_not_found argument. The main limitation is that it’s only available in Excel 365 and Excel 2021, so users on older versions must stick with the older methods.

If you need to return multiple matches, FILTER is the better choice. For example, =FILTER(B2:B100, ISNUMBER(SEARCH("cat", A2:A100))) returns all values in B where A contains “cat”. This is more powerful than XLOOKUP for substring matching when you need all results.

Alternatives to Wildcards for Contains Lookups

Sometimes wildcards aren’t the best tool. If you need to match a substring that might appear in different cases, or if you need to match whole words, consider these alternatives:

• SEARCH and ISNUMBER: Use =ISNUMBER(SEARCH("cat", A2)) to get TRUE/FALSE. Combine with FILTER or INDEX/MATCH.
• FIND: Case-sensitive version of SEARCH. Use =ISNUMBER(FIND("cat", A2)).
• Regular expressions (RegEx): In Excel 365, you can use REGEXTEST, REGEXEXTRACT, and REGEXREPLACE functions (if available in your version) for more complex patterns.
• Power Query: For complex data cleaning and matching, Power Query has a “Contains” operator in its filtering.

Each has its strengths. SEARCH is case-insensitive and supports wildcards too, but it returns the position, not a value. RegEx is the most powerful but has a steeper learning curve.

Frequently Asked Questions

Does XLOOKUP wildcard work with numbers?

Yes, but you need to convert the number to text. Wildcards work on text, so if your lookup value is a number, Excel will convert it to text for the match. For example, =XLOOKUP("*123*", A2:A10, B2:B10, , 2) will match cells containing “123” as text. Be aware that this can sometimes match unintended values, like “1234” also containing “123”.

Can I use XLOOKUP wildcard to match from the end?

Yes. To match text that ends with a specific substring, put the asterisk at the beginning only: "*end". To match text that starts with a substring, put the asterisk at the end: "start*". To match anywhere, use asterisks on both sides.

Why is my XLOOKUP wildcard not working?

Check three things: (1) Make sure the match_mode argument is set to 2, not 0 or 1. (2) Verify that you haven’t accidentally used a tilde (~) in your lookup value, which escapes wildcards. (3) Ensure your lookup_array is a single row or column – XLOOKUP doesn’t handle multi-column ranges without the return_array being the same size.

Can XLOOKUP return multiple matches with wildcards?

No, XLOOKUP returns only the first match. If you need all matches, use the FILTER function with ISNUMBER(SEARCH(…)) as the condition. For example, =FILTER(B2:B100, ISNUMBER(SEARCH("cat", A2:A100))) returns all values in B where A contains “cat”.

Is XLOOKUP wildcard case-sensitive?

No, by default it is not. If you need case-sensitive matching, you’ll have to use a different method, such as an array formula with FIND. For example, =INDEX(B2:B100, MATCH(TRUE, ISNUMBER(FIND("cat", A2:A100)), 0)) entered with Ctrl+Shift+Enter in older Excel.

Does XLOOKUP wildcard work in Excel 2019 or older?

No, XLOOKUP is only available in Excel 365 and Excel 2021. For older versions, you can use VLOOKUP with wildcards (if your lookup column is the first column) or an array formula with INDEX/MATCH and SEARCH.

Related

• Early Space Colonization Ideas That Failed
• RSS Readers vs Social Media: Should You Switch?
• Tipping Mechanics: What Work Deserves a Tip?