Excel Partial Match Lookup: 4 Formulas That Work

Formulas & functions · Updated November 22, 2026

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 FALSE or 0 for 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

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.