While the standard XLOOKUP formula has largely replaced legacy VLOOKUP and INDEX/MATCH combinations for straightforward tabular queries, most spreadsheet users rely exclusively on its default exact-match behaviour. However, when querying massive enterprise datasets spanning hundreds of thousands of rows, or searching against partial SKU strings, serial prefixes, and wildcards, standard lookup configurations cause noticeable spreadsheet recalculation lag.
Google Sheets includes two advanced parameter switches in XLOOKUP engineered specifically for complex and high-speed lookups: Match Mode 2 (Wildcards) and Search Mode 2 (Binary Search). This guide details their mathematical architecture, syntax rules, and performance optimisation strategies.
Understanding Advanced XLOOKUP Parameters
=XLOOKUP(lookup_value, lookup_range, result_range, [missing_value], [match_mode], [search_mode])- match_mode
2: Enables wildcard characters (*matches any sequence of characters,?matches any single character, and~escapes literal wildcards). - search_mode
2: Executes a binary search on a range sorted in ascending order. Reduces time complexity from linear $O(n)$ to logarithmic $O(\log n)$. - search_mode
-2: Executes a binary search on a range sorted in descending order.
Step-by-Step Practical Implementations
1. Wildcard Matching on Partial Product Serial Numbers
Suppose you need to retrieve product prices from column C where product serial numbers in column A contain a specific regional code (e.g. EU in the middle of SN-EU-9821):
=XLOOKUP("*EU*", A2:A5000, C2:C5000, "Not Found", 2)Setting match_mode to 2 instructs Google Sheets to evaluate * as a wildcard pattern rather than searching for literal asterisks.
2. Binary Search for Sub-Millisecond Lookups on Large Tables
When searching through a ledger of 100,000 transaction IDs, a linear scan inspects every row sequentially until a match occurs. If the lookup range is sorted in ascending order, enable binary search:
=XLOOKUP(F2, A2:A100000, B2:B100000, "Not Found", 0, 2)Under binary search, Google Sheets cuts the search space in half with every check, resolving lookups across 100,000 rows in approximately 17 comparison operations instead of 100,000.
Comparison of XLOOKUP Match and Search Modes
| Parameter | Value Setting | Algorithm Behaviour | Prerequisites |
|---|---|---|---|
match_mode |
0 (Default) |
Exact match lookup | None |
match_mode |
-1 |
Exact match, otherwise returns next smaller item | Useful for tax brackets |
match_mode |
1 |
Exact match, otherwise returns next larger item | Useful for volume discount tiers |
match_mode |
2 |
Wildcard pattern matching (*, ?, ~) |
String lookups only |
search_mode |
1 (Default) |
Linear scan from first to last | None |
search_mode |
2 |
High-speed binary search (Ascending) | Lookup column MUST be sorted ascending |
search_mode |
-2 |
High-speed binary search (Descending) | Lookup column MUST be sorted descending |
Troubleshooting Common Errors
- Corrupt Results with Binary Search: If you specify
search_mode 2on an unsorted range, the binary search algorithm will return invalid data or false#N/Aerrors. Always sort your lookup table or keepsearch_modeat default1. - Searching Literal Asterisks: To look up an item that literally contains an asterisk (such as a footnote marker), escape it with a tilde:
"~*".