Yes, Excel’s XLOOKUP function supports wildcard characters (* and ?) for partial-match lookups, but you must set the match_mode argument to 2. For example, =XLOOKUP("*"&D2&"*", A2:A20, B2:B20, "Not found", 2) finds the first product name in A2:A20 that contains the text in D2 and returns its price from B2:B20.
Background: Why Wildcards Matter in Lookups
Exact-match lookups fail when your lookup value isn’t typed identically to the data. For instance, you might have a cell with “Acme Corp” but your data lists “Acme Corporation” or “Acme Corp (NYC)”. Wildcards let you match a pattern instead of an exact string. In Excel, the asterisk (*) represents any sequence of characters (including zero), and the question mark (?) represents exactly one character. XLOOKUP is the modern replacement for VLOOKUP and INDEX/MATCH, and it’s the only lookup function that lets you control wildcard behavior directly through its match_mode argument.
How XLOOKUP Wildcard Search Works
XLOOKUP’s syntax is: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The fifth argument, match_mode, accepts these 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. This enables * and ? in the lookup_value.
When you set match_mode to 2, XLOOKUP treats the lookup_value as a pattern. For example, with data in A1:A5 containing “Apple”, “Banana”, “Cherry”, “Date”, “Elderberry”, the formula =XLOOKUP("B*", A1:A5, A1:A5, "No match", 2) returns “Banana” because it’s the first item starting with “B”. The wildcard search is case-insensitive by default, and it returns the first match in the lookup array (unless you change search_mode).
How to Choose: Exact vs. Wildcard vs. Approximate
Deciding which match_mode to use depends on your data and goal:
- Use 0 (exact) when your lookup values are clean and you need a precise match. This is fastest and most predictable.
- Use 2 (wildcard) when you’re searching for a partial name, a code with a known prefix/suffix, or when data entry is inconsistent (e.g., “Prod-123” vs. “prod123”).
- Use -1 or 1 (approximate) only for numeric ranges (like tax brackets) where your data is sorted and you need the closest value. These modes ignore wildcards.
If you need to match multiple criteria (e.g., find a product by both name and color), you can combine XLOOKUP with the & operator: =XLOOKUP(D2&E2, A2:A20&B2:B20, C2:C20, "Not found", 2) – but note that wildcards in the concatenated lookup value work only if you set match_mode to 2, and the pattern applies to the entire concatenated string.
Common Problems and How to Fix Them
Here are frequent pitfalls when using wildcards with XLOOKUP:
- Wildcards not working: You forgot to set match_mode to 2. Check the fifth argument – it must be exactly 2.
- Literal asterisk or question mark in data: If your data contains actual * or ? characters, wildcard search will misinterpret them. To match a literal asterisk, use a tilde before it:
~*. For a literal question mark, use~?. For example, to find a cell containing “5*”, use=XLOOKUP("*5~**", A:A, B:B, "", 2). - Unexpected first match: XLOOKUP returns the first match. If you need the last match, set search_mode to -1 (search from last to first).
- Case sensitivity: By default, wildcard search is case-insensitive. If you need case-sensitive matching, you’ll need to use a different approach, like FILTER with EXACT, because XLOOKUP doesn’t offer a case-sensitive option.
- #VALUE! or #N/A errors: If lookup_array is a different size than return_array, you’ll get a #VALUE! error. If no match is found and you didn’t provide an if_not_found value, you get #N/A.
Comparison: XLOOKUP vs. VLOOKUP vs. INDEX/MATCH for Wildcards
VLOOKUP also supports wildcards, but it requires setting the range_lookup argument to FALSE (or 0) and it only searches the first column of a table. For example, =VLOOKUP("*"&D2&"*", A2:B20, 2, FALSE) works similarly, but VLOOKUP can’t look to the left, and it’s harder to control search direction. INDEX/MATCH can also use wildcards: =INDEX(B2:B20, MATCH("*"&D2&"*", A2:A20, 0)) – but MATCH’s wildcard behavior is always on when match_type is 0, and you can’t turn it off. XLOOKUP is more flexible because you can explicitly choose wildcard mode or exact mode, and it has a built-in if_not_found argument. For Excel 2021 or Microsoft 365, XLOOKUP is the recommended choice.
Practical Examples with Real Formulas
Assume you have a product list: names in A2:A10, prices in B2:B10. You want to find the price of any product that contains “widget” (case-insensitive). In cell D2, type “widget”, then in E2 use:
=XLOOKUP("*"&D2&"*", A2:A10, B2:B10, "No product found", 2)
This returns the price of the first product containing “widget”. If D2 is empty, the pattern becomes “**”, which matches any non-empty cell, so it returns the first price – you might want to add an IF to handle that: =IF(D2="", "Enter a search term", XLOOKUP("*"&D2&"*", A2:A10, B2:B10, "No product found", 2)).
Another example: find a product code that starts with “A” and ends with “1”. Use =XLOOKUP("A*1", A2:A10, B2:B10, "Not found", 2). If you need to match exactly one character in the middle, use ?: =XLOOKUP("A?1", A2:A10, B2:B10, "Not found", 2) matches “A1B”? No, ? matches exactly one character, so “A?1” matches “A11”, “AB1”, but not “A1” or “A123”.
Alternatives to XLOOKUP Wildcard Search
If you don’t have Excel 2021 or Microsoft 365, or if you need more advanced pattern matching, consider these alternatives:
- FILTER function (Excel 2021/365):
=FILTER(B2:B10, ISNUMBER(SEARCH(D2, A2:A10)), "No match")returns all matches, not just the first. SEARCH is case-insensitive; use FIND for case-sensitive. - Regular expressions (RegEx) – not native to Excel formulas, but you can use VBA or Power Query (Get & Transform) for complex patterns. In Power Query, you can use the Text.Contains function with a wildcard-like syntax, but it’s not true RegEx unless you use advanced features.
- VLOOKUP with wildcards – works in all versions, but limited as described.
- INDEX/MATCH – works in all versions, but wildcard behavior is implicit and not controllable.
Frequently Asked Questions
Can I use XLOOKUP wildcard with numbers? Yes, but you must convert the number to text in the lookup_value. For example, to match a numeric ID that starts with 12, use =XLOOKUP("12*", TEXT(A2:A10,"0"), B2:B10, "", 2) – note that TEXT converts the lookup_array to text, so the pattern works.
Why does my wildcard search return the wrong item? XLOOKUP returns the first match in the lookup_array order. If your data isn’t sorted and you expect a different match, you may need to sort or use FILTER to get all matches. Also, check that you didn’t accidentally include extra spaces or hidden characters in your pattern.
Does XLOOKUP wildcard work on Mac? Yes, the function works identically in Excel for Mac. The only difference is the keyboard shortcut for entering formulas (Cmd+Enter vs Ctrl+Shift+Enter is not needed for XLOOKUP, but for array formulas in older versions).
Can I use wildcards in the return_array? No, wildcards only apply to the lookup_value. The return_array is just a range to pull from.
How do I match a literal asterisk in XLOOKUP? Use a tilde before the asterisk: =XLOOKUP("*~**", A:A, B:B, "", 2) – the first * is a wildcard, ~* matches a literal asterisk, and the final * is a wildcard. Similarly, for a literal question mark, use ~?.
Is XLOOKUP wildcard case-sensitive? No, by default it’s case-insensitive. If you need case-sensitive, use FILTER with EXACT or use a helper column with EXACT.
Related
- Excel Template for Income Expense Tracking: How to Build One
- Excel Template for Family Budget: How to Build One
- Excel Template for HOA Budget: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Weekdays Between Dates: Formulas, Holidays, and TipsTo count weekdays between two dates in Excel, use =NETWORKDAYS(A2,B2), with the start date in A2 and the…
- Cash Flow Formula in Excel: A Step-by-Step TutorialIn Excel, net cash flow for a period is cash inflows minus cash outflows. If inflows are in…
- Excel IFS in Conditional Formatting: How to Use ItYes. In Excel for Microsoft 365 on Windows, you can use the IFS function in a conditional-formatting formula,…
- Cannot Filter in Excel? Causes, Checks, and FixesYes—Excel can filter both ordinary data ranges and tables. In Microsoft 365 for Windows, select a cell in…
- Excel Julian Date Formula: Day of Year and MoreFor a date in A2, use =INT(A2)-DATE(YEAR(A2),1,0) to return its day number within the year: January 1 is…
- Excel What-If Analysis with Goal Seek: A Practical GuideIn Excel for Microsoft 365 on Windows, use Goal Seek to find the input value that makes a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.