How to XLOOKUP Partial Match in Excel: Easy Guide

Sharing is caring!

When working with Excel, you often deal with data that doesn’t match perfectly such as customer names, product codes, or text entries that only partially match. The XLOOKUP function can handle partial matches easily when combined with wildcards.

In this guide, we will show you exactly how to use XLOOKUP for partial matches with step-by-step explanations, practical examples, and formula variations.

When You Need a Partial Match

Partial matches are useful when your lookup value doesn’t exactly match the lookup array entries.

Example Scenarios:

• Searching for “Pro” to find “Product A”
• Finding a customer by a part of their name (like “Smith” for “John Smith”)
• Matching partial item codes (e.g., “A10” for “A1001”)

Without using wildcards, XLOOKUP looks for exact matches only. Let’s see how to enable partial matching.

Using XLOOKUP with Wildcards for Partial Match

Excel supports two wildcard characters in XLOOKUP:

Example 1: Partial Match Using Asterisk (*)

Let’s say you have the following dataset:

If you want to look up a product code that starts with “A10”, you can use:

=XLOOKUP("A10*", A2:A4, B2:B4, "Not Found", 2)

Explanation:

• "A10*" tells Excel to find any value beginning with “A10”.
• A2:A4 is the lookup array.
• B2:B4 is the return array.
• "Not Found" displays if no match is found.
• The 2 in [match_mode] means wildcard match.

This will return $25 because “A1001” is the first partial match for “A10”.

Example 2: Partial Match Containing Text

If you want to find a match where the lookup text is anywhere within the cell, you can use the wildcard * before and after your lookup value.

Formula:

=XLOOKUP("*"&"100"&"*", A2:A4, B2:B4, "Not Found", 2)

Explanation:

• The formula looks for “100” anywhere inside the text of cells in A2:A4.
• The & symbol concatenates wildcards before and after the search text.
• Returns the price for the first item that includes “100” in the product code.

This will return $25 because “A1001” includes “100”.

Example 3: Partial Match Using Cell Reference

If your lookup value is stored in a cell (say C2 contains A10), you can use:

=XLOOKUP(C2 & "*", A2:A4, B2:B4, "Not Found", 2)

This formula makes it flexible to change the lookup value without editing the formula each time.

Understanding Match Mode Options

The [match_mode] argument in XLOOKUP determines how Excel searches for matches:

To perform partial matches, you must set match_mode to 2.

How XLOOKUP Handles Wildcards Internally

When match_mode is set to 2, Excel treats the lookup_value as a text pattern. It interprets * and ? as wildcard symbols. If your lookup value actually contains these symbols and you want them treated literally (not as wildcards), you’ll need to escape them using the tilde (~) symbol.

Example:

=XLOOKUP("A~*10", A2:A4, B2:B4, "Not Found", 2)

This will look for “A*10” exactly, not as a wildcard pattern.

Using XLOOKUP for Partial Text in Names

Imagine you have a list of customer names and you want to find someone by a partial name.

To find the city for a name containing “Smith”, use:

=XLOOKUP("*Smith*", A2:A4, B2:B4, "Not Found", 2)

This returns Dallas since the function matches “John Smith”.

Combining XLOOKUP with Functions for Flexible Partial Matches

Sometimes you need case-insensitive, contains-based, or dynamic matches beyond simple wildcards. You can combine XLOOKUP with other Excel functions like SEARCH, FILTER, or IFERROR for more flexibility.

Example 1: Using SEARCH with XLOOKUP

You can simulate a “contains” match using SEARCH and FILTER, then wrap it inside XLOOKUP.

=XLOOKUP(TRUE, ISNUMBER(SEARCH(C2, A2:A10)), B2:B10, "Not Found")

How it works:

• SEARCH(C2, A2:A10) returns a number if C2 is found within each cell.
• ISNUMBER converts that to TRUE/FALSE.
• XLOOKUP finds the first TRUE and returns the matching value from B2:B10.

This method doesn’t require wildcards and is case-insensitive.

Example 2: Using IFERROR to Handle Missing Matches

To avoid Excel errors like #N/A, use IFERROR:

=IFERROR(XLOOKUP("*"&C2&"*", A2:A10, B2:B10, , 2), "No Match Found")

This keeps your sheet clean by returning a custom message instead of an error.

XLOOKUP vs. VLOOKUP for Partial Matches

While VLOOKUP can also perform partial matches using wildcards, XLOOKUP is more advanced and efficient.

Verdict: XLOOKUP is faster, cleaner, and easier for partial matching tasks.

Real-Life Example: Product Lookup with Partial Codes

Let’s look at a practical scenario.

You have a partial code in cell E2 (e.g., P-3) and want to find the Product Name.

Formula:

=XLOOKUP(E2 & "*", A2:A5, B2:B5, "Not Found", 2)

Result: Monitor

If you wanted the Price instead, replace B2:B5 with C2:C5:

=XLOOKUP(E2 & "*", A2:A5, C2:C5, "Not Found", 2)

Result: $150

How to Search from Last to First Match

If your data has multiple partial matches and you want to return the last match, you can use the [search_mode] argument.

Example:

=XLOOKUP("*"&C2&"*", A2:A10, B2:B10, "Not Found", 2, -1)

This finds the last item containing the text from C2.

Common Errors When Using XLOOKUP Partial Match

Here are a few mistakes to avoid:

Pro Tips for Using XLOOKUP Partial Match Effectively

1. Always define match_mode as 2 when using wildcards.
2. Combine * before and after text to find values containing a substring.
3. Use cell references for dynamic lookups.
4. Wrap with IFERROR to handle missing data gracefully.
5. For multiple partial matches, use FILTER instead of XLOOKUP.
6. When you expect duplicates, add [search_mode] = -1 to find the last match.

Final Thoughts

The XLOOKUP partial match feature makes searching flexible and efficient in Excel. By combining wildcards (* and ?) and setting the match_mode to 2, you can easily find text fragments, partial product codes, or incomplete data.

Once you’re comfortable with wildcards, you can even pair XLOOKUP with functions like SEARCH or FILTER for more advanced lookups; making it one of the most powerful tools in modern Excel.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.