दोस्तों, Excel में VLOOKUP या XLOOKUP का normal use तो सभी करते हैं — लेकिन क्या आपने कभी ऐसी situation face की जहाँ आपके Excel डाटा मे किसी company का नाम या कोई डाटा जिसको पूरे Worksheet मे खोजना है का नाम mixed हो? जैसे data में “Apple Store NY”, “Apple India Pvt Ltd”, “Apple Retail Mumbai” — और आपको सिर्फ “Apple” से search करके उसकी सबसे पहली (Oldest) या सबसे आखिरी (Latest) sale निकालनी हो? आज हम सीखेंगे XLOOKUP Wildcard se Oldest aur Last Sale kaise nikale — SWITCH Function के साथ एक fully dynamic lookup बनाकर।
यह trick interview में भी पूछी जाती है — और real-world data analysis में बेहद काम आती है।
Table of Contents
Problem क्या है? — Mixed Company Names
सबसे पहले problem को clearly समझते हैं। मान लीजिए आपके पास एक Sales Data है:
| Date | Company Info | Sales (₹) |
|---|---|---|
| 01-Jan-25 | Apple Store NY | ₹12,000 |
| 08-Jan-25 | Apple India Pvt Ltd | ₹18,000 |
| 15-Jan-25 | Apple Retail Mumbai | ₹15,000 |
| 23-Jan-25 | Apple Service Center Delhi | ₹14,000 |
| 30-Jan-25 | Apple Premium Reseller | ₹20,000 |
| 05-Feb-25 | Apple Online India | ₹17,500 |
| 14-Feb-25 | Apple Experience Center | ₹22,000 |
| 27-Feb-25 | Apple Distributor Chennai | ₹21,000 |
| 05-Mar-25 | Apple Flagship Store Pune | ₹23,000 |
अब सवाल यह है — Apple की सबसे पहली sale कितने की थी? और सबसे आखिरी sale?
Normal XLOOKUP से यह नहीं होगा क्योंकि Company Info column में exact “Apple” नहीं लिखा — हर जगह अलग-अलग नाम हैं। यहाँ Wildcard और Search Mode काम आएगा।
Solution — दो Formulas मिलकर Magic करेंगे
इस problem को solve करने के लिए हम दो formulas का combination use करेंगे:
| Step | Formula | काम क्या करेगा? |
|---|---|---|
| Step 1 | SWITCH Function | Dropdown की text value (“Oldest Sales” / “Last Sales”) को number (1 / -1) में convert करेगा |
| Step 2 | XLOOKUP Wildcard | Partial name से search करेगा + SWITCH से आई value से decide करेगा कि पहली entry चाहिए या आखिरी |
Key Insight: XLOOKUP का Search Mode argument सबसे powerful feature है जो ज्यादातर लोग ignore करते हैं। 1 = First से search (Oldest) और -1 = Last से search (Latest) — यही इस trick का दिल है।
Data Setup करें
इस technique के लिए दो चीजें चाहिए — Main Data Table और Lookup Table।
Main Data Table
Column B: Date, Column C: Company Info (mixed names), Column D: Sales Amount। Data date-wise sorted होना चाहिए — oldest date ऊपर, latest date नीचे।
Lookup Table (अलग जगह)
एक छोटा सा table बनाएं जिसमें:
- Column F: Company names (सिर्फ short keyword — जैसे “Apple”, “Samsung”)
- Column G: Search keyword (same as F — यहाँ XLOOKUP Wildcard search करेगा)
- Column H: Result आएगा (XLOOKUP formula यहाँ जाएगा)
और ऊपर एक Dropdown cell — जहाँ user “Oldest Sales” या “Last Sales” select करे।
Dropdown बनाएं — User Interface Ready करें
सबसे पहले एक Dropdown बनाएंगे जिससे user choose कर सके — Oldest Sales देखनी है या Last Sales।
Step to Apply:
- G4 cell select करें।
- Data Tab → Data Validation पर click करें।
- Allow में “List” select करें।
- Source में type करें:
Oldest Sales,Last Sales - OK click करें।
अब G4 में dropdown आ जाएगा — दो options: “Oldest Sales” और “Last Sales”।
SWITCH Function — Dropdown Value को Number में बदलें
XLOOKUP का Search Mode argument एक number चाहता है — 1 (First से search) या -1 (Last से search)। लेकिन हमारे dropdown में text है — “Oldest Sales” और “Last Sales”। SWITCH function इस text को number में convert करेगा।
SWITCH Function का Syntax
=SWITCH(expression, value1, result1, value2, result2, ...)हमारा SWITCH Formula
=SWITCH(G4, "Oldest Sales", 1, "Last Sales", -1)इसे G5 cell में लिखें।
Formula का logic:
G4— Expression: Dropdown cell को देखो।"Oldest Sales", 1— अगर G4 = “Oldest Sales” तो result = 1"Last Sales", -1— अगर G4 = “Last Sales” तो result = -1
💡 SWITCH vs IF: यही काम IF से भी होता:
=IF(G4="Oldest Sales",1,-1)— लेकिन SWITCH ज्यादा readable है और जब options ज्यादा हों तो IF nested बन जाता है। SWITCH से code clean रहता है।
Verify करें — SWITCH काम कर रहा है?
G4 में “Oldest Sales” select करें → G5 में 1 आना चाहिए।
G4 में “Last Sales” select करें → G5 में -1 आना चाहिए।
XLOOKUP Wildcard Formula — पूरा और समझाया हुआ
अब main formula। यह formula H8:H14 range में एक साथ enter करेंगे।
Step to Apply:
- H8:H14 range select करें (सभी 7 company rows एक साथ)।
- Formula type करें।
- Ctrl+Enter press करें — एक साथ सभी cells में formula fill हो जाएगा।
Complete Formula
=XLOOKUP("*"&G8&"*",$C$5:$C$29,$D$5:$D$29,"-",2,$G$5)
⭐ Wildcard का जादू:
"*"&G8&"*"से “*Apple*” बनता है। इसका मतलब है — जिस भी cell में कहीं भी “Apple” आता हो वो match करे। “Apple Store NY”, “Apple India Pvt Ltd” — सब match होंगे। यही Wildcard search है।
XLOOKUP के हर Argument को Detail में समझें
XLOOKUP के 6 arguments हैं — इन सबको ध्यान से समझें:
| Argument | हमारा Value | क्या करता है | Customize कैसे करें? |
|---|---|---|---|
| Lookup Value | "*"&G8&"*" | Wildcard search pattern बनाता है — *Apple* जैसा | G8 में अपना keyword cell reference दें |
| Lookup Array | $C$5:$C$29 | Company Info column — यहाँ search होगी | अपने data के Company column की range दें (F4 से lock करें) |
| Return Array | $D$5:$D$29 | Sales column — यहाँ से value return होगी | जो column return करना हो उसकी range (F4 से lock करें) |
| If Not Found | "-" | Match न मिले तो dash आएगा | “N/A”, 0, या कोई भी default value दें |
| Match Mode | 2 | Wildcard match enable करता है ⭐ — यह MUST है! | Wildcard के लिए हमेशा 2 ही रखें |
| Search Mode | $G$5 | SWITCH से आई value — 1=Oldest, -1=Latest | SWITCH formula cell को reference करें (F4 से lock करें) |
Match Mode क्या होता है? — पूरी Table
| Match Mode Value | क्या करता है | कब Use करें |
|---|---|---|
0 (Default) | Exact match — बिल्कुल same होना चाहिए | Normal lookup |
-1 | Exact या उससे छोटा (Approximate, Ascending) | Range lookup जैसे grades |
1 | Exact या उससे बड़ा (Approximate, Descending) | Price ranges |
2 | Wildcard match — *, ?, ~ use होते हैं | Partial text match (हमारा case) |
Search Mode क्या होता है? — पूरी Table
| Search Mode Value | क्या करता है | Result |
|---|---|---|
1 (Default) | First से Last की तरफ search करता है | Oldest (पहली matching entry) |
-1 | Last से First की तरफ search करता है | Latest (आखिरी matching entry) |
2 | Binary search (sorted ascending data) | Sorted data में fast search |
-2 | Binary search (sorted descending data) | Descending sorted data में |
Verify करें — Dynamic Formula काम कर रहा है?
Formula enter होने के बाद इन steps से verify करें:
- G4 dropdown में “Oldest Sales” select करें।
- G5 में 1 दिखना चाहिए (SWITCH काम कर रहा है)।
- H8 में Apple की पहली entry का Sales value आना चाहिए — यानी ₹12,000 (01-Jan-25 Apple Store NY)।
- अब dropdown में “Last Sales” select करें।
- G5 में -1 दिखना चाहिए।
- H8 में Apple की आखिरी entry का Sales value आना चाहिए — यानी ₹23,000 (05-Mar-25 Apple Flagship Store Pune)।
- सभी 7 companies के results automatically बदलने चाहिए।
यही है इस Trick की Power! एक dropdown change किया — और सातों companies के Oldest/Latest sales एक साथ update हो गए। यही Dynamic Lookup है।
Extra Tips और Common Mistakes
Common Mistake 1 — Match Mode = 2 भूल गए
सबसे common mistake। अगर Match Mode = 2 नहीं दिया तो Wildcard (*) काम नहीं करेगा और formula error या wrong result देगा। Partial match के लिए Match Mode = 2 हमेशा जरूरी है।
Common Mistake 2 — Data Date-wise Sorted नहीं है
Search Mode 1 (Oldest) और -1 (Latest) सही काम करे इसके लिए data date-wise ascending order में sorted होना चाहिए — oldest date ऊपर, latest नीचे। गलत sorting से गलत results आएंगे।
Common Mistake 3 — Lookup Array को Lock नहीं किया
Formula को Ctrl+Enter से multiple cells में fill करते समय $C$5:$C$29 और $D$5:$D$29 में $ (absolute reference) जरूर होना चाहिए — वरना formula copy होने पर range shift हो जाएगी।
' गलत — बिना lock के
=XLOOKUP("*"&G8&"*",C5:C29,D5:D29,"-",2,G5)
' सही — F4 से lock करके
=XLOOKUP("*"&G8&"*",$C$5:$C$29,$D$5:$D$29,"-",2,$G$5)XLOOKUP Wildcard — और क्या-क्या Search Patterns बन सकते हैं
| Pattern | Formula में | क्या Match करेगा |
|---|---|---|
| कहीं भी हो | "*"&G8&"*" | “Apple Store NY”, “Apple India” — कहीं भी Apple |
| शुरू से match | G8&"*" | “Apple…” — Apple से शुरू होने वाले |
| अंत में match | "*"&G8 | “…Apple” — Apple पर खत्म होने वाले |
| Single char | "Appl?" | “Apple”, “Appls” — 5 characters, last कोई भी |
Excel formulas और lookup functions के बारे में और जानने के लिए देखें: Excel में Formula और Function — Complete Guide। और XLOOKUP की official Microsoft documentation: Microsoft Support — XLOOKUP Function।
निष्कर्ष
तो दोस्तों, आज हमने step-by-step सीखा कि XLOOKUP Wildcard se Oldest aur Last Sale kaise nikale — SWITCH Function और XLOOKUP के Search Mode को मिलाकर एक fully dynamic lookup। Mixed company names से partial match, Search Mode से Oldest/Latest entry — यह combination बेहद powerful है।
यह trick real-world data analysis में बहुत काम आती है — जब आपका data imperfect हो, company names mixed हों, लेकिन फिर भी accurate lookup चाहिए। एक dropdown change करो और पूरा table update हो जाए — यही तो smart Excel है!
नीचे comment जरूर करें — आपने इसे किस data पर try किया? Match Mode और Search Mode में कोई confusion आई? सब पूछें! और TheOfficeTutorials.com को bookmark करना न भूलें — Excel के ऐसे ही advanced formula tricks यहाँ लगातार आते रहते हैं। 🙌
FAQ — अक्सर पूछे जाने वाले सवाल
Q1. XLOOKUP में Wildcard use करने के लिए क्या करना होगा?
XLOOKUP में Wildcard use करने के लिए Match Mode = 2 set करना mandatory है। Formula में 5th argument (Match Mode) में 2 लिखें। साथ ही Lookup Value में "*"&cell&"*" pattern use करें। बिना Match Mode = 2 के Wildcard (*) काम नहीं करेगा।
Q2. Search Mode 1 और -1 में क्या फर्क है?
Search Mode 1 = data को ऊपर से नीचे scan करता है → पहली matching entry return करता है (Oldest)। Search Mode -1 = data को नीचे से ऊपर scan करता है → आखिरी matching entry return करता है (Latest)। Date-wise sorted data में यह perfectly काम करता है।
Q3. SWITCH function की जगह IF use कर सकते हैं?
हाँ — =IF(G4="Oldest Sales",1,-1) same काम करेगा। लेकिन जब options 3 या ज्यादा हों तो SWITCH ज्यादा clean और readable होता है। दो options के लिए IF भी ठीक है।
Q4. Formula Ctrl+Enter से क्यों enter किया — सिर्फ Enter से क्यों नहीं?
जब multiple cells (H8:H14) एक साथ select करके formula type करें — तब Ctrl+Enter सभी selected cells में एक साथ formula fill करता है। सिर्फ Enter से केवल एक cell (H8) में formula जाएगा। इससे time बचता है और formula consistent रहता है।
Q5. अगर Company का नाम data में बिल्कुल नहीं है तो क्या होगा?
Formula में हमने if_not_found = "-" दिया है। अगर कोई matching entry नहीं मिली तो result में dash (—) आएगा। आप यहाँ "Not Found", 0, या कोई भी default value दे सकते हैं।
Q6. क्या यह formula Google Sheets में काम करेगा?
Google Sheets में XLOOKUP available है लेकिन Search Mode argument support नहीं है। इसलिए यह exact technique Google Sheets में काम नहीं करेगी। इसके लिए INDEX+MATCH combination का अलग approach चाहिए।
Q7. क्या एक साथ दो columns return कर सकते हैं — Sales और Date दोनों?
हाँ! Return Array में दोनों columns include करें: $C$5:$D$29 (अगर C=Date, D=Sales हो)। XLOOKUP दोनों columns का data return करेगा — लेकिन formula को दो adjacent cells में enter करना होगा। Excel 365/2021 में यह automatically spill होगा।