XLOOKUP Function: Excel में VLOOKUP का बाप

Harsh Decode

Upgrade With Harsh | Excel Tips in Hindi

XLOOKUP Function: Excel में VLOOKUP का बाप 🚀

✍️ By Harsh Saxena | 📅 8 Min Read | 🏷️ Excel, XLOOKUP, VLOOKUP

😫 VLOOKUP की Problem: Left se right hi search hota hai, Column number yaad rakhna padta hai, Exact match ke liye FALSE likhna padta hai...

✅ XLOOKUP Solution: Kahi bhi search karo, Column number ki jhanjhat khatam, Exact match default hai!

📋 Is Guide me Kya Sikhenge

1. XLOOKUP kya hai aur VLOOKUP se better kyu hai

2. Syntax aur 6 arguments detail me

3. 6 Real Examples - Basic se Advanced tak

4. VLOOKUP vs XLOOKUP comparison table

5. Pro Tips aur Common Mistakes

🔍 XLOOKUP क्या है?

XLOOKUP function se aap table ya range me row ke through cheezein dhund sakte ho. Jaise part number se price, ya employee ID se naam.

🎯 VLOOKUP vs XLOOKUP:
VLOOKUP sirf left-to-right dekhta hai. XLOOKUP kisi bhi side ka column return kar sakta hai. Plus column number yaad rakhne ki zaroorat nahi!

⚠️ Important Note: XLOOKUP Excel 2016 aur 2019 me available nahi hai. Office 365, Excel 2021, Excel for Web me chalega. Agar kisi ne naya Excel me XLOOKUP wali file banai aur aap purane Excel me khologe, to #NAME? error aayega.

📝 Syntax aur Arguments

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Arguments Detail:

1. lookup_value - Required
Jo value search karni hai. Example: "E001" ya cell F2

2. lookup_array - Required
Jaha search karna hai. Example: B2:B11

3. return_array - Required
Jaha se result lana hai. Example: D2:D11 ya C5:D14 for multiple columns

4. [if_not_found] - Optional
Match na mile to kya dikhana hai. Default: #N/A. Example: "Not Found"

5. [match_mode] - Optional
0 = Exact match default, -1 = Exact ya next smaller, 1 = Exact ya next larger, 2 = Wildcard (* ? ~)

6. [search_mode] - Optional
1 = First to last default, -1 = Last to first, 2 = Binary ascending, -2 = Binary descending

📊 Example 1: Basic XLOOKUP

Scenario: Country name se telephone country code dhundna

Formula:

=XLOOKUP(F2, B2:B11, D2:D11)

F2 = "India", B2:B11 = Country list, D2:D11 = Code list

Result: +91

VLOOKUP Equivalent: =VLOOKUP(F2,B2:D11,3,FALSE)
Dekho XLOOKUP kitna simple - column number 3 yaad rakhne ki zaroorat nahi!

👥 Example 2: Multiple Columns Return

Scenario: Employee ID se Name + Department dono ek saath lana

Formula:

=XLOOKUP(B2, B5:B14, C5:D14)

Magic: return_array me C5:D14 diya - do columns! XLOOKUP array return kar sakta hai.

Result: Name aur Department dono aa jayenge. VLOOKUP me ye possible nahi tha.

🚫 Example 3: If Not Found Handle Karna

Formula:

=XLOOKUP(B2, B5:B14, C5:D14, "Employee not found")

Ab #N/A ki jagah "Employee not found" message aayega. User-friendly!

💰 Example 4: Approximate Match - Tax Rate

Scenario: Income ke hisaab se tax rate nikalna. Exact amount na mile to next slab ka rate:

Formula:

=XLOOKUP(E2, C2:C7, B2:B7, 0, 1)

match_mode = 1 matlab exact na mile to next larger value do

if_not_found = 0 matlab kuch na mile to 0 return karo

💡 Pro Tip: XLOOKUP me lookup_array return_array ke right me bhi ho sakta hai! Example me C column income hai aur B column tax rate. VLOOKUP me ye impossible tha - sirf left-to-right dekhta tha.

🔄 Example 5: Nested XLOOKUP - 2D Lookup

Scenario: Row aur Column dono match karke value nikalna. INDEX+MATCH ka replacement:

Formula:

=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))

Kaise kaam karta hai:
1. Pehle "Gross Profit" dhundo column B me
2. Phir "Qtr1" dhundo row 5 me
3. Dono ke intersection ki value return karo

Bonus: XLOOKUP se HLOOKUP ko bhi replace kar sakte ho! Horizontal lookup ke liye same formula use karo.

➕ Example 6: SUM + XLOOKUP

Scenario: "Grapes" se "Pears" tak ke sabhi values ka sum:

Formula:

=SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))

Magic: XLOOKUP range return karta hai! Agar B3="Grapes" aur C3="Pears", to formula ban jata hai =SUM($E$7:$E$9)

Formulas → Formula Auditing → Evaluate Formula se step-by-step dekh sakte ho

⚔️ VLOOKUP vs XLOOKUP - Final Comparison

Feature	VLOOKUP	XLOOKUP
Search Direction	Left to Right Only	Any Direction ⬅️➡️
Column Number	Yaad rakhna padta	Zaroorat nahi ✅
Exact Match	FALSE likhna padta	Default hai ✅
Multiple Columns	❌ Nahi	✅ Haan
If Not Found	IFERROR chahiye	Built-in argument
Insert Column Problem	Formula break ho jata	Koi problem nahi ✅

🎯 Conclusion: XLOOKUP Kyon Use Kare?

1. Simple: Column index number yaad rakhne ki zaroorat nahi

2. Flexible: Left, right, up, down - kahi se bhi lookup karo

3. Powerful: Multiple columns, approximate match, reverse search sab kuch

4. Safe: Column insert/delete karne se formula nahi tutega

💬 XLOOKUP use karna start kiya?

Comment me batao kaunsa example sabse useful laga!

📱 WhatsApp पर Share करें

Excel friends ke saath share karo jo abhi bhi VLOOKUP use kar rahe hain!

Harsh Decode - Upgrade With Harsh
Excel Tips in Hindi | By Harsh Saxena

Excel ko master karne ke liye jude rahiye. Next post me INDEX+MATCH vs XLOOKUP!

WhatsApp pe Share karo