The quickest way to do a partial match lookup in Excel is to use XLOOKUP with wildcards: =XLOOKUP("*"&D2&"*",A2:A10,B2:B10,,2). For older versions, use VLOOKUP or INDEX/MATCH with wildcards, or the FILTER function to return all matches. This article explains each method, when to use it, and how to avoid common errors.
Why Wildcards Are the Key
Excel’s lookup functions normally demand an exact match. Wildcards let you search for a fragment of text instead of the whole cell. There are three wildcard characters in Excel:
- Asterisk (*) – matches any number of characters (including zero). For example,
"*cat*"matches “cat”, “catalog”, and “concatenate”. - Question mark (?) – matches exactly one character.
"c?t"matches “cat” and “cut” but not “cart”. - Tilde (~) – escapes the next character so you can search for a literal asterisk or question mark.
"*~**"finds cells containing an asterisk.
When you combine a wildcard with a cell reference, you build a partial match. For instance, if D2 contains “cat”, the search string "*"&D2&"*" becomes “*cat*”, which finds any cell that contains “cat” anywhere.
Method 1: XLOOKUP with Wildcards (Excel 365 / 2021)
XLOOKUP is the modern, recommended way. It handles partial matches natively with its fifth argument, match_mode. Set it to 2 for a wildcard match.
Example: You have product codes in A2:A20 and prices in B2:B20. You want the price for a code that contains “AB-34” (entered in D2).
Formula:
=XLOOKUP("*"&D2&"*", A2:A20, B2:B20, "Not found", 2)
This returns the first price where the product code contains the text in D2. If no match is found, it returns “Not found”. The 2 at the end tells XLOOKUP to treat the search value as a wildcard pattern.
Note: XLOOKUP is not available in Excel 2019 or earlier. On Mac, it works the same way.
Method 2: VLOOKUP with Wildcards (All Versions)
VLOOKUP also supports wildcards, but only when the last argument is FALSE (exact match). If you use TRUE, it does an approximate match and ignores wildcards.
Example: Same data as above, but you want the price for a code containing “AB-34” in D2.
Formula:
=VLOOKUP("*"&D2&"*", A2:B20, 2, FALSE)
This searches the first column of the range A2:B20 for the first cell that contains the text in D2, and returns the value from the second column. The FALSE is essential – without it, VLOOKUP will not treat the asterisk as a wildcard.
Caveat: VLOOKUP only looks to the right. If your return column is to the left of the lookup column, you need INDEX/MATCH or XLOOKUP.
Method 3: INDEX and MATCH with Wildcards
INDEX/MATCH is more flexible than VLOOKUP because it can return values from any column. The MATCH function also accepts wildcards when its third argument is 0 (exact match).
Example: You have names in A2:A20 and email addresses in C2:C20. You want the email for a person whose name contains “John” (entered in D2).
Formula:
=INDEX(C2:C20, MATCH("*"&D2&"*", A2:A20, 0))
MATCH finds the position of the first cell in A2:A20 that contains “John”, and INDEX returns the corresponding value from C2:C20. If no match, it returns #N/A.
Tip: To avoid #N/A, wrap it in IFERROR: =IFERROR(INDEX(C2:C20, MATCH("*"&D2&"*", A2:A20, 0)), "Not found").
Method 4: FILTER to Return All Matches (Excel 365)
Sometimes you need every row that matches, not just the first. The FILTER function can do that with wildcards, but you must use the SEARCH or ISNUMBER function because FILTER does not accept wildcards directly.
Example: You have a list of orders in A2:C20 (Product, Qty, Price). You want all rows where the product contains “wire”. Enter the search term in E2.
Formula:
=FILTER(A2:C20, ISNUMBER(SEARCH(E2, A2:A20)), "No matches")
SEARCH returns a number (the position) if the text is found, and an error if not. ISNUMBER converts that to TRUE/FALSE. FILTER then returns all rows where the product contains the search term. This spills multiple results automatically.
Note: SEARCH is case-insensitive. Use FIND instead if you need case-sensitive matching.
How to Choose the Right Method
Your choice depends on your Excel version and what you need:
- Excel 365 or 2021? Use XLOOKUP for a single match, or FILTER for multiple matches.
- Older Excel? Use VLOOKUP if your return column is to the right, otherwise INDEX/MATCH.
- Need the first match only? XLOOKUP, VLOOKUP, or INDEX/MATCH all work.
- Need all matches? FILTER is the only built-in way (or use an array formula in older versions, which is complicated).
Also consider performance: on very large datasets, XLOOKUP and INDEX/MATCH are faster than VLOOKUP because they don’t scan the entire table.
Common Problems and Fixes
- Wildcards not working? Make sure you’re using
FALSEor0for exact match. Approximate match ignores wildcards. - Case sensitivity? VLOOKUP, XLOOKUP, and MATCH are case-insensitive. If you need case-sensitive, use FIND with array formulas or a helper column.
- Finding literal asterisks? Use a tilde:
"*~**"finds cells containing an asterisk. - #N/A errors? Wrap your formula in IFERROR to return a friendly message.
- Partial match in multiple columns? Use SUMPRODUCT or a helper column that concatenates the columns, then search that.
Frequently Asked Questions
Can I use wildcards with COUNTIF or SUMIF?
Yes. COUNTIF and SUMIF support wildcards in the criteria. For example, =COUNTIF(A2:A20, "*cat*") counts cells containing “cat”. SUMIF works similarly: =SUMIF(A2:A20, "*cat*", B2:B20) sums values in B where A contains “cat”.
Why does my VLOOKUP return the wrong value when using wildcards?
Most likely because you used TRUE for the range_lookup argument. Always use FALSE. Also, if your data has duplicates, VLOOKUP returns the first match – that’s expected.
How do I make a partial match lookup case-sensitive?
Built-in lookup functions are case-insensitive. To do a case-sensitive partial match, use an array formula with FIND. For example, to return the first matching value from B when A contains D2 (case-sensitive), enter this as an array formula (Ctrl+Shift+Enter in older Excel): =INDEX(B2:B20, MATCH(TRUE, ISNUMBER(FIND(D2, A2:A20)), 0)). In Excel 365, it works without Ctrl+Shift+Enter.
Can I use partial match with XLOOKUP to return multiple matches?
No, XLOOKUP returns only one value. Use FILTER as shown above to get all matches. If you need to return multiple values from a single lookup, consider using TEXTJOIN with an array formula, but FILTER is simpler.
What if I want to search for text at the beginning or end only?
Use "text*" for starts-with, or "*text" for ends-with. For example, "*cat" matches “bobcat” but not “catalog”.
Is there a way to do a partial match without wildcards?
Yes, you can use the SEARCH function with an array formula, or use a helper column with ISNUMBER(SEARCH(…)). But wildcards are the simplest method and are built into the lookup functions.
Related
- Excel Eye Center Spanish Fork: Services, Doctors & More
- Excel Energy Group: What It Is and How It Works
- Free Excel Expense Tracking Sheet Template
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Rank Formula with Tie BreakerThe direct answer: use =RANK(B2,$B$2:$B$10)+COUNTIF($B$2:B2,B2)-1 to break ties by giving the first occurrence a higher rank. This adds…
- Excel Formula Has Text? How to Check & Use ItYes, an Excel formula can return text, and you can check whether a cell contains text using the…
- COUNT Formula in Excel for Text and Numbers ExplainedTo count numeric values in cells A2:A100, use =COUNT(A2:A100); COUNT does not count text. To count text, use…
- Excel Filter Shortcut: Turn AutoFilter On or OffIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn the filter buttons on or off. The…
- COUNTIF Between Dates in Excel: Formula and ExamplesTo count dates between two boundaries in Excel, use COUNTIFS with a lower-bound and an upper-bound condition. With…
- Calculating ROI in Excel: Formulas, Examples, and TipsTo calculate return on investment (ROI) in Excel, subtract the investment cost from the value it generated, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.