How to Use Wildcard Searches in Microsoft Excel Using XLOOKUP

In business data analysis, inventory tracking, and client billing management, lookup values rarely match master database records with character-for-character perfection. Users frequently search using truncated serial numbers, incomplete product titles, or variable SKU prefixes. While older functions like VLOOKUP supported basic wildcard matching by default, modern spreadsheet engineering relies on XLOOKUP for superior performance, two-way lookups, and built-in error handling. In Microsoft Excel, activating wildcard matching in XLOOKUP requires explicitly enabling its match mode parameter, giving analysts surgical control over partial string searches.

Understanding How Wildcards Work in XLOOKUP

Unlike VLOOKUP—which always treats wildcards as active in exact match mode—XLOOKUP defaults to strict exact matching (match_mode = 0), ignoring wildcards as literal characters unless instructed otherwise. To perform partial string searches, you must set the 5th parameter (match_mode) to 2.

When wildcard match mode is enabled, XLOOKUP recognizes three special wildcard operators:

  • Asterisk (*): Represents any sequence of zero or more characters. For example, "*" & "North" & "*" matches “North Region”, “Great North Road”, or “Sub-North”.
  • Question Mark (?): Represents any single character. For example, "Part-?0" matches “Part-A0”, “Part-B0”, but not “Part-AB0”.
  • Tilde (~): The escape character. Used to search for literal asterisks, question marks, or tildes (e.g. "~*" searches for an actual star symbol).

Much like building multi-dimensional models with two-way matrix lookups or standardizing catalogs via Power Query fuzzy matching, mastering XLOOKUP wildcards provides robust data retrieval.

Syntax and Argument Structure for Wildcard Matching

The standard syntax for a wildcard lookup in Excel is structured as follows:

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

To enable wildcards, provide the first five arguments:

  • lookup_value: The search string combined with wildcard symbols (e.g. "*" & D2 & "*").
  • lookup_array: The column or range containing the master text data.
  • return_array: The column or range containing the values you wish to retrieve.
  • if_not_found: A custom fallback message (e.g. "Not Found") to prevent ugly #N/A errors.
  • match_mode: Must be set to 2 (Wildcard character match).

Step-by-Step Example: Searching Incomplete Product Codes

Suppose you maintain an inventory table where Column A contains full SKUs (e.g. SKU-US-48210-BLK) and Column B contains unit prices (e.g. £45.00). You want to retrieve the price by entering only the numeric core 48210 into search cell D2.

In cell E2, enter the formula:

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

Here is what happens during formula calculation:

  1. The concatenation operator (&) wraps the text in D2 with asterisks, evaluating internally as "*48210*".
  2. Because match_mode is set to 2, XLOOKUP scans A2:A100 looking for any SKU string that contains 48210 anywhere inside it.
  3. Upon finding the first match, it immediately returns the corresponding value from B2:B100.
  4. If no matching SKU exists, it gracefully outputs "SKU Not Found" instead of throwing an error.

Controlling Search Direction with search_mode

By default, XLOOKUP searches from the first item to the last (top-to-bottom). If your dataset contains multiple partial matches—such as sequential transaction logs—and you want to find the most recent entry, pass -1 as the 6th argument (search_mode):

=XLOOKUP("*" & D2 & "*", A2:A100, B2:B100, "None", 2, -1)

This instructs Excel to search from bottom-to-top, returning the latest record matching your wildcard criteria.

Handling Literal Asterisks and Question Marks

If your database contains literal asterisks (such as footnote tags Item*) and you need to search for that exact character without triggering wildcard behavior, use the tilde prefix:

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

The tilde neutralizes the wildcard function, ensuring Excel searches strictly for the literal * glyph.

Combining wildcard flexibility with standard XLOOKUP fundamentals empowers data teams to build resilient financial models, automated schedules, and interactive executive reporting decks in Microsoft Excel.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.