How to Use XLOOKUP with Wildcards in Excel

1. Understanding XLOOKUP Basics

XLOOKUP replaces the older VLOOKUP, HLOOKUP, and LOOKUP functions. It searches a lookup array for a value (or pattern) and returns the corresponding entry from a return array. Because it works vertically or horizontally, you no longer need separate functions for each orientation.

Argument	Description	Example
`lookup_value`	What you are searching for – a literal, a cell reference, or a wildcard pattern.	`"Apple"` or `"*ple"`
`lookup_array`	Range that contains the values to compare against `lookup_value`.	`A2:A10`
`return_array`	Range that holds the values you want to retrieve; must be the same size as `lookup_array`.	`B2:B10`
`if_not_found` (optional)	Value returned when no match is found.	`"Not found"`
`match_mode` (optional)	`0` = exact (default) • `‑1` = exact‑match or next smaller • `1` = exact‑match or next larger • `2` = wildcard match.	`2`
`search_mode` (optional)	`1` = first‑to‑last (default) • `‑1` = last‑to‑first.	`‑1`

Key points

Arrays must be the same size – otherwise XLOOKUP returns #VALUE!.
Wildcard support is enabled by setting match_mode to 2. The supported wildcards are * (any number of characters) and ? (single character).
XLOOKUP is available in Excel 365 and Excel 2021; earlier versions must use older lookup functions.
The function is case‑insensitive by default. For a case‑sensitive search use FILTER with EXACT.

2. Why Use Wildcards in XLOOKUP?

Wildcards let XLOOKUP search for patterns instead of exact text, which is useful when data is inconsistent—extra spaces, prefixes, suffixes, or partially known strings.

Situation	Wildcard pattern	Example
Partial product codes	Starts with known letters	`"ABC*"`
Names with middle initials	Any characters between first and last name	`"John*Doe"`
Trailing spaces or punctuation	Ignore stray characters at the end	`"* "`
Variable suffixes	Same base code, different region codes	`"Item-001-*"`

Benefits

One‑formula solution – No need for helper columns or nested IFs.
Fewer helper columns – the pattern lives in the formula.
Readability – The pattern appears directly in the formula, making intent clear.

When to avoid wildcards

Numeric lookups – Wildcards treat the lookup value as text, which can produce unexpected results.
Very large tables – A wildcard forces a linear scan; for millions of rows, consider a helper column that normalizes the data and then use an exact match.

3. Syntax for Wildcard XLOOKUP

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

To enable wildcard matching, set match_mode to 2.

Wildcard	Meaning	Escape sequence
`*`	Any number of characters (including none)	`~*`
`?`	Exactly one character	`~?`
`~`	Literal tilde	`~~`

Typical wildcard XLOOKUP

=XLOOKUP("*2023*", A2:A10, B2:B10, "Not found", 2)

lookup_value – a string containing * or ?.
lookup_array – column (or row) to search.
return_array – column (or row) to return.
if_not_found – optional fallback.
match_mode – 2 tells Excel to treat lookup_value as a pattern.
search_mode – optional; 1 (default) scans top‑to‑bottom, ‑1 scans bottom‑to‑top.

4. Practical Examples with Sample Data

All examples work in Excel 365 or Excel 2021.

A (Item Code)	B (Description)	C (Price)
A‑001	Red Apple	0.50
A‑002	Green Apple	0.45
B‑001	Red Banana	0.30
B‑002	Yellow Banana	0.35
C‑001	Red Cherry	1.20

4.1 Find the price of any “Red” fruit

=XLOOKUP("*Red*", B2:B6, C2:C6, "Not found", 2)

Result: 0.50 (price of the first “Red” fruit, Red Apple).

4.2 Return the Item Code for a fruit that ends with “Banana”

=XLOOKUP("*Banana", B2:B6, A2:A6, "Not found", 2)

Result: B‑001.

4.3 Get the description of the last “Red” item

=XLOOKUP("*Red*", B2:B6, B2:B6, "Not found", 2, -1)

Result: Red Cherry.

5. Handling Errors and Edge Cases

Issue	Why it Happens	Fix / Work‑around
No match found	Pattern does not exist in `lookup_array`.	Provide `if_not_found`, e.g., `=XLOOKUP(""&A2&"", B2:B100, C2:C100, "Not found", 2)`.
Multiple matches	Wildcards can match several cells; XLOOKUP returns the first match.	Use `search_mode = -1` to get the last match, or combine with `FILTER` to return all matches: `=FILTER(C2:C100, ISNUMBER(SEARCH(A2, B2:B100)))`.
Case‑sensitivity needed	XLOOKUP is case‑insensitive by default.	Use `FILTER` with `EXACT`: `=FILTER(C2:C100, EXACT(A2, B2:B100))`.
Literal `*`, `?`, or `~` in data	These characters are interpreted as wildcards.	Escape them with `~`, e.g., `=XLOOKUP("~Sales~", B2:B100, C2:C100, "Not found", 2)`.
Binary search on unsorted data	`search_mode` 2 (binary, ascending) or -2 (binary, descending) needs sorted data, and binary search does not work with wildcard matching.	Keep `search_mode` at 1 or -1 for wildcard lookups.
Whole columns are slow	`A:A` makes Excel check far more cells than needed.	Use a bounded range like `A2:A1000` or a table column.

6. Performance Tips for Large Datasets

Tip	Why it helps	How to apply
Limit the lookup range	XLOOKUP scans every cell in the range you give it.	Use a dynamic named range or a table column, e.g., `Table1[ProductCode]` instead of `A:A`.
Switch to manual calculation while building	Prevents Excel from recalculating after every edit.	Formulas → Calculation Options → Manual; press F9 when you need an update.
Use helper columns for normalized text	Avoids repeated wildcard evaluation.	Add a column `=LOWER(TRIM(A2))` and look up against that column with `match_mode = 0`.
Avoid volatile functions in lookup arrays	Functions like `INDIRECT` or `OFFSET` recalc on every change.	Replace them with static ranges or `INDEX`.
Use binary search only for exact matches on sorted data	`search_mode` 2 is faster on large sorted lists, but it cannot be combined with wildcards.	Use it only with `match_mode` 0 on ascending data.
Cache repeated expressions with LET	Prevents duplicate calculations.	`excel<br>=LET(lk, XLOOKUP(""&B2&"", A2:A1000, C2:C1000, "Not found", 2), lk)`

7. FAQ

What are the wildcard characters supported in XLOOKUP?
XLOOKUP recognizes the same wildcards as COUNTIF/SUMIF: * matches any number of characters, ? matches a single character, and ~ escapes the next character so it is treated literally.

Can I combine multiple wildcards in a single XLOOKUP?
Yes. You can mix * and ? in any order, e.g., "A*?Z" finds values that start with “A”, end with “Z”, and have at least one character in between.

How does XLOOKUP with wildcards differ from VLOOKUP and INDEX/MATCH?
XLOOKUP combines the flexibility of INDEX/MATCH with a simpler syntax and built‑in wildcard support via match_mode = 2. Unlike VLOOKUP, you can look up to the left, specify a custom “not found” value, and control search direction without extra functions.

What should I do if XLOOKUP returns #N/A when a wildcard matches?
First, verify that the pattern truly exists in the lookup array (watch for extra spaces or case differences). If the pattern is correct, supply an if_not_found argument to replace #N/A, or double‑check that match_mode is set to 2.

Is there a limit to the size of the lookup array when using wildcards?
The size limit is the same as for any XLOOKUP call: up to 2 ^ 20 (≈ 1 048 576) rows or columns, matching Excel’s maximum worksheet size. Performance may degrade with very large ranges, so use bounded ranges or helper columns for optimal speed.