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
For a wildcard, many students just write "*Road*" and don't give match_mode 2 – then XLOOKUP searches literally for "Road" and finds nothing. In VLOOKUP wildcards work automatically; in XLOOKUP you need match_mode = 2 – keep that in mind. When returning multiple columns, keep the space to the right empty, otherwise you get #SPILL!.
Ravindra Bagale's Tip – मराठी
Wildcard साठी बरेच students फक्त "*Road*" लिहितात आणि match_mode 2 देतच नाहीत – मग XLOOKUP अक्षरशः "Road" शोधतो आणि काहीच सापडत नाही. VLOOKUP मध्ये wildcard आपोआप चालतो, XLOOKUP मध्ये match_mode = 2 लागतो, हे लक्षात ठेवा. Multiple columns return करताना उजवीकडे जागा रिकामी ठेवा, नाहीतर #SPILL!.
Ravindra Bagale's Tip – हिंदी
Wildcard के लिए बहुत से students सिर्फ़ "*Road*" लिखते हैं और match_mode 2 देते ही नहीं – फिर XLOOKUP सचमुच "Road" ढूँढता है और कुछ नहीं मिलता. VLOOKUP में wildcard अपने-आप चलता है, XLOOKUP में match_mode = 2 चाहिए, ध्यान रखना. Multiple columns return करते समय दाईं तरफ़ जगह खाली रखो, वरना #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".