Excel Formulas for Finding Partial Matches with Wildcards

Locating specific data within fragmented, inconsistent spreadsheets often leads to significant manual reconciliation delays. While standard funding sources and corporate budget databases rely on rigid data structures, real-world reporting demands adaptability. Leveraging wildcard-enabled formulas grants analysts the immediate capability to bypass strict matching rules and uncover hidden data connections. However, under the stipulation that improper wildcard syntax can yield inaccurate duplicates, establishing precise search parameters is critical. For example, using the asterisk (*) wildcard in a SEARCH or XLOOKUP formula easily isolates "Corp" from "Global Corporation." Below, we outline the exact formulas required to execute these partial matches efficiently.

Introduction to Partial Matching in Excel

Excel is an incredibly powerful tool for data analysis, but real-world data is rarely clean or perfectly uniform. Quite often, you need to find and retrieve data based on incomplete information. For instance, you might want to look up a transaction using only a partial serial number, find a customer's details using only their last name, or locate a product when you only know a portion of its title.

This is where partial matching comes into play. By using Excel's built-in wildcards within your lookup formulas, you can search for text strings that contain, start with, or end with your target search term. In this comprehensive guide, we will explore how to perform partial matches using various Excel functions, including XLOOKUP, VLOOKUP, INDEX & MATCH, and text-searching functions like SEARCH and FIND.

Understanding Excel Wildcards

Before diving into the formulas, it is essential to understand the three wildcard characters that Excel supports. These characters act as placeholders in your search criteria:

• Asterisk (*): Represents any number of characters (including zero characters). For example, searching for *east* will find "Northeast", "Southeast", "Eastern", or simply "east".
• Question Mark (?): Represents exactly one single character. For example, searching for Sm?th will find "Smith" or "Smyth", but it will not match "Smithe".
• Tilde (~): Acts as an escape character. If you need to search for an actual asterisk or question mark in your data, you must place a tilde before it (e.g., ~* or ~?).

Method 1: Partial Match with XLOOKUP (The Modern Way)

If you are using Microsoft 365 or Excel 2021 and newer, XLOOKUP is the most robust and flexible tool for lookups. By default, XLOOKUP performs an exact match search. However, it features a dedicated argument that explicitly enables wildcard matching.

The XLOOKUP Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode])

To use wildcards, you must set the fifth argument, match_mode, to 2. This tells Excel that your search term contains wildcard characters.

Example: Finding a Product Code

Imagine you have a list of product IDs in column A (e.g., "TX-1002-PRO", "CA-2005-ENT") and their corresponding prices in column B. You want to find the price of the product containing "2005" using a search term in cell D2.

Your formula would look like this:

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

How it works:

• The concatenation operator (&) builds the lookup value. If D2 contains "2005", Excel evaluates the lookup value as "*2005*".
• The wildcards tell Excel to find any text string in A2:A10 that has "2005" anywhere inside it.
• Setting the match_mode argument to 2 instructs XLOOKUP to interpret the asterisks as wildcards rather than literal text.

Method 2: Partial Match with VLOOKUP

For users on older versions of Excel, or those working in environments where backward compatibility is crucial, VLOOKUP remains a reliable option. VLOOKUP natively supports wildcard characters when performing an exact match (where the fourth argument is set to FALSE or 0).

The VLOOKUP Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Example: Looking Up Customer Information

Suppose you have a customer database. Column A contains the full name (e.g., "Johnathan Davis") and Column B contains their email address. You want to retrieve the email address by searching for "John" in cell D2.

The formula to use is:

=VLOOKUP("*" & D2 & "*", A2:B100, 2, FALSE)

Key Considerations for VLOOKUP:

• Concatenation is key: Just like with XLOOKUP, you must concatenate the asterisks to the cell reference: "*" & D2 & "*". If you write "*D2*", Excel will literally search for the text "D2" instead of the value inside cell D2.
• Exact Match parameter: You must set the last argument to FALSE. Even though we are doing a "partial" match, we are instructing Excel to find an exact pattern match to our wildcard string.
• Left-to-Right Limitation: Remember that VLOOKUP can only search the leftmost column of your specified range. If your search terms are in Column B and you want to return values from Column A, VLOOKUP cannot do this without restructuring your sheet.

Method 3: INDEX and MATCH for Advanced Partial Matches

If you need to perform a partial match lookup but your return column is to the left of your search column, or if you want a more dynamic formula than VLOOKUP, the combination of INDEX and MATCH is the classic industry standard.

The Formula Structure

=INDEX(return_range, MATCH("*" & search_term & "*", search_range, 0))

Example: Leftward Lookup

Suppose your item descriptions are in Column B and their corresponding part numbers are in Column A. You want to find the part number for an item description containing "Cordless".

=INDEX(A2:A50, MATCH("*" & "Cordless" & "*", B2:B50, 0))

How it works:

• The MATCH function searches B2:B50 for any string containing "Cordless". The third argument of MATCH is set to 0 (exact match), which is required for wildcard processing.
• Once MATCH identifies the row number (e.g., row 5), the INDEX function retrieves the value from that exact row within Column A.

Method 4: Checking If a Cell Contains Partial Text (SEARCH & FIND)

Sometimes you don't want to pull values from another column; instead, you simply want to identify if a specific cell contains a substring. You can achieve this by combining ISNUMBER with either the SEARCH or FIND function.

SEARCH (Case-Insensitive) vs. FIND (Case-Sensitive)

• SEARCH does not care about capital letters. Searching for "apple" will match "Apple", "APPLE", or "apple".
• FIND is strictly case-sensitive. Searching for "apple" will not match "Apple".

Using ISNUMBER and SEARCH

To verify if cell A2 contains the text "urgent", use the following formula:

=ISNUMBER(SEARCH("urgent", A2))

If "urgent" is found inside A2, the SEARCH function returns its starting character position (a number). ISNUMBER then converts this numerical position to TRUE. If the text is not found, SEARCH returns a #VALUE! error, which ISNUMBER evaluates as FALSE.

Integrating with IF Statements

You can wrap this within an IF statement to categorize your data dynamically:

=IF(ISNUMBER(SEARCH("urgent", A2)), "High Priority", "Normal")

Common Mistakes and Troubleshooting Tips

When working with wildcards and partial matches in Excel, you might run into unexpected errors. Here are the most common issues and how to resolve them:

1. Receiving a #N/A Error

If your formula returns a #N/A error, double-check the following:

• For XLOOKUP, ensure you have set the match_mode parameter to 2. If omitted, Excel defaults to an exact match.
• For VLOOKUP and MATCH, ensure your match type is set to FALSE or 0.
• Ensure your search term actually exists in the lookup range. Watch out for leading or trailing spaces in your source data.

2. Unexpected Match Results

Because wildcards match broad patterns, Excel will return the first matching occurrence it encounters in your range. If you search for "*Mini*", Excel will stop at "iPad Mini" even if you were looking for "Mac Mini" further down the list. If you have duplicate matches, consider cleaning your data or using a filter function to extract all occurrences.

3. Looking Up Numbers Partially

Wildcards only work with text data. If you try to use wildcards on formatted numbers (like searching for a partial ZIP code or phone number stored as numerical data), Excel lookup functions will fail to find a match. To resolve this, you must convert your numbers to text, or use the TEXT function within your lookup array.

Summary Table of Partial Match Formulas

Conclusion

Mastering partial matches with wildcards can save you hours of manual data cleaning and searching. Whether you adopt the modern capabilities of XLOOKUP, rely on the robust dependability of VLOOKUP or INDEX/MATCH, or run quick checks with SEARCH, understanding how to construct dynamic criteria with asterisks and question marks is an invaluable asset for any Excel analyst.