XLOOKUP vs VLOOKUP: मुख्य अंतर, उदाहरण और कौन-सा इस्तेमाल करें

XLOOKUP vs VLOOKUP के चुनाव में, अगर file खोलने वाले हर व्यक्ति के पास Excel for Microsoft 365, Excel 2021 या उसके बाद का version, या Excel for the web है, तो XLOOKUP इस्तेमाल करें। यह वह सब करता है जो VLOOKUP करता है, पर गलतियाँ कम होती हैं: यह डिफ़ॉल्ट रूप से exact match करता है, बाईं ओर देख सकता है, और column जोड़ने पर टूटता नहीं। VLOOKUP सिर्फ़ तब इस्तेमाल करें जब workbook को Excel 2019 या उससे पुराने version में चलना हो।

यही compatibility वाली बात मुख्य चेतावनी है। XLOOKUP Excel 2016 या 2019 में मौजूद ही नहीं है, इसलिए XLOOKUP formulas से भरी file उन versions पर चलाने वाले हर व्यक्ति को #NAME? errors दिखाती है।

ध्यान दें: Excel के Hindi version में भी function names आमतौर पर English में ही रहते हैं। इस article के सारे formulas English version of Excel की तरह ही दिखाए गए हैं।

Syntax आमने-सामने

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

बड़ा फ़र्क इसमें है कि आप Excel को जवाब की जगह कैसे बताते हैं। VLOOKUP पूरी table और उस table के बाएँ किनारे से गिना हुआ column number लेता है। XLOOKUP दो अलग ranges लेता है: जिस column में खोजना है और जिस column से लौटाना है।

एक ही lookup दोनों तरीकों से

यह A1:D6 में एक छोटी product list है, और G2 में एक product ID लिखा है:

P-104 की कीमत ढूँढने के लिए:

=VLOOKUP(G2, A2:D6, 4, FALSE)
=XLOOKUP(G2, A2:A6, D2:D6)

दोनों $59.00 लौटाते हैं। VLOOKUP A2:D6 के पहले column में खोजता है और चौथा column लौटाता है; FALSE exact match को ज़रूरी बनाता है। XLOOKUP A2:A6 में खोजता है और D2:D6 से मेल खाने वाला cell लौटाता है, जिसमें exact match डिफ़ॉल्ट है।

अगर आप formula को किसी column में नीचे copy करेंगे, तो table को absolute references से lock करें, जैसे =XLOOKUP(G2, $A$2:$A$6, $D$2:$D$6)। Reference के अंदर cursor रखकर $ signs जोड़ने के लिए Windows पर F4 या Mac पर Cmd+T दबाएँ।

VLOOKUP की दो पुरानी दिक़्क़तें

1. FALSE (या 0) भूल जाना

VLOOKUP का आखिरी argument optional है, और उसे छोड़ने पर Excel approximate match इस्तेमाल करता है। Approximate match में VLOOKUP मान लेता है कि पहला column sorted है और बिना किसी चेतावनी के वह सबसे नज़दीकी value लौटाता है जो आपकी खोजी हुई value से कम या बराबर हो।

ऊपर की table पर =VLOOKUP("P-110", A2:D6, 4) आज़माएँ। P-110 मौजूद नहीं है, लेकिन #N/A की जगह आपको $32.75 मिलता है, जो P-105 की कीमत है, क्योंकि "P-105" सबसे नज़दीकी छोटी value है। बिना sort किए data पर नतीजे और भी बेतरतीब हो सकते हैं।

Exact-match वाले VLOOKUP को हमेशा FALSE या 0 पर खत्म करें। XLOOKUP यह समस्या टालता है क्योंकि जब तक आप कुछ और न कहें वह exact match इस्तेमाल करता है।

2. Hard-coded column number

VLOOKUP formula का 4 बस एक संख्या है। अगर कोई Product और Category के बीच Supplier column जोड़ दे, तो table A:E बन जाती है, Price column E में चला जाता है, और formula अब भी चौथा column लौटाता है, जो अब Category है। आपको $59.00 की जगह "Video" दिखेगा।

XLOOKUP सीधे price column (D2:D6) की ओर इशारा करता है, इसलिए Excel उसे अपने-आप E2:E6 कर देता है और जवाब सही बना रहता है।

XLOOKUP जो कर सकता है और VLOOKUP नहीं

बाईं ओर देखें

VLOOKUP सिर्फ़ lookup column के दाईं ओर के columns लौटा सकता है। XLOOKUP को दिशा से फ़र्क नहीं पड़ता। G2 में दिए product name का product ID ढूँढने के लिए:

=XLOOKUP(G2, B2:B6, A2:A6)

G2 में "Desk Lamp" हो तो यह P-105 लौटाता है, भले ही column A, column B के बाईं ओर है।

कुछ न मिलने पर आसान संदेश दिखाएँ

=XLOOKUP(G2, A2:A6, D2:D6, "Not found")

VLOOKUP में इसके बराबर के लिए wrapper चाहिए: =IFNA(VLOOKUP(G2, A2:D6, 4, FALSE), "Not found")।

एक formula में कई columns लौटाएँ

=XLOOKUP(G2, A2:A6, B2:D6)

P-104 के लिए यह Webcam HD, Video और $59.00 को बगल-बगल तीन cells में spill कर देता है। उन cells को खाली रखें, वरना #SPILL! error आएगा।

पहले की जगह आखिरी match खोजें

जिस log में एक ही customer कई बार आता है, वहाँ सबसे नीचे से ऊपर की ओर खोजने और सबसे ताज़ा entry पाने के लिए search_mode को -1 पर सेट करें:

=XLOOKUP(G2, A2:A500, C2:C500, "Not found", 0, -1)

बिना sorting के approximate match

Tiered कीमतों या commission rates के लिए VLOOKUP का approximate match तभी काम करता है जब पहला column ascending क्रम में sorted हो। XLOOKUP के match_mode -1 (exact match, या अगला छोटा item) और 1 (exact match, या अगला बड़ा item) को sorted data नहीं चाहिए। J2:J5 में न्यूनतम order की रकम और K2:K5 में discounts के साथ:

=VLOOKUP(B2, J2:K5, 2, TRUE)
=XLOOKUP(B2, J2:J5, K2:K5, , -1)

Wildcards

दोनों * और ? को support करते हैं, लेकिन XLOOKUP में उन्हें wildcard मानने के लिए match_mode 2 चाहिए:

=VLOOKUP("Desk*", B2:D6, 3, FALSE)
=XLOOKUP("Desk*", B2:B6, D2:D6, , 2)

दोनों Desk Lamp के लिए $32.75 लौटाते हैं।

INDEX/MATCH: पुराना, भरोसेमंद विकल्प

XLOOKUP से पहले VLOOKUP की सीमाओं का मानक हल INDEX और MATCH था। यह बाईं ओर देखता है, columns जोड़ने पर टिकता है, और Excel के हर version में काम करता है, इसीलिए उन files के लिए सबसे अच्छा चुनाव है जिन्हें Excel 2019 या उससे पुराने version में खुलना है:

=INDEX(D2:D6, MATCH(G2, A2:A6, 0))
=INDEX(A2:A6, MATCH(G2, B2:B6, 0))

पहला G2 के ID की कीमत लौटाता है; दूसरा बाईं ओर देखने वाला उदाहरण है। MATCH का 0 exact match दर्शाता है, और VLOOKUP के FALSE की तरह इसे भी भूलना आसान है। XLOOKUP उसी विचार का बस छोटा और ज़्यादा सुरक्षित रूप है।

VLOOKUP formulas को XLOOKUP में कैसे बदलें

कोई built-in converter नहीं है, पर अनुवाद यांत्रिक है:

1. अपने VLOOKUPs ढूँढें: Ctrl+F (Mac पर Cmd+F) दबाएँ, VLOOKUP लिखें, अगर अतिरिक्त settings छिपी हैं तो Options पर click करें, Look in को Formulas पर सेट करें, और Find All पर click करें।
2. पहला argument (lookup value) जैसा है वैसा रखें।
3. Table की जगह उसका पहला column lookup_array के रूप में रखें।
4. Column number की जगह मेल खाने वाला column return_array के रूप में रखें। जो table column A से शुरू होती है उसका column 5, column E होता है।
5. FALSE हटा दें। अगर मूल formula TRUE पर खत्म होता था या उसमें आखिरी argument था ही नहीं, तो "अगला छोटा" वाला व्यवहार बनाए रखने के लिए match_mode -1 जोड़ें।

उदाहरण के लिए:

=VLOOKUP(A2, Products!$A$2:$F$500, 5, FALSE)
=XLOOKUP(A2, Products!$A$2:$A$500, Products!$E$2:$E$500)

पूरी sheet बदलने से पहले कुछ नतीजे पुराने formula से मिलाकर जाँच लें। बिना sorted data पर approximate-match वाले VLOOKUPs शायद शुरू से गलत values लौटा रहे हों, इसलिए XLOOKUP के जवाब जायज़ तौर पर अलग हो सकते हैं।

XLOOKUP वाली file Excel 2016 या 2019 में खुले तो क्या होता है

Workbook खुल जाता है, लेकिन वे versions function को पहचानते नहीं। Formula bar में यह =_xlfn.XLOOKUP(...) की तरह दिखता है। हो सकता है cells शुरू में आखिरी बार सहेजे गए नतीजे दिखाते रहें, लेकिन formula के दोबारा calculate होते ही वह #NAME? लौटाता है।

अगर आप पुराने versions वाले लोगों के साथ files साझा करते हैं:

• इसके बजाय INDEX/MATCH या VLOOKUP इस्तेमाल करें, या
• ऐसी copy भेजें जिसमें नतीजे values के रूप में paste किए हों (Copy → Paste Special → Values)।

यही बात FILTER, UNIQUE और XMATCH जैसे दूसरे नए functions पर भी लागू होती है।

Google Sheets में XLOOKUP

Google Sheets में भी XLOOKUP है, उसी argument क्रम के साथ: =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])। इस article के formulas वहाँ जैसे लिखे हैं वैसे ही चलते हैं, इसलिए Excel और Sheets के बीच आने-जाने वाली files के लिए XLOOKUP सुरक्षित चुनाव है।

आपका lookup #N/A या गलत value क्यों लौटाता है

• फ़ालतू spaces। "P-104 " में पीछे space हो तो वह "P-104" से मेल नहीं खाता। TRIM से data साफ़ करें।
• Text के रूप में सहेजी गई संख्याएँ। Text के रूप में लिखा ID उसी ID से मेल नहीं खाता जो संख्या के रूप में सहेजा गया हो। Cell के कोने में हरा त्रिकोण ढूँढें और column को एक ही type में बदल दें।
• VLOOKUP में FALSE गायब है। आपको #N/A की जगह विश्वसनीय दिखने वाला पर गलत जवाब मिलता है।
• Formula copy करने पर खिसकी हुई ranges। Lookup table के लिए $ वाले absolute references इस्तेमाल करें।
• VLOOKUP से #REF!। Column number table के columns की संख्या से बड़ा है।
• XLOOKUP से #VALUE!। Lookup और return ranges अलग-अलग आकार की हैं, जैसे A2:A6 और D2:D60।
• #NAME? Function का नाम गलत लिखा है, या file ऐसे version में खुली है जिसमें XLOOKUP नहीं है।

अक्सर पूछे जाने वाले सवाल

क्या XLOOKUP, VLOOKUP से बेहतर है? हाँ, लगभग हर इस्तेमाल के लिए। यह डिफ़ॉल्ट रूप से exact match करता है, किसी भी दिशा में देखता है, columns खिसकने पर टूटता नहीं, और गायब values को बिना wrapper के संभालता है। VLOOKUP का एकमात्र असली फ़ायदा यह है कि वह Excel के हर version में चलता है।

क्या XLOOKUP, VLOOKUP से तेज़ है? रोज़मर्रा के workbooks में आपको कोई फ़र्क नहीं दिखेगा। बहुत बड़ी sorted lists पर XLOOKUP के binary search modes (search_mode 2 या -2) चीज़ें तेज़ कर सकते हैं, पर data sorted न हो तो वे गलत नतीजे लौटाते हैं।

मेरे computer पर XLOOKUP में #NAME? क्यों दिख रहा है? आपके Excel version में XLOOKUP शामिल नहीं है। यह Microsoft 365, Excel 2021 और उसके बाद के versions, और Excel for the web में है, पर Excel 2019 या 2016 में नहीं। इसके बजाय INDEX/MATCH इस्तेमाल करें।

क्या XLOOKUP, HLOOKUP की भी जगह ले लेता है? हाँ। अगर lookup और return ranges columns की जगह rows हैं, तो XLOOKUP उसी syntax से क्षैतिज रूप से खोजता है।

क्या XLOOKUP सिर्फ़ पहला नहीं, सारे matches लौटा सकता है? नहीं, यह एक match (या columns की एक row) लौटाता है। मेल खाने वाली हर row पाने के लिए FILTER इस्तेमाल करें, जैसे =FILTER(A2:D6, C2:C6="Office")।

क्या मुझे अब भी VLOOKUP सीखना चाहिए? उसे पहचानना ज़रूरी है, क्योंकि वह आपको पुराने workbooks और templates में मिलेगा। नए formulas के लिए XLOOKUP सीखें, और जिन files को पुराने Excel में खुलना है उनके लिए INDEX/MATCH याद रखें।

Lookup AI से लिखवाएँ

Lookup formulas वह जगह हैं जहाँ FALSE का छूट जाना या range का खिसक जाना जैसी छोटी बातें सबसे ज़्यादा चुपचाप गलतियाँ पैदा करती हैं। CoreGPT for Excel में आप बता सकते हैं कि आपको क्या चाहिए, जैसे "G2 के product ID की कीमत ढूँढो" या "हर row को target से ऊपर या नीचे के रूप में चिह्नित करो", और वह छोटी व्याख्या के साथ formula सीधे आपकी sheet में लिख देता है। शुरू करना free है और यह Windows, Mac और web पर Excel में चलता है।

आज़माएँ: CoreGPT for Excel

ये भी पढ़ें:

• Remove Duplicates in Excel: 5 तरीके (डेटा डिलीट करके और बिना डिलीट किए)
• Microsoft 365 Excel टिप्स जो हर हफ़्ते आपका समय बचाएँ
• Excel में ChatGPT कैसे इस्तेमाल करें: पूरी गाइड