Excel में XLOOKUP() का उपयोग कई मानदंडों के साथ कैसे करें

Excel में XLOOKUP() वर्तमान में लुकअप फ़ंक्शनों (LOOKUP(), VLOOKUP(), HLOOKUP(), और XLOOKUP()) के समूह का नवीनतम फ़ंक्शन है, जो कई फायदे, विस्तारित कार्यक्षमता, और अधिक लचीलापन प्रदान करता है।

इस ट्यूटोरियल में, हम पहले चर्चा करेंगे कि XLOOKUP() Excel फ़ंक्शन किस काम आता है और यह पुराने लुकअप फ़ंक्शनों से कैसे बेहतर है; उसके बाद, हम इसकी बुनियादी सिंटैक्स पर नज़र डालेंगे, और फिर मुद्दे पर आएँगे—एकाधिक मानदंडों के साथ XLOOKUP() फ़ंक्शन का उपयोग।

यदि आपको Excel की बुनियादी बातें सीखनी हैं, तो शुरुआती-अनुकूल Introduction to Excel कोर्स से शुरुआत करें।

सारांश

• XLOOKUP() Excel का सबसे नया लुकअप फ़ंक्शन है। यह VLOOKUP(), HLOOKUP(), और LOOKUP() से अधिक लचीला और तेज़ है, और यह बहुदिशात्मक खोज तथा आंशिक मिलान का समर्थन करता है।

• कई मानदंडों के साथ खोजने के लिए आपके पास दो मुख्य तरीके हैं: संयोजन (लुकअप वैल्यूज़ को & से जोड़ना, जैसे =XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11)) या बूलियन अभिव्यक्तियाँ (शर्तों का गुणन करना, जैसे =XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11))।

• संयोजन सरल और अधिक पठनीय है, लेकिन बूलियन अभिव्यक्तियाँ आपको अधिक लचीलापन देती हैं।

• वैकल्पिक पैरामीटर फ़ंक्शन को और बढ़ाते हैं: [if_not_found] मिलान न मिलने पर कस्टम टेक्स्ट लौटाता है, [match_mode] अनुमानित या वाइल्डकार्ड मैचिंग सक्षम करता है, और [search_mode] आपको खोज दिशा उलटने देता है।

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

XLOOKUP() फ़ंक्शन किसी रेंज या ऐरे में खोज करता है और पहले मिलान के अनुरूप आइटम लौटाता है। अगर कोई मिलान न मिले, तो विशिष्ट मैच प्रकार दिए जाने पर XLOOKUP() एक अनुमानित मिलान लौटा सकता है। कई पहलुओं में, XLOOKUP() फ़ंक्शन Excel के अपने पूर्ववर्तियों (VLOOKUP(), HLOOKUP(), और LOOKUP()) से आगे निकल जाता है।

विशेष रूप से, यह सक्षम बनाता है:

• क्षैतिज और ऊर्ध्वाधर—दोनों तरह से, और किसी भी दिशा में डेटा की खोज
• कई खोज मानदंड
• डिफ़ॉल्ट रूप से सटीक मिलान के साथ अनुमानित मिलान
• आंशिक मिलान
• एकाधिक कॉलम और पंक्तियाँ लौटाना
• मिलान न मिलने पर अनुकूलित टेक्स्ट लौटाना।

इसके अलावा, XLOOKUP() फ़ंक्शन Excel के पुराने लुकअप फ़ंक्शनों की तुलना में तेज़ प्रदर्शन करता है, जो बड़ी मात्रा के डेटा में खोज करते समय मायने रखता है।

एक शर्त के साथ XLOOKUP() का उपयोग कैसे करें

आइए XLOOKUP() फ़ंक्शन की बुनियादी सिंटैक्स का संक्षिप्त अवलोकन करें:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) संचालित द्वारा

जहाँ आवश्यक पैरामीटर हैं:

• lookup_value—वह मान जिसकी खोज करनी है

• lookup_array—वह ऐरे या रेंज जिसमें खोज करनी है

• return_array—वह ऐरे या रेंज जिससे मान लौटाना है

और वैकल्पिक पैरामीटर हैं:

• [if_not_found]—मिलान न मिलने पर लौटाया जाने वाला दिया गया टेक्स्ट

• [match_mode]—मिलान का प्रकार (सटीक या अनुमानित), और अगर मिलान न मिले तो क्या लौटाया जाए।

• [search_mode]—उपयोग करने का खोज मोड (सीधे या रिवर्स सर्च, लुकअप ऐरे पर बाइनरी सर्च जो आरोही या अवरोही क्रम में सॉर्टेड हो)।

हम इन वैकल्पिक पैरामीटरों का उपयोग करते हुए उदाहरण इस ट्यूटोरियल में आगे देखेंगे।

Excel में अन्य सहायक फ़ंक्शनों और फ़ॉर्मूलों को सीखने या दोहराने के लिए, आप The 15 Basic Excel Formulas Everyone Needs to Know ट्यूटोरियल और Excel Formulas Cheat Sheet देख सकते हैं।

कई मानदंडों के साथ XLOOKUP() का उपयोग कैसे करें

जैसा कि हम इसकी बुनियादी सिंटैक्स से देखते हैं, XLOOKUP() Excel फ़ंक्शन डिफ़ॉल्ट रूप से किसी निर्दिष्ट वैरिएबल के एक ही लुकअप मान के साथ काम करने के लिए बनाया गया है।

हालाँकि, हम इस फ़ंक्शन को एक साथ कई वैरिएबल्स पर उपयोग करने के लिए अनुकूलित कर सकते हैं, जहाँ प्रत्येक वैरिएबल में अलग-अलग लुकअप मान की खोज हो। दूसरे शब्दों में, हम XLOOKUP() के साथ कई मानदंड लागू करके खोज कर सकते हैं। ऐसा करने के लिए दो मुख्य तरीके हैं: संयोजन और बूलियन अभिव्यक्तियाँ—और हम दोनों पर चर्चा करेंगे।

तकनीकी विवरणों में जाने से पहले, आइए उस Excel टेबल को देखें जिस पर हम काम करेंगे। यह टेबल 10 बिल्लियों की जानकारी देती है, जिनमें उनके नाम, रंग, और आयु शामिल हैं:

अपने प्रयोगों में, हम XLOOKUP() का उपयोग करके किसी बिल्ली की तीन विशेषताओं में से एक को बाकी दो के आधार पर खोजेंगे।

यह ठीक है कि हमारी टेबल बहुत सरल है, और हर मामले में हम बिना किसी फ़ंक्शन के भी जो चाहिए वह ढूँढ सकते हैं। यहाँ, हमें कई मानदंडों के साथ XLOOKUP() का उपयोग करने के मूल सिद्धांत समझने हैं। इन सिद्धांतों को जानकर, हम उन्हें आसानी से अधिक जटिल वास्तविक जीवन परिदृश्यों में विस्तारित कर सकते हैं, जैसे जब हमारे पास दो से अधिक खोज मानदंड हों या कुछ विशिष्ट आवश्यकताएँ पूरी करनी हों।

संयोजन का उपयोग करके कई मानदंडों के साथ XLOOKUP()

संयोजन का तरीका काफ़ी सीधा है: हमें संबंधित लुकअप मान और ऐरे को आपस में जोड़ना होता है। देखें, यह कैसे काम करता है।

मान लीजिए, हम Nala नाम की सफेद-और-धूसर (white-and-grey) बिल्ली की उम्र ढूँढना चाहते हैं। सुविधा के लिए, हमने ये दो मानदंड उसी शीट पर जोड़ दिए हैं जहाँ हमारी टेबल है:

इस स्थिति में, XLOOKUP() का फ़ॉर्मूला इस प्रकार होगा:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11)संचालित द्वारा

यह 2.5 लौटाएगा, जो पहली सफेद-और-धूसर Nala (मेरी बिल्ली 😺) की उम्र है। याद रखें कि XLOOKUP() पहले मिलान के अनुरूप आइटम लौटाता है।

उपरोक्त फ़ॉर्मूला के पहले दो घटक—जिनमें ampersand है—क्या दर्शाते हैं, इसे समझने के लिए हम उन्हें अलग-अलग Excel सेल में चला सकते हैं:

=B2:B11&C2:C11संचालित द्वारा

परिणाम NalaWhite और Grey है।

=B2:B11&C2:C11संचालित द्वारा

परिणाम इस प्रकार है:

हाँ, यह इतना ही सरल है: हमने बस संबंधित लुकअप मानों और लुकअप ऐरे को आपस में जोड़ दिया, जैसा कि हमने पहले XLOOKUP() की बुनियादी सिंटैक्स में देखा था।

बूलियन अभिव्यक्तियों का उपयोग करके कई मानदंडों के साथ XLOOKUP()

अब, वही काम करते हैं—Nala नाम की सफेद-और-धूसर बिल्ली की उम्र ढूँढना—लेकिन इस बार दूसरे तरीके से: बूलियन अभिव्यक्तियाँ। इस स्थिति में, XLOOKUP() का फ़ॉर्मूला इस प्रकार होगा:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)संचालित द्वारा

अपेक्षा के अनुसार, यह भी 2.5 लौटाएगा, जो पहली सफेद-और-धूसर Nala की उम्र है।

आइए ऊपर दिए फ़ॉर्मूला के घटकों की पड़ताल करें। यहाँ 1 का अर्थ TRUE है, यानी हम लुकअप ऐरे—जो फ़ॉर्मूला के दूसरे घटक (B2:B11=G2)*(C2:C11=G3) से प्रदर्शित है—में TRUE मान खोज रहे हैं।

यह घटक, बदले में, दो गुणक रखता है, जिनमें से प्रत्येक एक विशेष शर्त की जाँच करता है: पहला यह जाँचता है कि नाम वही है जिसे हम ढूँढ रहे हैं, जबकि दूसरा यह जाँचता है कि रंग वही है जिसे हम ढूँढ रहे हैं। आइए प्रत्येक गुणक को अलग Excel सेल में चलाएँ:

=B2:B11=G2संचालित द्वारा

परिणाम इस प्रकार है:

=C2:C11=G3संचालित द्वारा

परिणाम इस प्रकार है:

अब, फ़ॉर्मूला के पूरे दूसरे घटक को चलाएँ:

=(B2:B11=G2)*(C2:C11=G3)संचालित द्वारा

परिणाम इस प्रकार है:

हालाँकि हमें ऊपर के परिणाम में 1 के दो मान दिखाई देते हैं (अर्थात दो TRUE मान), XLOOKUP() फ़ंक्शन पहले मिलान के अनुरूप आइटम लौटाता है।

कई मानदंडों के लिए XLOOKUP() चलाते समय बूलियन अभिव्यक्तियों का तरीका, संयोजन आधारित तरीके की तुलना में अधिक जटिल और कम सहज दिखता है। अगर हम दो से अधिक खोज मानदंड उपयोग करें तो यह और भी कठिन हो जाएगा। फिर हम हमेशा संयोजन क्यों न इस्तेमाल करें?

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

FALSE मान की जाँच

ऊपर के फ़ॉर्मूला में, हम बूलियन अभिव्यक्ति के TRUE होने की जाँच कर रहे थे, और 1 का मान पास कर रहे थे:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)संचालित द्वारा

कुछ स्थितियों में, हमें यह जाँचना पड़ सकता है कि बूलियन अभिव्यक्ति वास्तव में FALSE है। उदाहरण के लिए, हम अपनी टेबल में पहली ऐसी बिल्ली की उम्र ढूँढना चाहते हैं जो सफेद-और-धूसर Nala न हो। हमें अपना फ़ॉर्मूला इस प्रकार बदलना चाहिए:

=XLOOKUP(0, (B2:B11=G2)*(C2:C11=G3), D2:D11)संचालित द्वारा

परिणाम 1 होगा, जो पहली ऐसी बिल्ली की उम्र है जो सफेद-और-धूसर Nala नहीं है (ठीक-ठीक कहें, वह त्रिरंगी Nala है, पर सफेद-और-धूसर नहीं)।

लॉजिकल ऑपरेटरों का उपयोग

बूलियन अभिव्यक्तियों के साथ, हम केवल समानता की जाँच तक सीमित नहीं हैं। मान लीजिए, हम Nala नाम की ऐसी बिल्ली का रंग ढूँढना चाहते हैं जिसकी उम्र 2 वर्ष से कम हो:

इस स्थिति में, XLOOKUP() का फ़ॉर्मूला इस प्रकार होगा:

=XLOOKUP(1, (B2:B11=G2)*(D2:D11<G3), C2:C11)संचालित द्वारा

परिणाम Tricolor होगा।

कम-से-कम एक मानदंड पूरा करना

अब तक हम सभी दिए गए मानदंडों के अनुरूपता की जाँच कर रहे थे। अन्य परिदृश्यों में, हमें कम-से-कम एक मानदंड पूरा करना हो सकता है।

यह कैसे काम करता है, इसे देखने के लिए, अपनी प्रारंभिक कार्य पर वापस लौटते हैं—Nala नाम की सफेद-और-धूसर बिल्ली की उम्र ढूँढना:

लेकिन इस बार, हम ऐसी बिल्ली की उम्र ढूँढना चाहते हैं जो या तो Nala कहलाती हो या सफेद-और-धूसर हो। इस स्थिति में XLOOKUP() का फ़ॉर्मूला होगा:

=XLOOKUP(1, (B2:B11=G2)+(C2:C11=G3), D2:D11)संचालित द्वारा

यह 1 लौटाता है, जो त्रिरंगी Nala की उम्र है।

याद दिला दें, जब हम ऐसी बिल्ली की उम्र खोज रहे थे जो दोनों—Nala कहलाती हो और सफेद-और-धूसर हो—तो फ़ॉर्मूला इस प्रकार था:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)संचालित द्वारा

* को + से बदलने से ही अंतर आया।

यदि आप Excel की पूरी क्षमता जानना चाहते हैं, तो व्यापक, अभ्यास-उन्मुख, और सुव्यवस्थित Excel Fundamentals स्किल पाथ पर विचार करें।

कई मानदंडों के साथ XLOOKUP() के लिए वैकल्पिक पैरामीटर

जैसे एकल मानदंड के साथ XLOOKUP() का सामान्य उपयोग, वैसे ही हम कई मानदंडों पर इसे चलाते समय इस फ़ंक्शन के अतिरिक्त विकल्पों का उपयोग कर सकते हैं। यही वह जगह है जहाँ [if_not_found], [match_mode], और [search_mode] जैसे वैकल्पिक पैरामीटर काम आते हैं।

आइए, XLOOKUP() और कई मानदंडों के साथ इन प्रत्येक पैरामीटरों के उपयोग के उदाहरणों पर जल्दी से नज़र डालें। सरलता के लिए, हम हर उदाहरण में संयोजन तरीके को लागू करेंगे।

मिलान न मिलने पर दिया गया टेक्स्ट लौटाना

यहाँ हम काली Nala की उम्र खोज रहे हैं—जो हमारी टेबल में मौजूद नहीं है:

इस स्थिति में, हमने XLOOKUP() फ़ॉर्मूला में वैकल्पिक पैरामीटर [if_not_found] जोड़ा है, जिसे हमने वह टेक्स्ट सौंपा है जो मिलान न मिलने पर लौटाया जाना चाहिए:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, "No cat is found")संचालित द्वारा

वास्तव में, फ़ंक्शन ने दिया गया टेक्स्ट लौटाया: No cat is found। यदि हम कोई टेक्स्ट न देते, तो फ़ंक्शन #N/A लौटाता।

अनुमानित मिलान लौटाना

मान लीजिए, हम त्रिरंगी Nala की उम्र ढूँढ रहे हैं, पर हमें पक्का नहीं कि उसके रंग को टेबल में "Tricolor" लिखा गया है या "Tricolour"। यहाँ हमें वाइल्डकार्ड-आधारित अनुमानित मिलान का उपयोग करना होगा और XLOOKUP() फ़ंक्शन को [match_mode] आर्गुमेंट 2 पास करना होगा।

ऊपर की टेबल में, हमने तारक (*) का उपयोग किया है, जो किसी भी संख्या के अक्षरों (यहाँ तक कि 0) का प्रतिनिधित्व करता है। अन्य वाइल्डकार्ड Microsoft Office की दस्तावेज़ीकरण में मिल सकते हैं।

यहाँ XLOOKUP() फ़ॉर्मूला होगा:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , 2)संचालित द्वारा

और यह 1 लौटाएगा, जो हमारी टेबल में पहली (और एकमात्र) त्रिरंगी Nala की उम्र है।

ध्यान दें कि ऊपर के फ़ॉर्मूला में 2 आर्गुमेंट का अर्थ है कि हम अनुमानित मिलान में रुचि रखते हैं, न कि "Tricolo*r" रंग वाली Nala की उम्र खोजने में। हमारे मामले में, तारक ने "Tricolor" शब्द को कैप्चर किया, पर यह "Tricolour" या, मान लीजिए, "Tricolooor" पर भी वही काम करेगा।

रिवर्स सर्च करना

चलो वही अभ्यास करते हैं जो शुरुआत में किया था—Nala नाम की सफेद-और-धूसर बिल्ली की उम्र ढूँढना—लेकिन इस बार, हम अपनी खोज अंतिम आइटम से शुरू करेंगे।

ऐसा करने के लिए, हमें XLOOKUP() फ़ंक्शन में वैकल्पिक पैरामीटर [search_mode] को -1 पर सेट करना होगा, जैसा नीचे है:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , , -1)संचालित द्वारा

फ़ंक्शन 7 लौटाता है, जो लुकअप ऐरे के अंत से पहले सफेद-और-धूसर Nala की उम्र है।

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

Excel के पुराने संस्करणों में, कई मानदंडों के साथ XLOOKUP() जैसी कार्यक्षमता प्राप्त करने के लिए हमें INDEX() और MATCH() फ़ंक्शनों को संयोजित करना पड़ता है। दोनों फ़ंक्शनों की सिंटैक्स में जाए बिना, देखें कि हम पुराने तरीके INDEX() + MATCH() और नए XLOOKUP() फ़ंक्शन का उपयोग करके पहली सफेद-और-धूसर Nala की उम्र कैसे ढूँढ सकते हैं (सेल रेफ़रेंसेस के लिए, हम पिछली टेबल देख सकते हैं):

=INDEX(D2:D11, MATCH(1, (G2=B2:B11)*(G3=C2:C11), 0))
=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)संचालित द्वारा

हालाँकि आर्गुमेंट कुछ हद तक समान दिखते हैं, आइए पुराने तरीके पर XLOOKUP() फ़ंक्शन चलाने के मुख्य फ़ायदों का उल्लेख करें:

• इस ऑपरेशन के लिए एक ही फ़ंक्शन काफ़ी है—फ़ंक्शनों को संयोजित करना आवश्यक नहीं।

• जहाँ संभव हो, संयोजन तरीके को लागू करने की संभावना।

• वैकल्पिक पैरामीटरों का उपयोग करने की संभावना (MATCH() फ़ंक्शन में ये नहीं हैं)।