How to Use XLOOKUP with Wildcards for Partial Match Lookups in Excel

When you need to find a record in a massive Excel spreadsheet but only know part of the value — perhaps the first few characters of a product code, a fragment of a customer name, or a partial invoice reference — a standard exact-match XLOOKUP will return nothing. The function simply cannot find a perfect match because the lookup value does not exist in its complete form within your data. Wildcard characters solve this problem by allowing XLOOKUP to match cells based on partial text patterns rather than requiring an exact string.

What Are Wildcard Characters in Excel?

Excel supports three wildcard characters that you can use within lookup functions:

• Asterisk (*) — Matches any sequence of characters. For example, Lon* matches “London”, “Longitude”, and “Long Beach”.
• Question mark (?) — Matches exactly one character. For example, b?t matches “bat”, “bet”, and “bit” but not “boat”.
• Tilde (~) — Escapes a wildcard character so it is treated literally. For example, ~* searches for an actual asterisk character.

These wildcards work inside XLOOKUP only when you set the match_mode argument to 2 (wildcard match). Without this setting, XLOOKUP treats asterisks and question marks as literal characters and will not perform pattern matching.

The XLOOKUP Wildcard Syntax

The complete XLOOKUP syntax for wildcard matching is:

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

The critical difference from a standard XLOOKUP is the fifth argument. Setting match_mode to 2 activates wildcard interpretation. Here is what each argument does in this context:

• lookup_value — The partial text pattern including wildcard characters (e.g., "*laptop*" or "INV-2024-???").
• lookup_array — The column or range to search within.
• return_array — The column or range from which to return a result.
• [if_not_found] — Optional text to display if no match is found, such as "No match".
• 2 — Enables wildcard character matching.

Finding Records That Start With a Specific Prefix

Suppose you have a product catalogue in column A and prices in column B. You need to find the price of a product whose code starts with “PRD-78” but you do not remember the full code. Use this formula:

=XLOOKUP("PRD-78*", A2:A500, B2:B500, "Product not found", 2)

The asterisk after “PRD-78” tells Excel to match any product code that begins with those characters, regardless of what follows. This returns the first matching result from the lookup array.

Finding Records That Contain a Keyword Anywhere

To find a customer whose name contains the word “Technologies” anywhere in the string, wrap the keyword with asterisks on both sides:

=XLOOKUP("*Technologies*", A2:A1000, B2:B1000, "Not found", 2)

This matches values like “Apex Technologies Ltd”, “Technologies International”, and “Global Technologies Corp”. The double-asterisk pattern is particularly useful when you know a keyword but not its position within the cell value.

Using Question Marks for Fixed-Length Pattern Matching

The question mark wildcard is useful when you know the exact structure of a value but are uncertain about specific characters. For example, if your invoice numbers follow the format INV-2024-XXX where XXX represents a three-digit number, and you need to find any invoice from that series:

=XLOOKUP("INV-2024-???", A2:A2000, C2:C2000, "Invoice not found", 2)

Each question mark represents exactly one character, so this matches “INV-2024-001”, “INV-2024-999”, and “INV-2024-abc” but not “INV-2024-1” (too few characters) or “INV-2024-1234” (too many characters).

Combining Wildcards With Cell References for Dynamic Lookups

Hardcoding the search term directly into the formula limits flexibility. In most practical scenarios, you will want the lookup value to come from a cell. To build a dynamic wildcard lookup, concatenate the cell reference with wildcard characters:

=XLOOKUP("*" & D1 & "*", A2:A1000, B2:B1000, "No match", 2)

If cell D1 contains “Smith”, this formula searches for any value containing “Smith” anywhere within it. When you change D1 to “Johnson”, the formula instantly recalculates without any modification.

For prefix matching using a cell reference:

=XLOOKUP(D1 & "*", A2:A1000, B2:B1000, "No match", 2)

For suffix matching:

=XLOOKUP("*" & D1, A2:A1000, B2:B1000, "No match", 2)

Searching for Values That Contain a Literal Asterisk or Question Mark

If your data genuinely contains asterisks or question marks and you need to search for them literally, prefix the character with a tilde:

=XLOOKUP("~*Special~*", A2:A500, B2:B500, "Not found", 2)

This searches for the exact text “*Special*” rather than treating the asterisks as wildcards. The tilde tells Excel to interpret the next character as a literal match.

Common Mistakes to Avoid

• Forgetting to set match_mode to 2. Without the fifth argument set to 2, Excel treats wildcards as literal text and the formula will not find partial matches.
• Using wildcards with numeric values. Wildcard matching works only with text strings. If your lookup array contains numbers formatted as numbers, XLOOKUP will not apply wildcard logic. Convert the numbers to text first if needed.
• Expecting multiple results. XLOOKUP with wildcards returns the first match it finds. If multiple records match the pattern, only the first one in the lookup range is returned. To retrieve all matching records, consider using the FILTER function instead.
• Case sensitivity. Wildcard matching in XLOOKUP is case-insensitive. Searching for "*apple*" matches “Apple”, “APPLE”, and “apple” equally.

When to Use XLOOKUP Wildcards Instead of FILTER

XLOOKUP with wildcards is ideal when you need a single result from a partial match — such as looking up a price, a status, or a contact detail. If you need to return all rows that match a pattern, the FILTER function combined with the SEARCH or FIND function is a better approach because it can return multiple results as a dynamic array.

For single-result partial matching, XLOOKUP with match_mode set to 2 remains the fastest and most readable solution in modern Excel.

Practical Use Cases Worth Knowing

Wildcard lookups are particularly useful in everyday work scenarios:

• Finding a contact by surname when the full name includes a title or middle name you cannot remember.
• Looking up a product by partial SKU when the full code is too long to type accurately.
• Matching invoice or order numbers when you only know part of the reference.
• Searching log data for entries containing a specific error code or keyword.
• Cross-referencing abbreviated company names against a full-name directory.

By combining XLOOKUP with wildcard characters and the match_mode argument set to 2, you gain a powerful and flexible way to retrieve data from incomplete information — something that standard exact-match lookups simply cannot do.