4.7 XLOOKUP Match Modes, Search Modes and Multiple Returns
| Argument | Value | Meaning |
|---|---|---|
| match_mode | 0 (default) |
Exact match |
-1 |
Exact, else next smaller item | |
1 |
Exact, else next larger item | |
2 |
Wildcard match (*, ?, ~) |
|
| search_mode | 1 (default) |
First to last |
-1 |
Last to first (find the latest entry) | |
2 / -2 |
Binary search on sorted data (ascending / descending) |
Worked examples
| Need | Formula |
|---|---|
| Latest order amount of customer Zoya (Orders sorted by date) | =XLOOKUP("Zoya", Orders[Customer], Orders[Amount], , 0, -1) |
| Store whose area contains "Road" | =XLOOKUP("*Road*", Stores[Area], Stores[Store ID], , 2) → BLK-NSK-01 (College Road) |
| Delivery fee slab (see 4.9) | =XLOOKUP(G2, FeeSlabs[Min Order], FeeSlabs[Fee], , -1) |
Multiple returns. If return_array has several columns, XLOOKUP spills them all:
=XLOOKUP(G2, Stores!$A$2:$A$8, Stores!$C$2:$E$8)
→ City, Area and City Manager in three adjacent cells (e.g. Nashik | College Road | Zoya).
Ravindra Bagale's Tip
Wildcard sathi khup students fakt "*Road*" lihitat aani match_mode 2 detach nahit – mag XLOOKUP literally "Road" shodhto aani sapdat nahi. VLOOKUP madhe wildcard automatic chalto, XLOOKUP madhe match_mode = 2 lagto, dhyan rakho. Multiple columns return kartana ujvikade jaga rikami theva, nahitar #SPILL!.
Practice task
Return the last delivery time recorded for BLK-PUN-01. Return City, Area and Manager in one XLOOKUP. Find the first product whose name contains "Grapes".