7 XLOOKUP Tricks You Didn't Know in 2026 — Advanced Patterns
Home› 7 XLOOKUP Tricks You Didn't Know 2026
🔍 Top List · XLOOKUP Deep-Dive

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

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
  • 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.

1
Beginner Excel 365 & 2021 Return Array
Multi-Column Returns

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.

=XLOOKUP(A2, IDs, CustomerData[Name:Tier])
What each argument does
A2The customer ID you're looking up.
IDsSingle column of IDs to search in.
CustomerData[Name:Tier]MULTI-column range — Name, Email, Phone, Address, Tier all returned at once.
Real use case: A customer dashboard where entering a customer ID instantly populates their name, email, phone, address, and tier across 5 cells. One formula, spilled results, zero risk of columns getting out of sync.

Google Sheets XLOOKUP behaves identically — same trick works there too. See how this compares to the classic approach in the XLOOKUP vs VLOOKUP breakdown.

2
Intermediate Excel 365 & 2021 Search Mode
Reverse Search

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.

=XLOOKUP(A2, CustomerIDs, TransactionDates, "No history", 0, -1)
What each argument does
A2Customer ID to look up.
CustomerIDsColumn of customer IDs (may have duplicates).
TransactionDatesColumn to return from.
"No history"Fallback if no match found.
0match_mode: exact match required.
-1search_mode: search bottom-to-top → returns LAST occurrence.
Real use case: A transaction log sorted chronologically. Same customer appears many times. search_mode = -1 gives you their most recent transaction date, current status, or latest order value. In VLOOKUP, this was impossible without sorting hacks.
3
Intermediate Excel 365 & 2021 Match Mode
Wildcard Matching

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.

=XLOOKUP("*" & A2 & "*", CompanyNames, Values, "Not found", 2)
What each argument does
"*" & A2 & "*"Wraps A2 in wildcards — matches any name containing A2's text anywhere.
CompanyNamesColumn to search in.
ValuesColumn to return from.
"Not found"Fallback message.
2match_mode: enables wildcard interpretation.
Real use case: User types "Acme" and gets a match on "Acme Corporation Ltd" — you don't need the exact full name. Perfect for customer lookup fields, product SKU search with variant codes, and any interface where users type partial values.
Wildcards return the FIRST match

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.

4
Intermediate Excel 365 & 2021 Match Mode
Threshold Lookups

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.

=XLOOKUP(A2, Thresholds, CommissionRates, "Below minimum", -1)
What each argument does
A2The sales value to categorize into a tier.
ThresholdsColumn of tier lower bounds (e.g. 0, 10000, 50000, 100000).
CommissionRatesColumn of corresponding rates (e.g. 5%, 8%, 12%, 15%).
"Below minimum"Displayed if A2 is below all thresholds.
-1match_mode: if no exact match, return the next SMALLER threshold's value.
Real use case: Sales value of $65,000 doesn't match a specific threshold, but with 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.

5
Advanced Excel 365 & 2021 Nested XLOOKUP
Two-Dimensional Lookup

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.

=XLOOKUP(A2, RowHeaders, XLOOKUP(B2, ColumnHeaders, DataMatrix))
How the nesting works
Inner XLOOKUPFinds the column matching B2 in ColumnHeaders, returns that entire column from DataMatrix.
Outer XLOOKUPTakes that returned column and finds the row matching A2, returning the intersection value.
Real use case: A pricing matrix where rows are customer tiers (Gold/Silver/Bronze) and columns are product categories. Input customer tier in A2 and product category in B2, get the tier-specific price for that product. Also works for tax tables (income × filing status), shipping matrices (weight × destination), and any 2D reference.

Compare to the classic INDEX/MATCH/MATCH pattern in INDEX/MATCH vs XLOOKUP.

6
Advanced Excel 365 & 2021 Performance
Binary Search

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.

=XLOOKUP(A2, SortedIDs, Values, "Not found", 0, 2)
What each argument does
A2Value to look up.
SortedIDsColumn MUST be sorted ascending for binary search to work correctly.
ValuesColumn to return from.
"Not found"Fallback message.
0match_mode: exact match.
2search_mode: binary search (requires ascending sort).
Real use case: A master customer list with 500,000 rows. Regular XLOOKUP takes noticeable seconds to recalculate on updates. Binary search XLOOKUP is essentially instant. Also invaluable for large product catalogs, historical financial data, and any reference table where speed matters.
Binary search on unsorted data returns wrong values silently

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.

7
Advanced Excel 365 & 2021 Nested XLOOKUP
Cascading Fallbacks

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.

=XLOOKUP(A2, PrimaryIDs, PrimaryPrices, XLOOKUP(A2, SecondaryIDs, SecondaryPrices, "Not in either"))
How the cascade works
Outer XLOOKUPTries to find A2 in PrimaryIDs first.
if_not_foundIf primary lookup fails, runs the inner XLOOKUP against SecondaryIDs.
Inner XLOOKUPIf secondary lookup also fails, returns "Not in either" as the ultimate fallback.
Real use case: Customer-specific pricing → falls back to standard tier pricing → falls back to list price. Or: check inventory in local warehouse first → check regional warehouse → check central warehouse. Any scenario with a preferred source and one or more backup sources.
Without this trick Long IFERROR-wrapped nested lookups. Multiple helper columns for each fallback layer. Difficult to modify without breaking references.
With this trick One nested XLOOKUP formula. Adding more fallback layers means adding another nested XLOOKUP. Reads top-down like a priority list.

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:

=XLOOKUP("*" & A2 & "*", CompanyNames, CustomerData[Name:Tier], "No match", 2)

Latest transaction across multiple customer entries

Combining tricks 1 and 2 — for a customer who appears multiple times, return their latest transaction row:

=XLOOKUP(A2, CustomerIDs, TransactionData[Date:Amount], "No history", 0, -1)

Fast tier lookup on huge sorted dataset

Combining tricks 4 and 6 — binary search + approximate match for tier lookups on massive sorted data:

=XLOOKUP(A2, SortedThresholds, TierRates, "Below range", -1, 2)
The 6-argument mental model

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 modeBehaviorUse for
0 (default)Exact match onlyIDs, exact codes, precise lookups
-1Exact or next SMALLERTier lookups, tax brackets, thresholds (looking down)
1Exact or next LARGERShipping cutoffs, cost breakpoints (looking up)
2Wildcard matchPartial text search, pattern matching
Search modeBehaviorUse for
1 (default)First-to-last linearStandard case, finds first match
-1Last-to-first linearLatest transaction, most recent status
2Binary ascendingMassive sorted datasets (100k+ rows)
-2Binary descendingMassive sorted datasets, reverse-sorted

Related resources

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.