Locating exact data in Excel is straightforward, but managing messy, inconsistent, or partial text strings often stalls financial and operational reporting. While standard exact-match VLOOKUP or XLOOKUP functions handle structured databases perfectly, they fail when faced with slight text variations.

Integrating wildcard characters into your formulas grants you the ultimate flexibility to retrieve complete records using only a fragment of the search term. As an educational stipulation, note that wildcards like the asterisk (*) apply strictly to text-based data rather than numeric values. For instance, searching "Excel*" will seamlessly locate "Excel Formulas 101."

Below, we will detail the exact formulas and syntax required to master these partial match lookups in your daily workflows.

Excel Formula to Lookup Value With Partial Match Wildcard

In Microsoft Excel, performing an exact match lookup using functions like VLOOKUP, XLOOKUP, or INDEX/MATCH is a standard procedure. However, real-world data is rarely perfect. You will often encounter situations where you need to find a value based on an incomplete, misspelled, or partial text string. For instance, you might want to look up "Microsoft" within a list containing "Microsoft Corporation", or search for a product using only part of its serial number.

This is where wildcard characters come into play. By pairing wildcards with Excel's powerful lookup functions, you can retrieve data when you only have partial search criteria. In this comprehensive guide, we will explore how to build formulas for partial matches using VLOOKUP, XLOOKUP, and INDEX/MATCH, and look at advanced solutions for handling complex scenarios.

Understanding Wildcard Characters in Excel

Before writing the formulas, it is crucial to understand the three wildcard characters that Excel supports:

• Asterisk (*): Represents any sequence of characters (zero or more). For example, "XL*" matches "Excel", "XL", "XLSX", and "Xylophone".
• Question Mark (?): Represents any single character. For example, "Te?t" matches "Test", "Tent", and "Text", but not "Teest".
• Tilde (~): Converts a wildcard character into a literal character. If you want to search for an actual asterisk or question mark, you prefix it with a tilde (e.g., "~*" matches an actual asterisk symbol).

Method 1: Partial Match Using VLOOKUP

The VLOOKUP function has been the go-to tool for lookup tasks for decades. To perform a partial match with VLOOKUP, you must concatenate the wildcard characters directly with your lookup value and ensure that the function is set to search for an exact match (using FALSE or 0 as the fourth argument).

Scenario and Formula Syntax

Suppose you have a table where Column A contains full employee names (e.g., "Johnathan Smith") and Column B contains their respective departments. You only remember the first name "John" and want to retrieve his department.

The syntax for a partial match VLOOKUP is as follows:

=VLOOKUP("*" & lookup_value & "*", table_array, col_index_num, FALSE)

Let's break down how this works:

• "*" & lookup_value & "*": This concatenates an asterisk before and after the lookup value. If your lookup value in cell D2 is "John", Excel evaluates this argument as "*John*". This instructs Excel to find any string in the lookup column that contains "John" anywhere within it (at the beginning, middle, or end).
• table_array: The range containing your data.
• col_index_num: The column number in the range from which to retrieve the value.
• FALSE: Forces VLOOKUP to look for a match based on the pattern defined by the wildcards. (Do not set this to TRUE, as approximate matching behaves unpredictably with unsorted data).

VLOOKUP Examples

Method 2: The Modern Approach – XLOOKUP with Wildcards

If you are using Microsoft 365, Excel 2021, or Excel for the Web, XLOOKUP is the superior choice. It eliminates many limitations of VLOOKUP (such as only searching to the right) and features built-in wildcard processing options.

The Key Match Mode Parameter

Unlike VLOOKUP, where you must concatenate wildcards and rely on standard exact matching, XLOOKUP has a dedicated match_mode parameter. To use wildcard character matching, you must set the 5th argument (match_mode) to 2.

The standard syntax is:

=XLOOKUP("*" & lookup_value & "*", lookup_array, return_array, [if_not_found], 2)

Step-by-Step Example

Imagine you have a product inventory sheet:

• A2:A100: Product Codes (e.g., "PROD-1092-A", "PROD-2034-B")
• B2:B100: Stock Quantity

If a customer asks for stock levels of a product and only provides the partial code "1092", you can write the following formula in cell E2:

=XLOOKUP("*1092*", A2:A100, B2:B100, "Not Found", 2)

Or, if the search term "1092" is entered in cell D2:

=XLOOKUP("*" & D2 & "*", A2:A100, B2:B100, "Not Found", 2)

Why is this better? If no product containing "1092" exists, XLOOKUP gracefully returns "Not Found" instead of a messy #N/A error, thanks to its native 4th argument.

Method 3: INDEX and MATCH for Flexible Partial Match Lookups

For users running older versions of Excel (like Excel 2010, 2013, or 2016) who need to lookup values to the left of their search column, the classic INDEX and MATCH combo is the perfect solution.

How to Integrate Wildcards with MATCH

The MATCH function accepts wildcard characters when its third argument (match_type) is set to 0 (exact match).

The syntax for a partial match lookup is:

=INDEX(return_range, MATCH("*" & lookup_value & "*", lookup_range, 0))

Example Construction

Suppose you have client names in Column B (e.g., "Acme Corporate Services") and their Account Managers in Column A. You want to find the manager for "Acme".

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

Here, MATCH searches through B2:B50 for any string containing "Acme". It finds "Acme Corporate Services" at position 4, and then INDEX returns the value from the 4th row of the managers list in Column A.

Method 4: Case-Sensitive Partial Match Lookups

By default, VLOOKUP, XLOOKUP, and MATCH are case-insensitive. Searching for "*apple*" will match "Apple", "APPLE", or "apPLe". If you need to restrict your lookup to match exact case criteria dynamically, you need a different formulation because wildcards do not preserve case sensitivity.

To perform a case-sensitive partial match lookup, you must pair the FIND function (which is case-sensitive) with ISNUMBER, MATCH, and INDEX.

The Formula:

=INDEX(return_range, MATCH(TRUE, ISNUMBER(FIND(lookup_value, lookup_range)), 0))

Note: Depending on your Excel version, you may need to press Ctrl + Shift + Enter to enter this as an array formula.

How It Works:

1. FIND(lookup_value, lookup_range) checks every cell in the lookup range for the case-sensitive lookup_value. If it finds it, it returns its starting position (a number); otherwise, it returns a #VALUE! error.
2. ISNUMBER(...) converts these results into an array of TRUE and FALSE values.
3. MATCH(TRUE, ..., 0) searches for the first TRUE value in the array, indicating a successful case-sensitive partial match.
4. INDEX retrieves the corresponding value from the designated return range.

Important Tips and Best Practices

• Watch Out for Multiple Matches: All lookup functions discussed here (VLOOKUP, XLOOKUP, INDEX/MATCH) return the first matching value they encounter. If your dataset contains both "North Supply Inc." and "Northwest Depot", searching for "*North*" will stop at whichever appears first.
• Escaping Literal Wildcards: If you are looking up values containing physical wildcards (e.g., trying to find the price of "Product *A"), ensure you use a tilde in your search parameters to prevent Excel from interpreting the symbol as a wildcard.
• Concatenation Syntax: Always use double quotes around literal asterisks and use the ampersand operator to concatenate variables: "*" & CellReference & "*". Writing "*CellReference*" inside your lookup formula will cause Excel to literally search for the word "CellReference".

Conclusion

Mastering partial match lookups in Excel dramatically improves your efficiency when working with messy or unstructured datasets. Whether you choose the traditional VLOOKUP, the flexible INDEX/MATCH combo, or the modern, robust XLOOKUP function, incorporating wildcard characters like * and ? provides the flexibility needed to pinpoint and extract the exact information you require.