How to Xlookup Wildcard Match Example in Excel

Why This Task Matters in Excel

Matching text that only partially matches a lookup key is an everyday need in data work. Inventory systems track thousands of stock-keeping units whose codes share common fragments, customer relationship databases store names in numerous formats, and web-analytics exports often split URLs into long strings where the section you actually care about is buried in the middle. In all these cases you do not have the luxury of an exact lookup key; you simply know that the code contains “-XL-”, or that the customer starts with “Acme”, or that the landing page ends with “/pricing”.

Wildcard lookups allow analysts to bridge this gap between messy real-world data and the tidy, structured data models that downstream reports depend on. Without them, you find yourself resorting to manual filtering, copy-pasting intermediate columns, or worst of all, rewriting the underlying source systems. Wildcard lookups let you automate the match once and reuse it in every refresh.

Historically, wildcard lookups required INDEX + MATCH with wildcards or even older functions like VLOOKUP with approximate match flags. Both work, but they bring baggage: they need helper columns, require the lookup column to remain on the left, and cannot gracefully handle errors. Enter XLOOKUP, Excel’s modern lookup workhorse introduced in Microsoft 365 and Excel 2021. XLOOKUP overcomes the column-ordering limitations, supports explicit wildcard modes, and delivers superior error handling through its if_not_found argument.

The ability to perform wildcard matching directly inside XLOOKUP means you can build cleaner workbooks, reduce helper columns, and gain performance advantages when querying large tables of tens of thousands of rows. For business analysts, that translates to quicker answers, fewer formula headaches, and dashboards that update reliably. For managers, it means decisions made on up-to-date numbers. And for organizations, it ensures data integrity without expensive IT re-engineering.

Finally, mastering wildcard XLOOKUP is a stepping-stone to other advanced pattern-matching tasks, such as dynamic report labels, partial URL tagging, or fuzzy product bundling. Neglecting the skill leaves you vulnerable to brittle solutions and maintainability nightmares.

Best Excel Approach

The most straightforward and future-proof way to achieve a wildcard lookup today is with XLOOKUP’s optional match_mode argument. Setting match_mode to 2 activates wildcard interpretation for the lookup_value. When Excel sees the wildcard characters “*” (any number of characters) or “?” (exactly one character) in the lookup value, it searches the lookup_array for the first cell that satisfies the pattern. You still receive all the custom error-handling, reverse-order search, and flexible return-array benefits that have made XLOOKUP popular.

Syntax recap:

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

To enable wildcard matching:

=XLOOKUP(lookup_value, lookup_array, return_array, "Not found", 2)

Why is this method best?

• No need for helper columns—the pattern logic lives in the lookup value.
• Bidirectional lookup—the return array can be anywhere, left or right of the lookup array.
• Built-in error handling via the if_not_found argument, eliminating cumbersome IFERROR wrappers.
• Superior readability—one function conveys the entire intent.

When would you pick another method? If you support colleagues on Excel 2016 or earlier, you will need an INDEX + MATCH workaround. Otherwise, XLOOKUP with match_mode 2 is the modern default.

Parameters and Inputs

Before building the formula, verify the following inputs are ready:

• lookup_value (text): the pattern that includes “*” or “?” wildcards. It can be a hard-coded string, a cell reference, or even the result of another formula.
• lookup_array (single-dimension range): the column or row you want to search. Must be consistent in data type—mixed numbers and text can cause mismatches.
• return_array (single-dimension range): the parallel slice from which you want the result. Length must match lookup_array.
• if_not_found (optional text or numeric): what you want displayed when no match occurs; defaults to #N/A.
• match_mode (numeric flag): 2 for wildcard matching; 0 for exact, -1 for exact or next smaller, 1 for exact or next larger.
• search_mode (optional): 1 to search top-to-bottom (default) or -1 for bottom-to-top, useful when the latest entry should win.

Data prep tips:

• Strip leading/trailing spaces with TRIM.
• Convert numbers stored as text to real numbers when wildcards are not needed.
• For case-sensitive scenarios, consider adding COLLATE columns or helper checks because XLOOKUP wildcard matching is case-insensitive by design.
• Validate the ranges are the same size; mismatched ranges trigger a #VALUE! error.
• If your lookup pattern could accidentally match multiple rows, decide whether “first from top” or “first from bottom” should win and set search_mode accordingly.

Step-by-Step Examples

Example 1: Basic Scenario

Goal: Return a product’s full description when you only know part of its code.

Sample data setup
Inventory table in [B3:E9]

Cell G4 contains the partial code “-XL-”. We want the matching description in H4.

Step-by-step

1. Click cell H4.
2. Enter the formula:

=XLOOKUP(G4, B4:B9, C4:C9, "No match", 2)

3. Press Enter. The result is “Running Shoe XL” because it is the first record that satisfies the pattern (“anything-XL-anything”).

Why it works
lookup_value “-XL-” tells Excel: “Find the first cell in [B4:B9] that contains a hyphen, then XL, then another hyphen, with any characters before or after.” XLOOKUP scans top-to-bottom, matches row 1, and returns the parallel value from [C4:C9]. Because we passed 2 in match_mode, the engine interprets * as a wildcard rather than a literal asterisk.

Variations

• You can flip the search order to choose the latest XL item by adding -1 in search_mode.
• Add TRIM around the lookup array if user data sometimes contains leading spaces.

Troubleshooting
If you get #N/A, confirm match_mode is 2 and that the pattern really exists; remember that XLOOKUP wildcards are case-insensitive, so “xl” equals “XL”.

Example 2: Real-World Application

Goal: In a multilingual CRM, return the customer’s latest support ticket ID when you only know a fragment of their email domain.

Context
A customer support team stores tickets in a sheet with 20,000+ rows. Each row has ticket ID, date opened, and requester email. Agents often receive verbal hints like “the company is something like global-research.net,” but the exact sub-domain varies by business unit: “fr.global-research.net”, “sales.global-research.net”, or “hk-global-research.net”. The agent needs the most recent ticket for that umbrella domain.

Data snapshot

• Ticket IDs in column A
• Opened date in column B
• Requester email in column C
• Sheet name: Tickets

Solution steps

1. Create a pattern cell [F2] where the agent types “*global-research.net”. The leading asterisk accounts for any sub-domain prefix.
2. Because we want the latest ticket, we must search from the bottom upward. Use:

=XLOOKUP(F2, Tickets!C:C, Tickets!A:A, "No ticket", 2, -1)

3. Press Enter; Excel whips through the 20,000-row column from bottom to top, finds the last (therefore newest) email that ends with global-research.net, and returns its ticket ID.

Why this solves the business problem

• The lookup is instantaneous; no array filters or sorts required.
• Agents do not need to remember exact sub-domains, speeding call resolution.
• Fallback text “No ticket” removes ambiguous #N/A errors from user view.
• Because XLOOKUP references entire columns, new tickets appended to the bottom remain in scope.

Performance note
Searching an entire column downward would stop at the first match, often an old ticket. By changing search_mode to -1, you let Excel short-circuit as soon as it finds the last row, which is efficient even on large datasets.

Integration tip
Combine with dynamic array FILTER if agents need a list of all matching tickets, then use INDEX to fetch the latest. But for single-value retrieval, XLOOKUP is the leanest approach.

Example 3: Advanced Technique

Goal: Return a region’s current sales target when the lookup pattern is built dynamically from user inputs and multiple wildcards are required.

Scenario
A sales manager chooses a product tier from dropdown A2 and a region code from dropdown B2. The target table stores tier abbreviations in column D and composite region patterns in column E, such as “NA*” for North America, “EU-??” where “??” is a two-letter country code. We need to match both tiers and region patterns to pull the current quarterly target.

Steps

1. Build a dynamic lookup pattern in C2:

=CONCAT("*",B2,"*",A2,"*")

If B2 is “EU” and A2 is “Gold”, the pattern becomes “EUGold*”.

2. In D2, retrieve the target:

=XLOOKUP(C2, TargetTable!E:E, TargetTable!F:F, "No target", 2)

Why advanced

• The lookup pattern is itself constructed via CONCAT using user selections.
• The wildcard search must reconcile two independent inputs (tier and region) without helper columns.
• “*” wildcards before, between, and after allow variable text lengths in the stored pattern.
• This eliminates many nested IFs or FILTERs across multiple columns.

Optimization
Wrap the return array in VALUE if you need the number for calculations rather than text. Use LET to store intermediate results for readability:

=LET(
    pattern, CONCAT("*",B2,"*",A2,"*"),
    XLOOKUP(pattern, TargetTable!E:E, TargetTable!F:F, "No target", 2)
)

Error handling
If the dynamic pattern becomes “*Gold” because region was blank, XLOOKUP still works; two consecutive asterisks act as one wildcard. However, confirm at least one literal character exists so you don’t unintentionally match the first row of the table.

Tips and Best Practices

1. Always specify match_mode explicitly. Relying on the default (exact match) and sprinkling wildcards in the lookup value without changing the flag causes #N/A headaches.
2. Use search_mode -1 for “latest” records; use 1 for “earliest” or default. Document this in cell comments for colleagues.
3. Combine TRIM and CLEAN on imported data columns to remove non-printing characters that break matches invisibly.
4. Store wildcard patterns in separate input cells rather than embedding them directly in formulas. This improves maintainability and enables spill-over for multiple lookups.
5. Use LET to define the pattern once if your formula references it multiple times. Reduces calculation overhead and clarifies intent.
6. Format your result cells with meaningful messages like “Pattern not found” instead of leaving raw error codes exposed to end users.

Common Mistakes to Avoid

1. Forgetting to set match_mode to 2. The presence of “*” in the lookup value does not automatically trigger wildcard mode. Fix by revisiting the fifth argument.
2. Mismatched range sizes between lookup_array and return_array. The formula returns #VALUE! instantly. Resize ranges to equal length or use structured table references.
3. Confusing case sensitivity. XLOOKUP wildcard compares in a case-insensitive manner. Users expecting case-sensitive matches must combine with EXACT or FILTER.
4. Searching top-to-bottom when the dataset is time-stamped but unsorted. You may accidentally return an outdated match. Switch search_mode to -1 or sort your data descending.
5. Using unnecessary IFERROR wrapping. XLOOKUP already has if_not_found. Double error handlers slow performance and obscure logic.

Alternative Methods

Sometimes XLOOKUP isn’t available or another strategy is preferable. Below is a quick comparison.

Choose INDEX + MATCH if colleagues lack XLOOKUP. Use Power Query when you require fuzzy logic levels beyond simple “*” patterns. FILTER works well for spill ranges when you need multiple matches, but then you must aggregate or pick a single row with INDEX.

FAQ

When should I use this approach?

Use XLOOKUP wildcard matching when you have a single value to return and the lookup key is only partially known—for example, SKU codes with common fragments, domain names, or variable prefixes/suffixes. It shines when your spreadsheet runs on Microsoft 365 or Excel 2021.

Can this work across multiple sheets?

Yes. Reference fully qualified ranges such as \'Product List\'!A:A for lookup_array and \'Product List\'!B:B for return_array. Structured table references across sheets also work.

What are the limitations?

Wildcard matching is case-insensitive, so “ABC” equals “abc”. XLOOKUP always returns the first (or last) match only. For simultaneous multiple matches, use FILTER instead. Also, wildcard mode recognizes “*” and “?” only; it does not support regular expressions.

How do I handle errors?

Leverage the if_not_found argument to return a custom message. For more complex logic—such as logging unmatched patterns—wrap the XLOOKUP inside IF(ISNA()) or use LET to output diagnostics.

Does this work in older Excel versions?

No. XLOOKUP is unavailable in Excel 2016 and earlier. Use INDEX + MATCH:

=INDEX(return_range, MATCH(lookup_pattern, lookup_range, 0))

Ensure you enter the wildcard pattern and keep MATCH’s third argument at 0 for exact match plus wildcard interpretation.

What about performance with large datasets?

XLOOKUP is built on modern engine improvements. Searching two entire columns of 100,000 rows returns nearly instant results on current hardware. Still, limit ranges to only the necessary rows when possible, avoid volatile wrapper functions, and consider turning off “Enable iterative calculation” unless needed.

Conclusion

Wildcard matching with XLOOKUP brings power and simplicity to a task traditionally riddled with workarounds. By learning how to craft effective patterns, set the all-important match_mode to 2, and control search direction, you can unlock rapid and reliable lookups in messy, real-world datasets. This skill bolsters your overall Excel proficiency, enabling you to build smarter dashboards, automate data cleansing, and answer stakeholder questions faster. Keep experimenting with dynamic patterns, combine XLOOKUP with LET for clarity, and soon partial-text lookups will feel as routine as SUM or AVERAGE. Happy querying!