XLOOKUP(): Excel का सबसे बहुमुखी लुकअप फ़ंक्शन

यदि आपने कभी VLOOKUP() का उपयोग किया है और बाएँ तरफ़ खोजने की ज़रूरत के कारण अटक गए, तो XLOOKUP() वह समस्या हल कर देता है। यह Excel का नया लुकअप फ़ंक्शन है। यह एक सरल फ़ॉर्मूला से VLOOKUP(), HLOOKUP(), और अधिकांश INDEX() तथा MATCH() फ़ॉर्मूलों को भी बदल सकता है।

XLOOKUP() किसी भी दिशा में खोज सकता है, एक साथ एक से अधिक कॉलम वापस ला सकता है, और कोई मान न मिलने पर एक कस्टम संदेश दिखा सकता है। 

इस गाइड में, हम पहले सिंटैक्स से गुजरेंगे। फिर हम शुरुआत से असली फ़ॉर्मूले चरण-दर-चरण बनाएंगे, और पूरे समय एक ही सरल डेटासेट का उपयोग करेंगे। हम सटीक और अनुमानी मिलान, एक से अधिक कॉलम लौटाना, एरर को संभालना, और XLOOKUP() की तुलना VLOOKUP() और INDEX()/MATCH() से भी कवर करेंगे। 

Excel में XLOOKUP() का उपयोग कैसे करें

आइए शून्य से एक असली XLOOKUP() फ़ॉर्मूला बनाते हैं। 

हमारे डेटासेट में पाँच प्रोडक्ट हैं, प्रत्येक का एक ID, नाम, श्रेणी, और मूल्य है, जो कॉलम A से D में हैं, और हेडर पंक्ति 1 में हैं।

XLOOKUP() के लिए डेटासेट। चित्र: लेखक द्वारा।

मान लीजिए हम "P104" का मूल्य खोजना चाहते हैं। आइए इसे चरण-दर-चरण समझते हैं।

एकल मान को लुकअप करें

किसी खाली सेल पर क्लिक करें जहाँ आप परिणाम दिखाना चाहते हैं। मैं F2 का उपयोग करता/करती हूँ। और निम्न फ़ॉर्मूला टाइप करें:

=XLOOKUP("P104", A2:A6, D2:D6)

इस फ़ॉर्मूले में: 

• "P104" lookup_value है, यानी जिसे हम खोज रहे हैं

• A2:A6 lookup_array है, यानी वह रेंज जिसमें Excel ने खोज की

• D2:D6 return_array है, यानी वह रेंज जिससे Excel ने उत्तर निकाला

Excel कॉलम A, “Product ID,” में "P104" खोजता है, उसे पंक्ति 5 में पाता है, और कॉलम D से संबंधित मान लौटाता है: $45।

"P104" सीधे टाइप करने के बजाय, आप एक सेल को संदर्भित कर सकते हैं ताकि आप फ़ॉर्मूला बदले बिना लुकअप वैल्यू बदल सकें। हमारे मामले में, सेल A5 में "P104" है, तो फ़ॉर्मूला बन जाता है:

=XLOOKUP(A5, A2:A6, D2:D6)

Excel में XLOOKUP() का उपयोग करें। चित्र: लेखक द्वारा।

अब आप A5 में कोई भी प्रोडक्ट ID टाइप करें, और मूल्य अपने-आप अपडेट हो जाएगा।

किसी अन्य कॉलम से मान लौटाएँ

यही चरण XLOOKUP() और VLOOKUP() के बीच का असली अंतर दिखाता है। आप अपनी खोज वाले कॉलम के दाईं ओर के कॉलम से ही मान लौटाने तक सीमित नहीं हैं।

मान लीजिए मूल्य की बजाय आप "P102" की श्रेणी चाहते हैं। वही सेटअप, बस return_array को कॉलम C, “Category” की ओर इंगित करें:

=XLOOKUP(A3, A2:A6, C2:C6)

यहाँ, A3 का परिणाम Home आता है। यदि A5 किया जाए, तो यह Electronics में अपडेट हो जाएगा। इसी तरह, return_array बदलकर प्रोडक्ट का नाम, मूल्य या कोई और कॉलम खींचें—सब कुछ वही lookup_value और lookup_array के साथ।

किसी अन्य कॉलम से XLOOKUP() रिटर्न वैल्यू। चित्र: लेखक द्वारा।

VLOOKUP() के साथ, आप अपने खोज कॉलम से कॉलम गिनते और हर बार अलग परिणाम चाहने पर कॉलम इंडेक्स नंबर समायोजित करते। XLOOKUP() में, आप सीधे उसी कॉलम की ओर इशारा करते हैं जिसकी आपको जरूरत है। 

फ़ॉर्मूला को कई पंक्तियों में कॉपी करें

अब तक, हमने एक समय में एक मान लुकअप किया है। पर अधिकांश वास्तविक स्प्रेडशीट्स में एक साथ कई मानों का लुकअप करना होता है, जैसे ऑर्डर IDs की पूरी सूची, जिनमें से हर एक को मिलान करता मूल्य चाहिए।

इसे ऐसे सेट करें: कॉलम A में, A2 से शुरू होकर, पहले से हमारे Product IDs की सूची है। हम चाहते हैं कि हर एक के बगल में, कॉलम F में उसके मिलते-जुलते मूल्य दिखें।

ऐसा करने के लिए, F2 पर क्लिक करें और फ़ॉर्मूला टाइप करें, जहाँ लुकअप वैल्यू के लिए A2 को संदर्भित करें, इस तरह:

=XLOOKUP(A2, $A$2:$A$6, $D$2:$D$6)

अब फिर से F2 पर क्लिक करें, फिर फिल हैंडल (सेल के निचले दाएँ कोने पर छोटा वर्ग) को अपनी बाकी पंक्तियों तक खींचें।

Excel फ़ॉर्मूला नीचे कॉपी कर देता है, और अब हर पंक्ति अपने Product ID के लिए सही मूल्य लौटाती है। A2 की ID का मूल्य F2 में, A3 की ID का मूल्य F3 में, और इसी तरह।

फ़ॉर्मूला को अन्य सेल्स में नीचे कॉपी करें। चित्र: लेखक द्वारा।

A2:A6 और D2:D6 के चारों ओर डॉलर चिह्नों पर गौर करें। इसे एब्सोल्यूट रेफ़रेंस कहते हैं। यह रेंज को स्थिर रखता है, ताकि आप फ़ॉर्मूला नीचे कॉपी करें तब भी यह खिसके नहीं। 

यहाँ यह इसलिए ज़रूरी है क्योंकि हर पंक्ति को उसी प्रोडक्ट सूची में खोज करनी है, बस हर बार लुकअप वैल्यू अलग होगी।

XLOOKUP() में Exact Match और Approximate Match का उपयोग कैसे करें

डिफ़ॉल्ट रूप से, XLOOKUP() सटीक मिलान खोजता है। अगर उसे सटीक मिलान नहीं मिलता, तो यह एरर लौटाता है, जब तक कि आपने पहले बताए गए if_not_found आर्ग्युमेंट को सेट न किया हो। 

लेकिन XLOOKUP() match_mode आर्ग्युमेंट का उपयोग करके सबसे नज़दीकी मिलान भी खोज सकता है। 

सटीक मिलान (डिफ़ॉल्ट)

इसके लिए आपको कुछ अतिरिक्त टाइप करने की ज़रूरत नहीं है। अब तक बनाए गए हर फ़ॉर्मूले में पहले से सटीक मिलान उपयोग हुआ है, क्योंकि यह XLOOKUP() का डिफ़ॉल्ट व्यवहार है।

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6)

Excel A2 में मौजूद सटीक मान को खोजता है, और तभी परिणाम लौटाता है जब उसे वही सटीक मिलान कहीं लुकअप रेंज में मिल जाए।

यदि आप इसे स्पष्ट रूप से लिखना चाहें, तो चौथे आर्ग्युमेंट के रूप में 0 जोड़ दें:

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6, , 0)

XLOOKUP() सटीक मिलान लौटाता है। चित्र: लेखक द्वारा।

0 से पहले खाली स्थान दिख रहा है? यह if_not_found को स्किप करता है, क्योंकि हम उसे यहाँ ख़ाली छोड़ रहे हैं, और सीधे match_mode पर जाता है। ज़्यादातर समय आप इस आर्ग्युमेंट को बिल्कुल छोड़ सकते हैं और Excel को अपने-आप सटीक मिलान पर रहने दें।

अनुमानी मिलान

अब मान लीजिए आपके डेटा में हर संभावित लुकअप के लिए मान मौजूद नहीं है। जैसे एक कमीशन स्ट्रक्चर जहाँ सेल्स प्रतिनिधि अलग-अलग रेट कमाते हैं, इस पर निर्भर कि उनकी कुल बिक्री किस रेंज में आती है। आपके पास हर डॉलर राशि की पंक्ति नहीं होगी, बस हर रेंज की शुरुआती सीमा होगी।

यहीं अनुमानी मिलान मदद करता है। 

match_mode को -1 सेट करें, और XLOOKUP() वह सबसे बड़ा मान ढूँढता है जो आपकी लुकअप वैल्यू से छोटा या उसके बराबर हो।

=XLOOKUP(C2, $A$2:$A$5, $B$2:$B$5, , -1)

मान लीजिए आपकी लुकअप रेंज में $0, $5,000, और $10,000 के सेल्स थ्रेशहोल्ड हैं, जिनसे अलग-अलग कमीशन रेट जुड़े हैं। 

$7,500 बिक्री वाले प्रतिनिधि का कोई पंक्ति से सटीक मेल नहीं होगा। match_mode -1 होने पर, XLOOKUP() $5,000 ढूँढेगा—वह सबसे बड़ा थ्रेशहोल्ड जो $7,500 से अधिक नहीं है—और उस पंक्ति का कमीशन रेट लौटाएगा।

इसके बजाय match_mode को 1 सेट करें, तो XLOOKUP() तर्क उलट देता है। यह वह सबसे छोटा मान ढूँढता है जो आपकी लुकअप वैल्यू से बड़ा या उसके बराबर हो। यह उन चीज़ों में सहायक है जैसे शिपिंग वज़न ब्रैकेट, जहाँ आप नीचे की बजाय ऊपर वाले अगले टियर तक राउंड अप करते हैं।

XLOOKUP() अनुमानी मिलान लौटाता है। चित्र: लेखक द्वारा।

ध्यान दें: अनुमानी मिलान यह मानकर चलता है कि आपकी लुकअप रेंज सॉर्टेड है—-1 के लिए आरोही, 1 के लिए अवरोही। अगर यह सही तरह से सॉर्टेड नहीं है, तो XLOOKUP() बिना एरर दिए गलत परिणाम लौटा सकता है, जो और भी बुरा है। इसलिए, इस पर भरोसा करने से पहले हमेशा अपना सॉर्ट ऑर्डर दोबारा जाँच लें।

XLOOKUP() के साथ बाएँ तरफ़ मान कैसे खोजें

VLOOKUP() आपकी रेंज के सबसे बाएँ कॉलम में ही खोजता है और उसके दाईं ओर के कॉलम से मान लौटाता है; यह उल्टी दिशा में नहीं जा सकता। इसका तीसरा आर्ग्युमेंट कोई रेंज नहीं, बल्कि बाएँ किनारे से गिना जाने वाला कॉलम इंडेक्स होता है। 

इसे "एक कॉलम बाएँ जाओ" कहने का कोई तरीका नहीं है, तो अगर आवश्यक मान आपके खोज कॉलम के बाएँ है, तो आपको डेटा पुनर्व्यवस्थित करना पड़ता है या लुकअप को काम कराने के लिए एक हेल्पर कॉलम जोड़ना पड़ता है।

XLOOKUP() पर यह पाबंदी नहीं है, क्योंकि आप इसमें खोज और रिटर्न के लिए अलग-अलग रेंज देते हैं।

हमारी प्रोडक्ट सूची लें: Product ID कॉलम A में है, Product Name कॉलम B में। 

मान लीजिए आपके पास प्रोडक्ट का नाम है और आप उसका ID खोजना चाहते हैं; यह VLOOKUP() के उलट है।

C9 में Bluetooth Speaker दर्ज करें (एक नाम जो सूची में मौजूद है)। E2 में यह दर्ज करें:

=XLOOKUP(C9, $B$2:$B$6, $A$2:$A$6)

XLOOKUP() कॉलम B में "Bluetooth Speaker" खोजता है, उसे पंक्ति 5 में पाता है, और कॉलम A से "P104" लौटाता है—भले ही कॉलम A, कॉलम B के बाएँ है। 

XLOOKUP() बाईं ओर के मान खोजता है, VLOOKUP() के विपरीत। चित्र: लेखक द्वारा।

XLOOKUP() के साथ कई कॉलम कैसे लौटाएँ

XLOOKUP() डायनेमिक ऐरे नामक सुविधा का उपयोग करके, एक ही फ़ॉर्मूले में एक साथ कई कॉलम लौटा सकता है। ऐसा करने के लिए, return_array को एक कॉलम की जगह किसी रेंज की ओर इंगित करें, और XLOOKUP() पूरे मिलते-जुलते रो को जितने सेल चाहिए उनमें "स्पिल" कर देता है। 

आइए इसे अपनी प्रोडक्ट सूची पर आज़माएँ। 

C9 में, मैं एक Product ID टाइप करता/करती हूँ, उदाहरण के लिए "P104"। E2 में मैं दर्ज करता/करती हूँ:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6)

ध्यान दें कि यहाँ return_array B2:D6 है, जो तीन कॉलम चौड़ा है—Product Name, Category, और Price। एंटर दबाते ही, XLOOKUP() कॉलम A में "P104" ढूँढता है, फिर Bluetooth Speaker, Electronics, और $45 एक साथ लौटाता है, जो E9, F9, और G9 में स्पिल हो जाते हैं।

XLOOKUP() एक फ़ॉर्मूला से कई कॉलम लौटाता है। चित्र: लेखक द्वारा।

इसे ही स्पिल रेंज कहते हैं। E9 में असली फ़ॉर्मूला है, और F9 तथा G9 अपने-आप भरते हैं—उनमें अपने अलग फ़ॉर्मूले नहीं होते। 

F9 पर क्लिक करें, तो फ़ॉर्मूला बार में मूल फ़ॉर्मूले का धुंधला-सा रूप दिखेगा, न कि कुछ ऐसा जो वहीं टाइप किया गया हो। जब तक E9 का स्पिल सक्रिय है, F9 में टाइप करने की कोशिश करें, तो Excel स्पिल एरर देगा। 

हम इसे कॉमन एरर्स सेक्शन में ठीक करने का तरीका कवर करेंगे।

XLOOKUP() में Missing Values को कैसे संभालें

डिफ़ॉल्ट रूप से, XLOOKUP() तब #N/A एरर देता है जब उसे कोई मिलान नहीं मिलता। if_not_found आर्ग्युमेंट इसे ठीक कर सकता है, ताकि डिफ़ॉल्ट एरर की जगह वह वही दिखाए जो हम चाहते हैं।  

मान लीजिए C9 में मैं ऐसा Product ID टाइप करता/करती हूँ जो मेरे डेटा में मौजूद नहीं है, जैसे "P999"।

एक कस्टम संदेश लौटाना

मैं G2 में यह दर्ज करता/करती हूँ:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "Not Found")

क्योंकि "P999" कॉलम A में मौजूद नहीं है, XLOOKUP() एरर को पूरी तरह स्किप कर देता है और "Not Found" लौटाता है। इस वाक्यांश को अपनी ज़रूरत के मुताबिक बदलें, जैसे "Check Product ID" या "No Match"।

डिफ़ॉल्ट एरर की जगह कस्टम संदेश दिखाएँ। चित्र: लेखक द्वारा।

खाली सेल लौटाना

कभी-कभी आप बिना टेक्स्ट वाला बिल्कुल खाली सेल चाहते हैं। इसके लिए, दो उद्धरण चिह्नों का उपयोग करें जिनके बीच कुछ न हो, इस तरह:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "")

जब आप यह फ़ॉर्मूला किसी रिपोर्ट में उपयोग कर रहे हों, जहाँ "Not Found" जैसा अटपटा टेक्स्ट वास्तविक मानों वाले कॉलम के पास बेतरतीब दिखेगा, तब यह उपयोगी है।

डिफ़ॉल्ट एरर की जगह खाली सेल दिखाएँ। चित्र: लेखक द्वारा।

इसे वास्तविक एरर रहने देना

यदि आप if_not_found को पूरी तरह छोड़ दें, तो XLOOKUP() वापस #N/A पर आ जाता है। यह हमेशा बुरा नहीं है। अगर आप अपने लिए फ़ॉर्मूला बना रहे हैं और टाइपो या मिसिंग एंट्री को तुरंत पकड़ना चाहते हैं, तो #N/A दिखना किसी चुपचाप खाली सेल से ज़्यादा मददगार है जो समस्या छिपा दे।

ध्यान दें: if_not_found सिर्फ़ मिसिंग मैच को पकड़ता है। यदि आपका lookup_array और return_array अलग आकार के हैं, उदाहरण के लिए, तो XLOOKUP() फिर भी #VALUE! एरर देगा, और if_not_found उसे नहीं रोकेगा। इस ख़ास मामले को हम नीचे कॉमन एरर्स सेक्शन में कवर करेंगे।

XLOOKUP() की उन्नत विशेषताएँ

आइए XLOOKUP() की उन्नत विशेषताएँ देखते हैं।

नीचे से ऊपर तक खोजें

search_mode को -1 पर सेट करें, और XLOOKUP() आपकी रेंज को ऊपर की बजाय नीचे से ऊपर की ओर स्कैन करेगा। इसका उपयोग तब करें जब आपके डेटा में डुप्लिकेट मान हों और आप पहला नहीं, बल्कि सबसे हाल का एंट्री चाहते हों।

मान लीजिए मेरे डेटासेट में तीन डुप्लिकेट Customer IDs हैं। सेल E2 में, मैं डिफ़ॉल्ट फ़ॉर्मूला दर्ज करता/करती हूँ जो ऊपर-से-नीचे दृष्टिकोण अपनाता है: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6)

यह तालिका की शुरुआत से गणना करता है और पहले मिलान से परिणाम दिखाता है। 

लेकिन यदि मैं नीचे से खोजना चाहूँ, तो यह फ़ॉर्मूला उपयोग करूँगा/करूँगी: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, -1)

इस फ़ॉर्मूले में, -1 Excel से कहता है कि डेटासेट के नीचे से खोज शुरू करे। और यह 340 लौटाएगा, क्योंकि नीचे से पहला मिलान वही है।  

XLOOKUP() नीचे से ऊपर डेटा स्कैन करता है। चित्र: लेखक द्वारा।

बाइनरी सर्च मोड

search_mode को 2 पर सेट करें, और XLOOKUP() एक-एक पंक्ति जाँचने से बाइनरी सर्च पर स्विच कर जाएगा। 

यह ऐसे काम करता है: 

• पहले आपकी रेंज के बीच वाले आइटम को देखता है। 

• यदि वह मिलान नहीं है, तो वह आधा हिस्सा हटा देता है जहाँ मान हो ही नहीं सकता, फिर बचे हुए के बीच में देखता है। 

• वह ऐसा करता रहता है, हर बार डेटा को आधा करता हुआ, जब तक मिलान न मिल जाए। 

• search_mode को -2 पर सेट करें, तो तर्क वही रहता है, बस शुरुआत आख़िरी आइटम से होती है।

यहाँ E2 में दर्ज करें:

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, 2)

यह फ़ॉर्मूला इसके बजाय 200 लौटाता है, जो पूरी तरह अलग पंक्ति है।

बाइनरी सर्च मोड गलत परिणाम देता है। चित्र: लेखक द्वारा।

क्योंकि बाइनरी सर्च तभी काम करता है जब आपका lookup_array सॉर्टेड हो। चूँकि हमारा Customer ID कॉलम सॉर्टेड नहीं है—C100, C101, C100, C102, C100—तो बाइनरी सर्च गलत आधा फेंक देता है और गलत मिलान दे देता है। 

अब देखें कि जब डेटा वास्तव में सॉर्टेड हो तो क्या होता है। 

उसी लॉग को Customer ID के अनुसार आरोही क्रम में सॉर्ट करें (तीनों C100 शुरुआत में), और वही फ़ॉर्मूला फिर चलाएँ। 

बाइनरी सर्च मोड सॉर्टेड डेटा के साथ काम करता है। चित्र: लेखक द्वारा।

इस बार, E2 में 120 लौटता है, जो सामान्य खोज जैसा ही परिणाम है। फर्क यही है। बाइनरी सर्च सिर्फ़ वही डेटा स्किप करता है जिसे वह सुरक्षित रूप से नकार सकता है—और यह तभी मान्य है जब डेटा पहले से सॉर्टेड हो। 

नेस्टेड XLOOKUP() फ़ॉर्मूले

जब किसी लुकअप का परिणाम किसी तय रेंज की बजाय दूसरे लुकअप में फ़ीड होना हो, तब एक XLOOKUP() को दूसरे के अंदर नेस्ट करें।

मान लीजिए आप मूल्य ढूँढना चाहते हैं, पर किस कॉलम से उसे निकालना है यह किसी अलग लुकअप—जैसे ग्राहक का प्राइसिंग टियर—पर निर्भर करता है।

=XLOOKUP(C9, $A$2:$A$6, XLOOKUP(D9, $F$2:$F$4, $G$2:$I$4))

इस फ़ॉर्मूले में, अंदर वाला XLOOKUP() पहले D9 के आधार पर यह तय करता है कि किस कॉलम रेंज से खींचना है, फिर बाहरी XLOOKUP() उस परिणाम का उपयोग अपने return_array के रूप में करता है। 

नेस्टेड XLOOKUP()। चित्र: लेखक द्वारा।

XLOOKUP() बनाम VLOOKUP()

हमने विस्तार से देखा कि XLOOKUP() कैसे काम करता है। यहाँ इसकी तुलना VLOOKUP() से है।

ध्यान देने लायक दो बातें हैं: 

• VLOOKUP() डिफ़ॉल्ट रूप से अनुमानी मिलान करता है। अगर आप चौथा आर्ग्युमेंट भूल जाएँ, तो यह एरर नहीं दिखाएगा; यह चुपचाप गलत मान लौटा देगा।

• यदि आप VLOOKUP() शीट में कोई कॉलम जोड़ते हैं, तो फ़ॉर्मूले चुपचाप गलत मान खींचना शुरू कर सकते हैं। XLOOKUP() कॉलम गिनने के बजाय सीधे रेंज को संदर्भित करके इससे बचता है।

इस सबका मतलब यह नहीं कि VLOOKUP() बेमानी हो गया है। यह सरल, स्थिर शीट्स के लिए अब भी ठीक काम करता है। लेकिन आगे बनने वाली किसी भी चीज़ के लिए, XLOOKUP() सुरक्षित डिफ़ॉल्ट है।

XLOOKUP() बनाम INDEX() और MATCH()

XLOOKUP() आने से पहले, INDEX() और MATCH() साथ में VLOOKUP() की सीमाएँ पार करने का मानक तरीका थे। इसमें एक की जगह दो फ़ंक्शन लगते हैं। 

• MATCH() आपकी लुकअप वैल्यू की स्थिति ढूँढता है। 

• INDEX() फिर उस स्थिति का उपयोग करके जहाँ आप इशारा करें, वहाँ से परिणाम निकालता है: बाएँ या दाएँ—फर्क नहीं पड़ता।

=INDEX(D2:D6, MATCH(C9, A2:A6, 0))

इसे उस XLOOKUP() संस्करण से तुलना करें जिसे हमने पूरे लेख में उपयोग किया है:

=XLOOKUP(C9, A2:A6, D2:D6)

XLOOKUP() केवल Microsoft 365 और Excel 2021 से आगे में काम करता है, जैसा हमने पहले कवर किया। यदि आपकी वर्कबुक Excel 2019 या 2016 में खुलनी है, या आप पुराने संस्करण वाले लोगों के साथ शेयर कर रहे हैं, तो INDEX() और MATCH() हर जगह काम करते हैं।

एक छोटा-सा अपवाद भी है। 

INDEX() और MATCH() "स्थिति ढूँढने" और "मान लौटाने" को अलग करते हैं, जो कुछ जटिल नेस्टेड फ़ॉर्मूलों में मदद करता है। यह कम ही आता है, तो ज़्यादातर पाठकों को इसकी ज़रूरत नहीं पड़ेगी।

इन दो मामलों के बाहर, नई वर्कबुक के लिए XLOOKUP() ही बेहतर विकल्प है। 

आम XLOOKUP() एरर और उन्हें कैसे ठीक करें

यहाँ उन समस्याओं का त्वरित संदर्भ है जिनका सामना आपको सबसे अधिक होने की संभावना है, और हर एक को कैसे ठीक करें।

#N/A एरर

जब XLOOKUP() को मिलान नहीं मिलता, तो यह इसका डिफ़ॉल्ट उत्तर है। मुझे यह पहले तब मिला जब मेरा lookup_value और lookup_array मेल में नहीं थे, और कॉलम A में XLOOKUP() के पास मिलान करने को कुछ था ही नहीं।

इसे ठीक करने के लिए: दोबारा जाँचें कि आपका lookup_value वास्तव में आपके lookup_array में मौजूद है, और वही स्पेलिंग व फ़ॉर्मेटिंग है। यदि मिसिंग मैच सामान्य और अपेक्षित हैं, तो if_not_found आर्ग्युमेंट का उपयोग करें ताकि #N/A की जगह कुछ उपयोगी दिखे, जैसे "Not Found" या खाली सेल, जैसा हमने ऊपर कवर किया। 

गलत लुकअप रेंज

यह तब होता है जब आपका lookup_array और return_array सही तरह से लाइन अप नहीं होते: गलत पंक्तियाँ, गलत कॉलम, या आकार में न मिलने वाली रेंज। 

आप किस Excel या Sheets संस्करण का उपयोग कर रहे हैं, इस पर निर्भर करते हुए, यह #VALUE! एरर के रूप में दिख सकता है, या कभी-कभी #N/A के रूप में जिसके साथ अलग संदेश वास्तविक समस्या समझाता है। #N/A देखकर यह न समझ लें कि यह मिसिंग मैच है। 

इसे ठीक करने के लिए: सुनिश्चित करें कि lookup_array और return_array समान संख्या की पंक्तियों या कॉलम को कवर करते हों। यदि lookup_array A2:A5 है, तो return_array को भी पंक्ति 2 से 5 तक ही फैलना चाहिए, न कि 4 से 5 तक या कोई और असंगत रेंज।

डेटा टाइप मिसमैच

VLOOKUP() एरर दे सकता है क्योंकि उसे संख्या की उम्मीद थी पर टेक्स्ट मिला। XLOOKUP() को भी ऐसा ही मुद्दा आ सकता है। यदि आपका lookup_value एक संख्या है, पर lookup_array में वही मान टेक्स्ट के रूप में स्टोर हैं, या उल्टा, तो XLOOKUP() को मिलान नहीं मिलेगा—भले ही स्क्रीन पर मान एक जैसे दिखें।

इसे ठीक करने के लिए: जाँचें कि आपका lookup_value और lookup_array एक ही डेटा टाइप का उपयोग कर रहे हों। टेक्स्ट के रूप में स्टोर की गई संख्याएँ आमतौर पर सेल में बाएँ-तरफ़ संरेखित दिखती हैं, दाएँ की बजाय। टेक्स्ट को संख्या में बदलने के लिए अपने lookup_value को VALUE() में लपेटें, या उल्टा करने के लिए TEXT() का उपयोग करें—यह इस पर निर्भर करेगा कि मिसमैच किस दिशा में है।

स्पिल एरर

यह तब होता है जब आपका return_array एक से अधिक कॉलम कवर करता है, लेकिन जिन सेल्स की XLOOKUP() को ज़रूरत है वे खाली नहीं हैं। Excel को आपके फ़ॉर्मूला सेल के बगल में परिणाम स्पिल करने के लिए खाली जगह चाहिए। अगर वहाँ पहले से कुछ है, तो पूरा फ़ॉर्मूला फेल हो जाएगा।

इसे ठीक करने के लिए: उन सेल्स को साफ़ करें जहाँ परिणाम जाने हैं, फिर अपना फ़ॉर्मूला दर्ज करें। यदि आपको उन सेल्स में कुछ चाहिए, तो अपना XLOOKUP() फ़ॉर्मूला कहीं और रखें जहाँ उसके आसपास खाली जगह हो।

अंतिम विचार 

अब आप एकल मान, कई कॉलम, मिसिंग डेटा, और दो रेंज में नेस्टेड लुकअप्स तक के लिए XLOOKUP() का उपयोग करना जानते हैं। यह काफी कुछ कवर हो गया।

आगे से, किसी भी नई वर्कबुक के लिए XLOOKUP() को अपना डिफ़ॉल्ट बनाएँ। यह VLOOKUP() से अधिक लचीला है, INDEX/MATCH() से पढ़ने में आसान है, और हर फ़ॉर्मूला को IFERROR() में लपेटने से बचाता है।

यदि आप और गहराई में जाना चाहें, तो INDEX() और MATCH() को अलग से सीखना भी फायदेमंद है।