4.7 XLOOKUP Match Modes, Search Modes and Multiple Returns
XLOOKUP search mode (last match) with example — in 30 seconds
What: The 6th argument of XLOOKUP sets the search direction. -1 searches from the bottom, so you get the latest match.
Why: "What was Zoya's last order?" A normal lookup gives her first order, which is old news.
Tiny example: Zoya ordered ₹120 and later ₹450. Search mode -1 returns ₹450.
XLOOKUP search mode (last match) with example — 30 सेकंदात
काय: XLOOKUP चा 6वा argument शोधायची दिशा ठरवतो. -1 खालून वर शोधतो, म्हणजे सगळ्यात नवीन match मिळतो.
का: "Zoya ची शेवटची order कोणती?" साधा lookup तिची पहिली order देतो, जी जुनी बातमी आहे.
छोटं उदाहरण: Zoya ने आधी ₹120 ची आणि नंतर ₹450 ची order दिली. Search mode -1 उत्तर ₹450 देतो.
XLOOKUP search mode (last match) with example — 30 सेकंड में
क्या: XLOOKUP का 6वाँ argument खोज की दिशा तय करता है. -1 नीचे से ऊपर खोजता है, तो सबसे नया match मिलता है.
क्यों: "Zoya का आख़िरी order कौन सा?" सादा lookup उसका पहला order देता है, जो पुरानी ख़बर है.
छोटा उदाहरण: Zoya ने पहले ₹120 का और बाद में ₹450 का order दिया. Search mode -1 जवाब ₹450 देता है.
First look at the data (before → after):
Expected result — First vs last match
| Formula ends with | Result (₹) |
|---|---|
| …Orders[Amount]) (default) | 120 |
| …, , 0, -1) | 450 |
About this data: Teaching sample for a fictional shop “Orbit Logistics”. Figures and names (e.g. Aamir Khan) are made up for this lesson on xlookup match modes search modes and multiple.
Before table and formula as text
Before — Orders table (sorted by date)
| Date | Customer | Amount (₹) |
|---|---|---|
| 03-11-2026 | Zoya | 120 |
| 04-11-2026 | Amir | 300 |
| 07-11-2026 | Zoya | 450 |
| 08-11-2026 | Ravina | 180 |
About this data: Demo table only — not real customer or company data. Built to practise xlookup match modes search modes and multiple.
Formula / click path
=XLOOKUP("Zoya", Orders[Customer], Orders[Amount]) → first match
=XLOOKUP("Zoya", Orders[Customer], Orders[Amount], , 0, -1) → last match
Do it step by step:
- Make sure the table is sorted by date, oldest first.
- Type
=XLOOKUP("Zoya", Orders[Customer], Orders[Amount]). You get the first order. - Now type
=XLOOKUP("Zoya", Orders[Customer], Orders[Amount], , 0, -1). - The two commas skip if_not_found. 0 means exact match. -1 means search from the last row.
- Check: 120 and 450.
स्टेप बाय स्टेप करून बघा:
- Table तारखेनुसार sort आहे याची खात्री करा, जुनं आधी.
=XLOOKUP("Zoya", Orders[Customer], Orders[Amount])type करा. पहिली order मिळेल.- आता
=XLOOKUP("Zoya", Orders[Customer], Orders[Amount], , 0, -1)type करा. - दोन commas मुळे if_not_found skip होतो. 0 म्हणजे exact match. -1 म्हणजे शेवटच्या row पासून शोध.
- Check करा: 120 आणि 450.
स्टेप बाय स्टेप करके देखें:
- पक्का करें कि table तारीख़ से sort है, पुराना पहले.
=XLOOKUP("Zoya", Orders[Customer], Orders[Amount])type करें. पहला order मिलेगा.- अब
=XLOOKUP("Zoya", Orders[Customer], Orders[Amount], , 0, -1)type करें. - दो commas से if_not_found skip होता है. 0 यानी exact match. -1 यानी आख़िरी row से खोज.
- Check करें: 120 और 450.
| 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) |
About this data: Made-up rows so you can copy the pattern for xlookup match modes search modes and multiple. Currencies may use ₹, £, € or $.
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) |
About this data: Fictional worksheet used in class. Safe to edit; nothing here is live production data.
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".
Loading practice tasks...
- First order of Zoya (Easy): Find the Amount of Zoya's first order.
- Amir's order (Easy): Find the Amount for "Amir".
- Tracking: first scan (Easy): Find the first Status of AWB-7001.
- Last order of Zoya (Medium): Find the Amount of Zoya's last order.
- Date of last order (Medium): Find the Date of Zoya's last order.
- Tracking: latest status (Medium): Find the latest Status of AWB-7001.
- Tracking: latest for AWB-7002 (Medium): Find the latest Status of AWB-7002.
- Tracking: date delivered (Medium): Find the Date of the latest scan of AWB-7001.
- No order message (Hard): Find the Amount for "Raja". If not found show "No order".
- Tracking: no scan yet (Hard): Find the latest Status of AWB-7009. Show "No scan yet" if missing.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
