How to Use XLOOKUP with Wildcards for Partial Matches

To use XLOOKUP for a partial match, set match_mode to 2 and add wildcards to the lookup value. If A2 contains a base SKU such as ABC123, this formula finds the first code containing that text and returns its price:

=XLOOKUP("*"&A2&"*",$E$2:$E$7,$F$2:$F$7,"Not found",2)

The asterisks allow extra characters before and after ABC123. That helps when your price list contains longer codes such as ABC123-RED. It doesn't correct spelling mistakes or choose the most similar code: wildcard matching is not fuzzy matching.

The examples below use Excel for Microsoft 365, Excel 2024, or Excel 2021. Microsoft notes that XLOOKUP is unavailable in Excel 2016 and Excel 2019. If you need the basic arguments first, start with our XLOOKUP guide for beginners.

Set Up a Partial-Match SKU Lookup

Enter ABC123 in A2, then put this price list in E1:F7. The row order matters because XLOOKUP normally searches from top to bottom.

Download the XLOOKUP partial-match practice workbook to try these formulas. The SKU Matches sheet uses the same cells as this example; Wildcard Characters shows question marks and literal wildcard codes.

Enter the opening formula in B5. The result is 19: Excel finds ABC123 inside E2 and returns the price from F2.

Notice the direction of the lookup. You're searching for a short base key inside a column of longer SKUs. If your input is the full code ABC123-RED and your lookup column contains only ABC123, adding asterisks around the full input won't remove -RED. You would need to extract the base key first, using the rules of your SKU format.

The same contains pattern works with descriptions. For example, chair can match Office Chair - Black or Desk Chair Small. In either case, check whether returning the first matching row is what you want.

Choose Contains, Starts With, or Ends With

Where you put * determines which codes can match. Keep ABC123 in A2 and use these formulas with the same price list.

Starts-with match

Enter this in B6:

=XLOOKUP(A2&"*",$E$2:$E$7,$F$2:$F$7,"Not found",2)

A2&"*" becomes ABC123*. Excel skips PRE-ABC123-BOX because that code starts with PRE, then finds ABC123-RED in E3. The result is 22.

Use this pattern when the base SKU belongs at the beginning of the code. If the hyphen is a required separator, A2&"-*" is more precise: it matches ABC123-RED without also matching a code such as ABC1234-RED.

Ends-with match

Enter this in B7:

=XLOOKUP("*"&A2,$E$2:$E$7,$F$2:$F$7,"Not found",2)

The pattern *ABC123 allows characters before the key but none after it. That finds KIT-ABC123 in E5 and returns 18.

The final 2 is the wildcard match mode in all three formulas. Without it, XLOOKUP's default exact-match behavior won't interpret the asterisks as these patterns. See the XLOOKUP function reference for the full argument order.

Return the First or Last Partial Match

XLOOKUP selects one matching row. In the contains example, five SKUs qualify, but the default search returns the price from the first one in worksheet order.

To search from the bottom instead, enter this in B8:

=XLOOKUP("*"&A2&"*",$E$2:$E$7,$F$2:$F$7,"Not found",2,-1)

The added -1 is the sixth argument, search mode. It makes Excel search from last to first, so the result is 26, from ABC123-GREEN in row 7. Keep 2 in the fifth argument to retain wildcard matching.

“Last” means the last matching row in the range. It doesn't mean the latest price by date unless the rows are arranged that way. Sorting the source list can change both the first and last results.

If you need to choose a particular color, size, or region, use a more specific key or a multiple-criteria XLOOKUP. If you need every matching row, use the next formula.

Return All Partial Matches with FILTER and SEARCH

Use FILTER to return both the SKU and price for every contains match. Enter this in H2, with room for the results in columns H:I:

=FILTER($E$2:$F$7,ISNUMBER(SEARCH(A2,$E$2:$E$7)),"No matches")

With ABC123 in A2, the result spills into H2:I6:

SEARCH looks for ABC123 inside each SKU. It returns a character position when it finds the text, or an error when it doesn't. ISNUMBER turns those results into TRUE or FALSE, and FILTER keeps the rows marked TRUE.

Change A2 to ZZZ999 and H2 shows No matches. The four XLOOKUP formulas show Not found. Enter a nonblank search key: an empty key can match broadly and return results you didn't intend.

SEARCH ignores letter case and accepts wildcards. For ordinary SKU text such as ABC123, you don't need surrounding asterisks because SEARCH already looks inside the text. If the key contains a literal * or ?, escape it as explained below.

Keep the FILTER formula outside an Excel Table and leave its output area clear. Existing content in the spill area can cause #SPILL!. Microsoft's FILTER documentation explains spilled results and the optional no-match argument.

Use Question Marks and Escape Literal Wildcards

The wildcard characters have different jobs:

For example, on a separate worksheet where E2:E6 contains codes and F2:F6 contains prices:

=XLOOKUP("AB?12",$E$2:$E$6,$F$2:$F$6,"Not found",2)

This finds the first five-character code that starts with AB and ends with 12. The question mark fills exactly one position; it won't match AB12 or ABCD12.

If the asterisk is part of the actual product code, put a tilde before it:

=XLOOKUP("AB~*12",$E$2:$E$6,$F$2:$F$6,"Not found",2)

That searches for AB*12 itself. Use ~? for a literal question mark and ~~ for a literal tilde. These escapes also apply to the search text used by SEARCH. Microsoft's wildcard character guide lists all three escape forms.

Why a Partial Match Can Still Give the Wrong Result

Hidden spaces still matter

A leading space can prevent a starts-with match, and a trailing space can prevent an ends-with match. A contains pattern may hide those differences by allowing extra characters around the key.

Use TRIM to remove ordinary extra spaces where appropriate, and check both the search key and source codes. TRIM doesn't remove every kind of invisible character, including nonbreaking spaces. Our guide to lookups that fail with text values covers related cleanup problems.

The pattern is too broad

Searching for pen may match Pencil, Pendulum, or Open Box Pen Set. Similarly, ABC123* also allows extra digits immediately after the key. Check the returned SKU alongside its price before trusting a broad pattern, especially when several rows match.

The text contains a typo

Wildcards allow extra or unspecified characters only where you put them. Searching for *ABC123* won't find ACB123-RED, because the letters are in a different order. Correct the key or source data before trying the lookup again.

For #N/A, range-size problems, or spill errors, continue with common XLOOKUP errors and their fixes.