Excel में XMATCH ट्यूटोरियल: परिचय, सिंटैक्स, और व्यावहारिक उपयोग

डेटा विश्लेषण के दौरान कई स्थितियाँ आती हैं जब आपको किसी दिए गए मान या पैटर्न के आधार पर किसी ऐरे या सेल रेंज में किसी आइटम की सापेक्ष स्थिति खोजनी होती है। 

Excel में पैटर्न-मिलान संचालन करने का सबसे प्रभावी तरीका XMATCH फ़ंक्शन है। सरल होने के बावजूद शक्तिशाली, XMATCH में कई सुविधाएँ हैं जो सटीक और लचीला मिलान सक्षम करती हैं। 

इस ट्यूटोरियल में, हम Excel XMATCH फ़ंक्शन की मूल बातें कवर करेंगे, इसके सिंटैक्स और विभिन्न पैरामीटरों का विश्लेषण करेंगे, और इसकी क्षमताओं को दर्शाने के लिए व्यावहारिक उदाहरण देंगे। हम XMATCH की तुलना MATCH से भी करेंगे, जो समान कार्यों के लिए प्रयुक्त एक और लोकप्रिय फ़ंक्शन है। अंत में, हम XMATCH का उपयोग करते समय आने वाली आम समस्याओं पर नज़र डालेंगे और सही व कुशल उपयोग सुनिश्चित करने के लिए सर्वोत्तम प्रक्रियाएँ बताएँगे।

यदि आप Excel सीखने के सफर पर हैं और और अधिक जानना चाहते हैं, तो हमारे Excel Fundamentals स्किल ट्रैक देखें और सभी ज़रूरी बातें सीखें। 

XMATCH क्या है?

XMATCH मिलान कार्यों के लिए एक लचीला और भरोसेमंद फ़ंक्शन है। XMATCH() आपको वर्टिकल और हॉरिज़ॉन्टल लुकअप से लेकर सटीक, अनुमानित और आंशिक मेल तक—हर तरह के मिलान संचालन करने देता है। 

जो लोग लंबे समय से Excel का उपयोग कर रहे हैं, वे संभवतः MATCH फ़ंक्शन से परिचित होंगे। XMATCH को पेश करने वाले Excel अपडेट से पहले, मेल कार्यों के लिए MATCH ही प्रमुख विकल्प था। आप इनके बारे में हमारे Excel Lookup Functions ट्यूटोरियल में सीख सकते हैं। 

हालाँकि, जैसा कि हम आगे देखेंगे, कुछ परिदृश्यों में MATCH कम पड़ सकता है। इस संदर्भ में, XMATCH को पारंपरिक MATCH फ़ंक्शन का उन्नत और लंबे समय से अपेक्षित अपग्रेड माना जा सकता है, जो किसी भी दिशा में खोज और अनेक मैच प्रकार प्रदान करता है, जिससे इसका उपयोग व्यापक मामलों के लिए आसान और उपयुक्त हो जाता है।

ध्यान दें कि यह फ़ंक्शन केवल Excel for Microsoft 365 और Excel 2021 में उपलब्ध है। Excel के पुराने संस्करणों के उपयोगकर्ताओं के लिए, MATCH ही एकमात्र उपलब्ध फ़ंक्शन है।

अब आइए XMATCH के सिंटैक्स और पैरामीटर देखें। 

XMATCH सिंटैक्स और पैरामीटर

XMATCH फ़ंक्शन का मूल सिंटैक्स इस प्रकार है:

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

इस फ़ंक्शन में दो अनिवार्य आर्ग्युमेंट होते हैं (यानी lookup_value और lookup_array) और दो वैकल्पिक आर्ग्युमेंट (match_mode और search_mode) होते हैं, जो अधिक सटीक और लचीले संचालन के लिए क्षमताएँ प्रदान करते हैं।

नीचे आप चारों पैरामीटरों का विवरण देख सकते हैं:

• Lookup_value: वह मान जिसे खोजना है।
• Lookup_array: वह ऐरे या सूची जिसमें खोज करनी है। यह वर्टिकल या हॉरिज़ॉन्टल लुकअप ऐरे हो सकता है।
• Match_mode: मिलान के प्रकार का चुनाव करने देता है। इसके चार मोड हैं, जिनमें शामिल हैं:
• 0: सटीक मिलान (डिफ़ॉल्ट मोड)
• -1: सटीक मिलान या अगला सबसे छोटा आइटम
• 1: सटीक मिलान या अगला सबसे बड़ा आइटम
• 2: वाइल्डकार्ड मिलान, जैसे * और ?।
• Search_mode: खोज मोड निर्दिष्ट करने देता है, जिसमें खोज की दिशा और एल्गोरिद्म शामिल हैं। इसके चार संभावित मान हैं:
• 1: पहले से आखिरी तक खोज (डिफ़ॉल्ट मान)
• -1: आखिरी से पहले तक खोज (रिवर्स सर्च)।
• 2: बाइनरी सर्च (आरोही)। खोज यह मानकर की जाती है कि लुकअप ऐरे आरोही क्रम में सॉर्टेड है, अन्यथा परिणाम अमान्य होंगे।
• -2: बाइनरी सर्च (अवरोही)। खोज यह मानकर की जाती है कि लुकअप ऐरे अवरोही क्रम में सॉर्टेड है, अन्यथा परिणाम अमान्य होंगे।

मैच मोड: सटीकता और लचीलापन

XMATCH वैकल्पिक match_mode पैरामीटर के साथ आता है जो आपको मिलान के प्रकार का चुनाव करने देता है। ये विकल्प बहुत उपयोगी हैं, क्योंकि ये सटीक और अनुमानित—दोनों तरह के मिलान करने के तरीके उपलब्ध कराते हैं। आइए उपलब्ध मैच मोड को विस्तार से समझते हैं:

सटीक मिलान

सटीक मिलान (match_mode = 0) XMATCH का डिफ़ॉल्ट मैच मोड है। इसका अर्थ है कि Excel लुकअप ऐरे में लुकअप मान का सटीक मिलान ढूँढेगा, अन्यथा NA त्रुटि लौटाएगा। जब शुद्धता और सटीकता अहम हों—जैसे किसी डेटाबेस में विशिष्ट प्रविष्टियाँ ढूँढना—तब सटीक मिलान आवश्यक होता है।

यहाँ एक बुनियादी उदाहरण है कि DataCamp Course Catalog में उपलब्ध तकनीकों की सूची में “Excel” की सटीक स्थिति XMATCH से कैसे खोजें। हम D3 में लुकअप मान और (A3:A14) में लुकअप ऐरे देते हैं, और Excel ठीक-ठीक स्थिति 6 लौटाता है। 

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

कई बार आपको लचीला मिलान करना पड़ सकता है, या तो इसलिए कि आपको सटीक लुकअप मान पता नहीं है, या इसलिए कि आप अनुमानित परिणाम ढूँढ रहे हैं। 

सौभाग्य से, XMATCH दो मैच मोड देता है, जो सटीक या अगला-छोटा, और सटीक या अगला-बड़ा मान खोजने देते हैं। 

निम्न उदाहरण में, हम 15 के लुकअप मान के सबसे नज़दीकी कोर्स-काउंट्स ढूँढने के लिए अनुमानित मिलान का उपयोग करते हैं। 

जब अगला सबसे छोटा (match_mode = -1) उपयोग करते हैं, तो Excel स्थिति 6 लौटाता है, क्योंकि 12 कोर्स सूची में सबसे नज़दीकी छोटा मान है। अगले सबसे बड़े के मामले में, Excel 5 लौटाता है, क्योंकि 16 कोर्स सबसे नज़दीकी बड़ा मान है।

हम यह भी दिखाते हैं कि सटीक मिलान में, जब Excel को सटीक मान नहीं मिलता, तो क्या होता है।

वाइल्डकार्ड और रेगुलर एक्सप्रेशन मिलान

XMATCH की एक नई सुविधा यह है कि आप match_mode को 2 पर सेट करके बुनियादी वाइल्डकार्ड, और 3 पर सेट करके उन्नत रेगुलर एक्सप्रेशन का उपयोग कर सकते हैं। ये मैच मोड केवल टेक्स्ट डेटा के साथ काम करते हैं।

XMATCH दो लोकप्रिय वाइल्डकार्ड उपयोग करने देता है:

• ?: किसी एकल अक्षर से मेल कराने के लिए।
• *: किसी भी अक्षर-श्रृंखला से मेल कराने के लिए।

नीचे आप regex मिलान के विभिन्न उदाहरण देख सकते हैं। पहले उदाहरण में, हम उन तकनीकों को देखते हैं जो E से शुरू होती हैं। दूसरे में, हम उन तकनीकों को ढूँढते हैं जिनके नाम में A आता है। अंत में, हम तीन अक्षरों वाली तकनीकों को खोजते हैं।

ध्यान दें कि XMATCH लुकअप ऐरे में हमेशा पहली उपस्थिति लौटाएगा, जब तक कि हम अलग से निर्दिष्ट न करें (अगला अनुभाग देखें)।

यदि आप अधिक जटिल रेगुलर एक्सप्रेशन उपयोग करना चाहते हैं, तो match_mode को 3 पर रखें। रेगुलर एक्सप्रेशन, जिसे प्रायः "regex" या "regexp" कहा जाता है, खोज पैटर्न परिभाषित करने का एक तरीका है, जिसका उपयोग टेक्स्ट खोजना, पार्स करना और/या बदलना जैसे अनेक कार्यों में होता है। 

पैटर्न मिलान के बारे में अधिक जानकारी के लिए हमारा Excel Regex Tutorial: रेगुलर एक्सप्रेशंस के साथ पैटर्न मिलान में महारत एक अच्छा शुरुआती बिंदु है।

यहाँ एक सरल उदाहरण है: हम ऐसे कोर्स की स्थिति खोजना चाहते हैं जिसका कोड शुरुआत में तीन अक्षर, अंत में तीन अक्षर, और बीच में दो संख्याएँ (31) शामिल करता है।

XMATCH सर्च मोड: दिशा और दक्षता

XMATCH एक नया पैरामीटर पेश करता है जो आपको खोज की दिशा नियंत्रित करने देता है। यह सुविधा तब उपयोगी है जब आप पहली के बजाय आखिरी उपस्थिति ढूँढने में अधिक रुचि रखते हैं—जैसे टाइम-सीरीज़ विश्लेषण के कुछ कार्यों में। 

आइए विभिन्न सर्च मोड को विस्तार से समझते हैं।

रिवर्स सर्च

रिवर्स सर्च करने के लिए, आप search_mode पैरामीटर को -1 पर सेट कर सकते हैं। उदाहरण के लिए, नीचे देखें कि उपयोगकर्ता Peter द्वारा पूरा किए गए पहले और आखिरी कोर्स को कैसे खोजें।

बाइनरी सर्च

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

XMATCH आपको आरोही (search_mode = 2) और अवरोही (search_mode = -2)—दोनों क्रम में सॉर्टेड ऐरे पर बाइनरी सर्च का लाभ लेने देता है। यदि लुकअप ऐरे सॉर्टेड नहीं है, तो फ़ंक्शन त्रुटि लौटाएगा।

XMATCH के व्यावहारिक उपयोग और उदाहरण

XMATCH बुनियादी से उन्नत तक—Excel के व्यापक कार्यों के लिए उपयुक्त है। इस अनुभाग में, हम इसके कुछ प्रभावशाली व्यावहारिक उपयोग कवर करेंगे।

INDEX/XMATCH के साथ द्वि-आयामी लुकअप

INDEX और XMATCH फ़ंक्शन मिलकर द्वि-आयामी लुकअप कर सकते हैं, यानी एक साथ पंक्तियों और स्तंभों में खोज। इन दोनों फ़ंक्शन को संयोजित करके हम डायनेमिक लुकअप बना सकते हैं—जैसे वित्तीय विश्लेषण में—जहाँ श्रेणियों के पार चर की तुलना आम है। 

उदाहरण के लिए, नीचे दी गई छवि में, हम INDEX और MATCH को जोड़कर Q2 के दौरान Excel कोर्स में नामांकित उपयोगकर्ताओं की संख्या निकालते हैं। यहाँ INDEX तीन आर्ग्युमेंट लेता है: वह ऐरे जहाँ मान खोजना है, पंक्ति संख्या, और स्तंभ संख्या। अंतिम दो आर्ग्युमेंट के मान कई XMATCH फ़ंक्शनों से मिलते हैं।

यदि हम तकनीक या क्वार्टर बदलना चाहें, तो बस XMATCH फ़ंक्शनों में लुकअप मान बदल दें, और INDEX का परिणाम उसी के अनुसार बदल जाएगा।

INDEX/XMATCH का कार्य INDEX/MATCH से बहुत मिलता-जुलता है। यदि आप इस शक्तिशाली जोड़ी के बारे में व्यावहारिक उदाहरणों सहित जानना चाहते हैं, तो हमारा Excel में INDEX MATCH Multiple Criteria कैसे करें ट्यूटोरियल देखें।

मल्टी-कॉलम लुकअप

XMATCH का एक और बेहतरीन उपयोग यह है कि कई मानदंडों को मिलाकर मल्टी-कॉलम लुकअप बनाए जाएँ। यह सहज नहीं लगता, क्योंकि XMATCH को काम करने के लिए एक लुकअप मान और एक लुकअप ऐरे चाहिए। लेकिन हम XMATCH फ़ॉर्मूला के भीतर बूलियन लॉजिक का उपयोग कर सकते हैं, जो शर्तों से मेल खाने वाली पंक्तियों को दर्शाने के लिए 0 और 1 वाला एक अस्थायी ऐरे बनाता है। 

मान लें, आप सूची में पहली ऐसी तकनीक खोजना चाहते हैं जिसके हर क्वार्टर में 1000 से कम नामांकित उपयोगकर्ता हों। आप प्रत्येक कॉलम के परिणामों को * प्रतीक से गुणा कर शर्तें जोड़ सकते हैं। Excel आंतरिक रूप से सभी 1s वाला ऐरे ढूँढ लेगा—हमारे मामले में Tableau—और इसलिए स्थिति 5 लौटेगी।

डायनेमिक ऐरे फिल्टरिंग

XMATCH, FILTER और ISNUMBER फ़ंक्शनों के साथ मिलकर डायनेमिक ऐरे फिल्टरिंग को बेहतर बना सकता है।

नीचे देखें यह कैसे काम करता है। हम इन फ़ंक्शनों का उपयोग उपयोगकर्ता के नाम के आधार पर उपयोगकर्ताओं द्वारा पूरे किए गए कोर्स की जानकारी को फ़िल्टर करने के लिए करेंगे। 

पहले, हम XMATCH का उपयोग करके तालिका में प्रत्येक उपयोगकर्ता की सापेक्ष स्थिति की गणना करते हैं, उस लुकअप ऐरे के आधार पर जिसमें वे नाम हैं जिनसे हम फ़िल्टर करेंगे। इससे हमें प्रत्येक नाम की सापेक्ष स्थिति वाली एक तालिका मिलती है, जिसमें उन उपयोगकर्ताओं के लिए null मान भी होते हैं जो फ़िल्टर सूची में शामिल नहीं हैं।

फिर, हम सापेक्ष स्थितियों और null मानों वाले कॉलम को ISNUMBER फ़ंक्शन का उपयोग करके TRUE या FALSE में बदलते हैं।

अंत में, हम FILTER का उपयोग करके वे सभी कॉलम चुनते हैं जिन्हें हम दिखाना चाहते हैं, और पंक्तियाँ फ़िल्टर करने के लिए TRUE और FALSE वाले कॉलम का उपयोग करते हैं।

ISNA के साथ null मान संभालना

जब XMATCH कोई मिलान नहीं ढूँढ पाता, तो यह NA त्रुटि लौटाता है। यद्यपि यह सूचनात्मक है, आप ISNA और IF फ़ंक्शनों का उपयोग करके परिणाम को अधिक पठनीय बना सकते हैं और गलत व्याख्याओं से बच सकते हैं।

मान लें हमारे पास Sam और Peter द्वारा पूरे किए गए कोर्स की तालिका है, और हम जानना चाहते हैं कि उनके बीच कौन से कोर्स सामान्य हैं। यदि हम सिर्फ XMATCH का उपयोग करें, तो Excel सामान्य कोर्स की सापेक्ष स्थिति लौटाएगा, अन्यथा null मान। यदि हम XMATCH को ISNA के आर्ग्युमेंट के रूप में पास करें, तो फ़ंक्शन इसके बजाय TRUE या FALSE लौटाएगा। फिर, IF फ़ंक्शन का उपयोग करके, हम इन बूलियन मानों को अन्य संदेशों से बदल सकते हैं।

बड़े डेटासेट के लिए बाइनरी सर्च

हम पहले ही उल्लेख कर चुके हैं कि XMATCH मिलान कार्यों के दौरान बाइनरी सर्च एल्गोरिद्म का उपयोग करने देता है। बड़े डेटासेट के साथ काम करते समय यह सुविधा गेम-चेंजर हो सकती है। बशर्ते कि लुकअप ऐरे सॉर्टेड हो, बाइनरी सर्च इंजन चुनना आपके मिलान संचालन को काफी तेज कर सकता है।

XMATCH बनाम MATCH: मुख्य अंतर

जैसा कि पहले बताया गया, XMATCH को पारंपरिक MATCH फ़ंक्शन का स्थान लेने के लिए डिज़ाइन किया गया है। दोनों का उद्देश्य समान है, लेकिन XMATCH नई क्षमताओं के साथ आता है, जो इसे MATCH से अधिक शक्तिशाली बनाती हैं। नीचे XMATCH और MATCH के बीच मुख्य अंतर सूचीबद्ध हैं:

आम गलतियाँ और समाधान

XMATCH के कई संभावित उपयोग हैं। फिर भी, इसका उपयोग करते समय आने वाली आम समस्याओं का उल्लेख जरूरी है। यहाँ कुछ सामान्य गलतियाँ और उन्हें सुलझाने के सुझाव दिए गए हैं।

• डेटा प्रकार की जाँच: गलत डेटा प्रकार वाली रेंज पर लागू करने पर XMATCH अप्रत्याशित व्यवहार कर सकता है। इसलिए, फ़ंक्शन लागू करने से पहले सुनिश्चित करें कि आपके कॉलम के डेटा प्रकार सही सेट हैं।
• रेंज में स्थिरता: यद्यपि XMATCH हॉरिज़ॉन्टल और वर्टिकल—दोनों लुकअप रेंज की अनुमति देता है, उन्हें मिलाना अप्रत्याशित समस्याएँ और प्रदर्शन मुद्दे पैदा कर सकता है। जहाँ संभव हो, एक ही प्रकार—हॉरिज़ॉन्टल या वर्टिकल—रेंज चुनें।
• मैच मोड का लाभ लें: XMATCH कई मैच मोड देता है। डिफ़ॉल्ट मोड सटीक मिलान है, लेकिन आप अनुमानित खोज और regex ऑपरेशन का उपयोग कर अपने लुकअप को बारीक-संतुलित कर सकते हैं।
• सॉर्टेड ऐरे के साथ बेहतर: कुल मिलाकर, XMATCH का प्रदर्शन सॉर्टेड ऐरे पर लागू करने पर हमेशा बेहतर होगा। यह डिफ़ॉल्ट फ़ंक्शन के लिए भी सही है, और बाइनरी सर्च उपयोग करना चाहें तो यह अनिवार्य पूर्वापेक्षा है। 
• N/A त्रुटियों को संभालना:  जैसा कि बताया गया, अगर आपके लुकअप में कोई मिलान नहीं मिलता, तो XMATCH NA त्रुटि देगा। इसे संभालने और पठनीयता बढ़ाने का सबसे अच्छा तरीका है परिणामों को IF और ISNA के संयोजन, या IFNA फ़ंक्शन को पास करना।

प्रदर्शन संबंधी विचार

Excel में लुकअप संचालन करने के लिए XMATCH अकेला फ़ंक्शन नहीं है। उदाहरण के लिए, आप VLOOKUP, HLOOKUP, या यहाँ तक कि MATCH जैसे पुराने फ़ंक्शन पर भी विचार कर सकते हैं। हालाँकि, खोज गति की बात करें, तो XMATCH निस्संदेह सबसे तेज़ है—खासकर जब आप इसे क्रमबद्ध ऐरे पर लागू करते हैं, जहाँ आप बाइनरी सर्च की शक्ति का भी लाभ उठा सकते हैं।

यह भी उल्लेखनीय है कि XMATCH को डायनेमिक ऐरे फ़ॉर्मूला के रूप में डिज़ाइन किया गया है, यानी यह इनपुट डेटा के आधार पर परिवर्तनीय आकार के ऐरे लौटा सकता है। बड़े डेटासेट या जटिल गणनाओं पर काम करते समय डायनेमिक ऐरे फ़ॉर्मूला विशेष रूप से उपयुक्त होते हैं। 

निष्कर्ष

अंत तक पहुँचने पर बधाई। XMATCH एक शक्तिशाली फ़ंक्शन है, और इसे आप अपनी Excel डेटा विश्लेषण में नज़रअंदाज़ नहीं कर सकते। अकेले या अन्य Excel फ़ंक्शनों के साथ मिलकर, XMATCH आपके लुकअप कार्यों को नए स्तर पर ले जाएगा। 

लेकिन Excel की दुनिया में जानने के लिए और भी बहुत कुछ है। Excel विशेषज्ञ बनने के लिए हमारे समर्पित कोर्स, ब्लॉग, और ट्यूटोरियल देखें:

• XLOOKUP() बनाम VLOOKUP(): Excel उपयोगकर्ताओं के लिए तुलना
• Data Manipulation in Excel चीट शीट 
• Excel | Master Advanced Techniques कोर्स
• Intermediate Power Query in Excel 
• Excel Shortcuts चीट शीट 
• Excel Formulas चीट शीट 
• Excel में INDEX MATCH Multiple Criteria कैसे करें 
• स्प्रेडशीट्स में INDEX-MATCH के साथ Data Wrangling 
• Excel Regex ट्यूटोरियल: रेगुलर एक्सप्रेशंस के साथ पैटर्न मिलान