Lookup & Reference
XLOOKUP wildcard partial match: remember match_mode = 2!
Search for substrings, prefixes, or patterns inside product descriptions with XLOOKUP("*", ..., 2) and escape literal asterisks with "~*".
Why "*" & Term & "*" fails in XLOOKUP unless match_mode = 2
In XLOOKUP, match_mode=0 treats wildcard characters literally. A literal pattern can match a record containing those characters; otherwise the lookup returns not found.
Pass 2 as the fifth argument to enable * and ? wildcard patterns.
| Pattern Type | Lookup Expression (match_mode = 2) | Matches |
|---|---|---|
| Contains substring | "*" & Term & "*" | "ThinkPad X1 Carbon" for "X1" |
| Begins with prefix | Term & "*" | "SKU-4402" for "SKU-" |
| Fixed-width ? pattern | "INV-????-B" | "INV-2026-B" (4 chars) |
| Literal asterisk * | SUBSTITUTE(Term, "*", "~*") | Matches literal "*" character |
Partial-match XLOOKUP with match_mode = 2
Place "Not Found" in the 4th argument (if_not_found) and 2 in the 5th argument (match_mode).
=XLOOKUP("*" & D2 & "*", A2:A5, B2:B5, "Not Found", 2)Contrast default match_mode = 0 (literal "*" fails)
Without 2 in the 5th argument, "*X1*" looks for literal asterisks and falls back to "Not Found".
=XLOOKUP("*" & D2 & "*", A2:A5, B2:B5, "Not Found", 0)Search for a literal substring containing wildcard characters
Escape tilde before the other special characters.
=XLOOKUP("*" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"~","~~"),"*","~*"),"?","~?") & "*",A2:A5,B2:B5,"Not Found",2)Inputs and interpretation
Wildcard matching is case-insensitive and returns the first matching row by default. An empty search term matches broadly. If the input must be a literal substring, escape tilde first, then asterisk and question mark; do not interpret user-entered wildcards accidentally.
Try the example
Use the original values or change them to test your understanding.