7 XLOOKUP tricks you didn't know in 2026
Most people who use XLOOKUP know 20% of what it can do — the basic =XLOOKUP(value, range1, range2) pattern that replaces VLOOKUP. The other 80% is where XLOOKUP becomes genuinely magical: reverse searches, wildcard matching, 2D lookups, cascading fallbacks, and binary search on massive datasets. This is the advanced reference — 7 techniques that most analysts never discover.
🔍 Before we start — the full XLOOKUP syntax
XLOOKUP has 6 arguments — most tutorials only teach the first 3. The last 3 unlock everything on this list.
The complete XLOOKUP signature
lookup_value— What you're searching for.lookup_array— The single row or column to search in.return_array— The single or multi-column range to return values from.[if_not_found]— What to display if nothing matches (skip the IFERROR wrapper).[match_mode]— 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard match.[search_mode]— 1 = first-to-last (default), -1 = last-to-first, 2 = binary ascending, -2 = binary descending.
Quick navigation — jump to any trick
Return an entire row or column in one formula
XLOOKUP's return_array doesn't have to be a single column — it can be an entire multi-column range. Pass a 5-column range and XLOOKUP returns all 5 columns for the matched row. This one trick replaces 5 separate VLOOKUP formulas with one XLOOKUP that spills across cells.
Google Sheets XLOOKUP behaves identically — same trick works there too. See how this compares to the classic approach in the XLOOKUP vs VLOOKUP breakdown.
Search bottom-up to find the LAST match
By default XLOOKUP returns the first match it finds — searching top to bottom. Setting the 6th argument (search_mode) to -1 reverses the direction, giving you the LAST occurrence instead. This is invaluable when a customer has multiple entries and you want the most recent one.
search_mode = -1 gives you their most recent transaction date, current status, or latest order value. In VLOOKUP, this was impossible without sorting hacks.
Wildcard matching for partial text
XLOOKUP doesn't accept wildcards by default — a design choice that prevents accidental fuzzy matches. But set match_mode to 2 and wildcards spring to life. Use * for any sequence of characters and ? for any single character.
If your search term matches multiple entries ("Acme" matches "Acme Corp" AND "Acme Industries"), you get the first one. For predictable results, make wildcard patterns as specific as possible — "*" & A2 & "*" matches broadly, but A2 & "*" only matches names starting with A2's value.
Approximate match for tier lookups — both directions
Tier tables — commission tiers, tax brackets, shipping costs, grade cutoffs — are approximate lookups. You want "the highest threshold that A2 exceeds." VLOOKUP handles this but REQUIRES sorted data and only works one direction. XLOOKUP works both directions and doesn't require sorting.
match_mode = -1 XLOOKUP finds the highest threshold at or below $65,000 (the $50,000 tier) and returns its commission rate. Also works for tax brackets, shipping costs, tier assignments, and any "bucketing" logic.
Use match_mode = 1 for the opposite direction (next larger). See IFS vs Nested IF for when to use IFS instead of XLOOKUP for tiered logic.
2D lookup with nested XLOOKUP (replaces INDEX/MATCH/MATCH)
The classic "2D lookup" — finding a value at the intersection of a row header and a column header — used to require the notorious INDEX/MATCH/MATCH pattern. Nested XLOOKUPs do the same thing more readably.
Compare to the classic INDEX/MATCH/MATCH pattern in INDEX/MATCH vs XLOOKUP.
Binary search for massive datasets — 10-100x faster
For datasets with 100,000+ rows, XLOOKUP's default linear search becomes slow. Binary search (search_mode = 2) is dramatically faster — but requires your lookup data to be sorted ascending. Use -2 for data sorted descending.
Unlike VLOOKUP's approximate mode which also requires sorted data, XLOOKUP's binary search doesn't error out on unsorted data — it returns wrong values without warning. Only use search_mode = 2 when you're 100% certain the lookup column is sorted correctly. Add a sanity check formula that verifies sort order.
Cascading fallbacks — XLOOKUP inside itself
The 4th argument (if_not_found) accepts any formula, including another XLOOKUP. This creates cascading fallback logic: try the primary lookup first, and only if that fails, try a secondary source. Chain as many as you need.
See the errors this pattern prevents in IFERROR vs IFNA — cascading XLOOKUP with a final fallback message is the cleanest error-handling pattern for multi-source lookups.
Combining the tricks
The real power emerges when you combine these tricks in one formula. A few patterns worth memorizing:
Wildcard search returning entire row
Combining tricks 1 and 3 — user types a partial company name, gets back all customer details in one shot:
Latest transaction across multiple customer entries
Combining tricks 1 and 2 — for a customer who appears multiple times, return their latest transaction row:
Fast tier lookup on huge sorted dataset
Combining tricks 4 and 6 — binary search + approximate match for tier lookups on massive sorted data:
Every XLOOKUP is essentially answering 6 questions: What am I looking for (arg 1)? Where to search (arg 2)? What to return (arg 3)? Fallback if not found (arg 4)? How exact does the match need to be (arg 5)? Which direction to search (arg 6)? Master those 6 questions and every XLOOKUP formula becomes clear.
What XLOOKUP still can't do
For all its power, XLOOKUP has limits worth knowing:
Case-insensitive matching by default only. XLOOKUP matches "APPLE" and "apple" as the same value. If you need case-sensitive matching, wrap with EXACT: =XLOOKUP(TRUE, EXACT(A2, LookupColumn), Values).
No horizontal-then-vertical lookup with regex. XLOOKUP does 2D via nested XLOOKUP (trick 5) but no true regex-based matching. For regex, you need Power Query or LAMBDA-based custom functions.
No multi-criteria matching without concatenation. XLOOKUP looks for one value. For multi-criteria matching, you concatenate: =XLOOKUP(A2 & B2, ColumnA & ColumnB, Values) — works but requires an implicit array formula in some Excel versions.
Not available in Excel 2019 or earlier. Any workbook shared with non-365 users will show #NAME? errors on XLOOKUP formulas. For cross-version compatibility, use VLOOKUP or INDEX/MATCH.
The XLOOKUP arguments cheat sheet
| Match mode | Behavior | Use for |
|---|---|---|
0 (default) | Exact match only | IDs, exact codes, precise lookups |
-1 | Exact or next SMALLER | Tier lookups, tax brackets, thresholds (looking down) |
1 | Exact or next LARGER | Shipping cutoffs, cost breakpoints (looking up) |
2 | Wildcard match | Partial text search, pattern matching |
| Search mode | Behavior | Use for |
|---|---|---|
1 (default) | First-to-last linear | Standard case, finds first match |
-1 | Last-to-first linear | Latest transaction, most recent status |
2 | Binary ascending | Massive sorted datasets (100k+ rows) |
-2 | Binary descending | Massive sorted datasets, reverse-sorted |
Related resources
Go deeper with the complete lookup guide
The VLOOKUP Complete Guide covers XLOOKUP, VLOOKUP, INDEX/MATCH, and every lookup pattern you'll need — with a companion workbook to practice.
VLOOKUP Complete Guide → XLOOKUP Function Page →Frequently asked questions
Can XLOOKUP return multiple columns at once?
Yes. If you pass a multi-column range as the return_array argument, XLOOKUP returns the entire row for the matched value. For example =XLOOKUP(A2, IDs, Table[Name:Tier]) returns all columns between Name and Tier for the matched ID. This replaces multiple separate VLOOKUP formulas with one XLOOKUP that spills across cells.
How do I find the last matching value with XLOOKUP?
Set the search_mode argument to -1 to search from the bottom of the range upward. The formula =XLOOKUP(A2, IDs, Values, "Not found", 0, -1) finds the LAST occurrence of the search value instead of the first. This is perfect for finding the most recent transaction, latest status, or current price when history is stacked in chronological order.
Does XLOOKUP support wildcards?
Yes, but you must explicitly enable wildcard mode by setting match_mode to 2. The formula =XLOOKUP("*" & A2 & "*", Names, Values, "Not found", 2) matches any name containing the value in A2. Wildcards are * for any sequence of characters and ? for any single character. By default XLOOKUP does exact matching only.
How does XLOOKUP approximate match differ from VLOOKUP?
XLOOKUP's approximate match works in both directions and does not require sorted data. Match mode -1 returns the next smaller item if exact match is not found. Match mode 1 returns the next larger item. VLOOKUP's approximate match only works one direction and REQUIRES sorted data — if data is not sorted VLOOKUP returns wrong results silently. XLOOKUP is safer for tier and threshold lookups.
Can XLOOKUP do 2D lookups like INDEX/MATCH/MATCH?
Yes, by nesting one XLOOKUP inside another. The pattern =XLOOKUP(RowKey, RowHeaders, XLOOKUP(ColKey, ColHeaders, DataTable)) looks up both the row and column position and returns the intersection value. This replaces the classic INDEX/MATCH/MATCH pattern with cleaner, more readable code.
Is XLOOKUP faster than VLOOKUP on large datasets?
By default XLOOKUP is similar speed to VLOOKUP for exact matches. However XLOOKUP supports binary search via search_mode 2 (ascending) or -2 (descending) which is 10-100x faster on sorted data with 100,000+ rows. VLOOKUP's approximate mode also uses binary search but with less flexibility. For truly massive lookup tables, XLOOKUP binary search is the fastest option.
Can I nest XLOOKUP inside itself as a fallback?
Yes. The if_not_found argument accepts any formula including another XLOOKUP. This creates cascading fallback logic: =XLOOKUP(A2, PrimaryIDs, Values, XLOOKUP(A2, SecondaryIDs, Values, "Not in either")). Excel tries the primary lookup first, and only if that fails does it try the secondary. Perfect for multi-source data where you have a preferred source and a backup.
Does XLOOKUP work in Google Sheets?
Yes. Google Sheets added XLOOKUP support and it behaves identically to Excel's version, including all 6 arguments. All tricks on this list work in Google Sheets. This is one of the areas where the two platforms have fully converged on function syntax.
What Excel version do I need for XLOOKUP?
XLOOKUP requires Excel 365 (any current subscription version) or Excel 2021 as a standalone purchase. It is NOT available in Excel 2019, 2016, or earlier. Workbooks using XLOOKUP opened in older Excel versions show #NAME? errors on those cells. For cross-version compatibility with older Excel, use VLOOKUP or INDEX/MATCH instead.
Bottom line: XLOOKUP's power lives in its last 3 arguments. Most tutorials skip them. These 7 tricks turn XLOOKUP from a VLOOKUP replacement into one of Excel's most flexible functions.