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

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

तभी मुझे INDEX MATCH() मिला। फ़ॉर्मूला सही करने में कुछ कोशिशें लगीं, पर जैसे ही समझ आया कि यह ज़रूरी संख्याएँ कितनी आसानी से ढूँढ कर खींच लाता है, यह मेरी दिनचर्या का हिस्सा बन गया। सिर्फ दो फ़ंक्शंस से, मैं बिलकुल वही डेटा निकाल लेता था जिसकी ज़रूरत थी, चाहे वह कितनी भी शीट्स में बिखरा हो।

इस लेख में, मैं समझाऊँगा कि आप भी यही काम INDEX() और MATCH() फ़ंक्शंस से कैसे कर सकते हैं। Excel में सीखने को हमेशा और होता है। यदि आप शुरुआती हैं, तो हमारा Introduction to Excel कोर्स ज़रूर देखें। अगर आपके पास अनुभव है, तो हमारा Advanced Excel Functions कोर्स आज़माएँ।

INDEX MATCH का एक रिफ्रेशर

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

INDEX() फ़ंक्शन किसी निर्दिष्ट रेंज के भीतर स्थिति के आधार पर किसी सेल का मान लौटाता है। इसका सिंटैक्स इस प्रकार है:

=INDEX(array, row_num, [column_num])संचालित द्वारा

यहाँ:

• array वह सेल रेंज है, जिससे आप मान प्राप्त करना चाहते हैं।

• row_num array में वह पंक्ति संख्या है, जिससे मान लौटाना है।

• column_num (वैकल्पिक) array में वह स्तंभ संख्या है, जिससे मान लौटाना है।

MATCH() फ़ंक्शन किसी रेंज में किसी मान की सापेक्ष स्थिति बताता है। इसका सिंटैक्स है:

=MATCH(lookup_value, lookup_array, [match_type])संचालित द्वारा

यहाँ:

• lookup_value वह मान है जिसे आप ढूँढना चाहते हैं।

• lookup_array वह रेंज है जहाँ फ़ंक्शन मान खोजता है।

match_type वैकल्पिक है। 1 (डिफ़ॉल्ट) वह मान ढूँढता है जो lookup_value से कम या उसके बराबर हो (array आरोही क्रम में सॉर्ट होना चाहिए)। 0 सटीक मिलान ढूँढता है (array को सॉर्ट करने की आवश्यकता नहीं)। -1 वह सबसे छोटा मान ढूँढता है जो lookup_value से बड़ा या उसके बराबर हो (array अवरोही क्रम में सॉर्ट होना चाहिए)।

INDEX() को MATCH() के साथ कैसे जोड़ें

यदि हम MATCH() को INDEX() के भीतर नेस्ट करें, तो एक डायनेमिक लुकअप बनता है। इसे एक उदाहरण से समझें: मान लीजिए आप David Wilson की स्थिति डेटासेट में ढूँढना चाहते हैं। INDEX() में रो नंबर हार्डकोड करने के बजाय, उसे तय करने के लिए MATCH() का उपयोग करें:

=INDEX(C2:C6, MATCH("David Wilson", A2:A6, 0))संचालित द्वारा

ऊपर के फ़ॉर्मूले में MATCH("David Wilson", A2:A6, 0) 4 लौटाता है, जो पंक्ति की स्थिति है। और INDEX(C2:C6, 4) रेंज C2:C6 की चौथी पंक्ति से मान निकालता है, जो है Seattle.

INDEX() को MATCH() के साथ जोड़ें। चित्र: लेखक द्वारा।

इसे और अधिक डायनेमिक बनाने के लिए, हार्ड-कोडेड David Wilson को किसी सेल रेफ़रेंस से बदल दें। इस तरह, फ़ॉर्मूला D4 के मान के अनुसार अपने-आप समायोजित हो जाएगा:

=INDEX(C2:C6,MATCH(D4,A2:A6,0))संचालित द्वारा

INDEX MATCH में हार्ड-कोडेड मान बदलें। चित्र: लेखक द्वारा।

INDEX MATCH बनाम VLOOKUP()

अब जबकि आप समझ गए हैं कि INDEX() और MATCH() अलग-अलग कैसे काम करते हैं और साथ आने पर लुकअप कितना डायनेमिक हो जाता है, देखें कि INDEX MATCH VLOOKUP() से बेहतर विकल्प क्यों है।

• VLOOKUP() में लुकअप कॉलम बाएँ होना ज़रूरी होता है, जबकि INDEX MATCH आपको किसी भी कॉलम से, उसकी स्थिति की परवाह किए बिना, डेटा निकालने देता है।

• INDEX MATCH केवल आवश्यक रेंज को प्रोसेस करता है, जबकि VLOOKUP() पूरी टेबल स्कैन करता है। 

• यदि कॉलम जोड़ें या हटाएँ, तो VLOOKUP() वाले फ़ॉर्मूले टूट सकते हैं क्योंकि वे स्थिर कॉलम इंडेक्स पर निर्भर होते हैं। दूसरी ओर, INDEX MATCH डायनेमिक रेंजेस को रेफ़रेंस करता है, जिससे डेटा संरचना बदलने पर भी फ़ॉर्मूले सुरक्षित रहते हैं।

• INDEX MATCH के साथ हमें कॉलम नंबर मैन्युअली गिनने की ज़रूरत नहीं होती। लुकअप कॉलम और रिटर्न कॉलम बता दें, काम हो जाता है। 

कई मानदंडों के साथ INDEX MATCH

मुझे अक्सर ऐसे डेटासेट्स पर काम करना पड़ता है जिनमें डुप्लिकेट एंट्री होती हैं, और उनमें मान ढूँढना बहुत मुश्किल होता है। लेकिन अब मैं INDEX MATCH उपयोग करता हूँ क्योंकि यह ऐसे मामलों को बहुत आसानी से संभालता है, अन्य स्टैंडर्ड लुकअप फ़ॉर्मूलों के विपरीत। आइए, चरण-दर-चरण देखें कि मैं इसे कैसे उपयोग करता हूँ।

कई मानदंडों के लिए डेटा तैयार करें

पहले अपना डेटासेट बनाएँ और सुनिश्चित करें कि वह स्पष्ट हेडर के साथ एक सुव्यवस्थित टेबल में हो। हर पंक्ति एक यूनिक रिकॉर्ड दर्शाए और हर कॉलम किसी विशिष्ट डेटा एट्रिब्यूट को रखे।

उदाहरण के लिए, यहाँ एक सैंपल डेटासेट है:

INDEX MATCH मल्टीपल क्राइटेरिया के लिए डेटासेट। चित्र: लेखक द्वारा।

कई मानदंडों के लिए फ़ॉर्मूला लिखें

जब डेटा सही तरह से व्यवस्थित हो जाए, तो फ़ॉर्मूला लिखें। INDEX MATCH फ़ॉर्मूला कई शर्तें पूरी करने वाली पंक्ति पहचान कर दूसरे कॉलम से मान निकालता है। यह MATCH() में लॉजिकल टेस्ट्स जोड़कर और उसे INDEX() के भीतर एम्बेड करके किया जाता है।

इसका बेसिक सिंटैक्स इस प्रकार है: 

{=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2) * (…), 0))}संचालित द्वारा

यहाँ: 

• Return_range वह रेंज है जिससे मान लौटेगा।

• Criteria1, Criteria2, … वे शर्तें हैं जिन्हें पूरा होना है।

• Range1, Range2, … उन शर्तों के अनुरूप रेंजेस हैं।

अब जब डेटा सेटअप हो गया है, तो आइए, इस प्रश्न के दो तरीकों को करीब से देखें: कई मानदंडों के साथ INDEX MATCH कैसे उपयोग करें।

जटिल मानदंडों के लिए हेल्पर कॉलम का उपयोग करें

यदि आपके डेटासेट में कई शर्तें हैं, तो प्रक्रिया सरल करने के लिए हेल्पर कॉलम का इस्तेमाल करें। यह सभी शर्तों को एक ही कॉलम में जोड़ देगा, जिससे लुकअप आसान हो जाएगा। उदाहरण के लिए, मैं उसी डेटासेट में एक हेल्पर कॉलम बना रहा हूँ जिसमें First Name और Salary कॉलम्स को जोड़ रहा हूँ:

=A2&B2संचालित द्वारा

हेल्पर कॉलम बनाएँ। चित्र: लेखक द्वारा।

यह हेल्पर कॉलम मेरे INDEX MATCH फ़ॉर्मूला को सरल बनाता है। कई शर्तों के साथ जटिल ऐरे फ़ॉर्मूला लिखने के बजाय, मैंने अपने फ़ॉर्मूले में हेल्पर कॉलम का रेफ़रेंस देकर सरल तरीका अपनाया:

=INDEX(D2:D11, MATCH("AliceHR", E2:E11, 0))संचालित द्वारा

हेल्पर कॉलम के साथ INDEX MATCH। चित्र: लेखक द्वारा।

ऐरे फ़ॉर्मूला से कई मानदंड जोड़ें

यदि आप हेल्पर कॉलम पसंद नहीं करते, तो वही परिणाम पाने के लिए ऐरे फ़ॉर्मूला का उपयोग करें। यह आपको सीधे MATCH() के भीतर कई शर्तों का मूल्यांकन करने देता है। उदाहरण के लिए, मैं Alice की Salary को HR विभाग में इस तरह ढूँढता हूँ:

चरण 1: लॉजिकल शर्तों के साथ MATCH() लिखें:

 MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0)संचालित द्वारा

इस फ़ॉर्मूले में 1 सुनिश्चित करता है कि MATCH() उन पंक्तियों को ढूँढे जहाँ सभी शर्तें सही हों। (F4=A2:A11) जाँचता है कि F4 का मान रेंज A2:A11 में किसी मान से मेल खाता है या नहीं। (F5=B2:B11) जाँचता है कि F5 का मान रेंज B2:B11 में किसी मान से मेल खाता है या नहीं। * ऑपरेटर AND लॉजिक की तरह काम करता है, जिससे सभी शर्तें पूरी होना आवश्यक हो जाता है।

चरण 2: इस MATCH() को INDEX() के भीतर रैप करें:

=INDEX(D2:D11, MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0))संचालित द्वारा

चरण 3: फ़ॉर्मूला फाइनल करें। यदि आप Excel के पुराने संस्करण का उपयोग कर रहे हैं, तो इसे ऐरे फ़ॉर्मूला बनाने के लिए Ctrl+Shift+Enter दबाएँ। नए संस्करणों में, सिर्फ Enter दबाएँ।

कई मानदंडों के साथ Array INDEX MATCH। चित्र: लेखक द्वारा।

कई मानदंडों के साथ INDEX MATCH के उन्नत उपयोग

आप INDEX MATCH से और भी बहुत कुछ कर सकते हैं। आइए देखें कैसे: 

Named ranges और dynamic ranges के साथ INDEX MATCH का उपयोग

मैं Excel में named ranges का उपयोग अर्थपूर्ण नाम, जैसे results या totalSales देने के लिए करता हूँ, ताकि A1:A10 जैसी स्टैंडर्ड रेफ़रेंस के बजाय समझने में आसानी हो। इससे अलग-अलग शीट्स में फ़ॉर्मूलों को मैनेज करना आसान हो जाता है। 

किसी सेल रेंज को नाम देने के लिए, सेल्स चुनें और Ctrl + F3 (Windows) या Cmd + F3 (Mac) दबाएँ और Name Manager खोलें। फिर New पर क्लिक करें, नाम दर्ज करें, और OK पर क्लिक करें।

रेंज को नाम दें। चित्र: लेखक द्वारा।

Named range और dynamic range में फर्क सिर्फ इतना है कि named range निश्चित सेल समूह को संदर्भित करता है, जबकि dynamic range में डेटा जोड़ने-घटाने पर वह अपने-आप समायोजित हो जाता है।

डायनेमिक रेंज सेट करने के लिए, सेल्स चुनें। Formulas टैब में Name Manager पर क्लिक करें या Ctrl + F3 दबाएँ और Excel Name Manager खोलें। फिर New पर क्लिक करें। New Name डायलॉग बॉक्स खुलेगा। अब Name फ़ील्ड में मनचाहा नाम दर्ज करें। फिर Refers to फ़ील्ड में डायनेमिक रेंज का फ़ॉर्मूला डालें। 

डायनेमिक रेंज सेट करें। चित्र: लेखक द्वारा। 

अब एक उदाहरण देखें: मैंने दो डायनेमिक और एक स्टैटिक रेंज परिभाषित कीं:

• total_amount: =$F$2:INDEX($F:$F, COUNTA($F:$F))

• items_list: =$A$2:INDEX($A:$A, COUNTA($A:$A))

• lookup_value: =$I$3

अब, मैं इन रेंजेस को INDEX MATCH फ़ॉर्मूला में उपयोग करता हूँ:

=INDEX(total_amount,MATCH(lookup_value,items_list,0))संचालित द्वारा

और आप देख सकते हैं कि स्पष्ट नामों के साथ फ़ॉर्मूला समझना कहीं आसान हो जाता है।

INDEX MATCH के साथ डायनेमिक और named ranges का उपयोग करें। चित्र: लेखक द्वारा।

जटिल लुकअप के लिए Nested INDEX MATCH

बेसिक काम के अलावा, आप जटिल लुकअप संभालने के लिए nested INDEX MATCH भी उपयोग कर सकते हैं। उदाहरण के लिए, मेरे पास अलग-अलग क्षेत्रों में उत्पाद श्रेणी के अनुसार बिक्री दिखाने वाला डेटासेट है। 

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

मैं East में फर्नीचर की बिक्री खोजना चाहता हूँ। लेकिन इसके लिए मुझे प्रोडक्ट केटेगरी (रो) और रीजन (कॉलम) दोनों का मिलान करना होगा, जो बेसिक INDEX MATCH से नहीं होता। इसलिए मैं यहाँ निम्नलिखित nested INDEX MATCH फ़ॉर्मूला उपयोग करता हूँ:

=INDEX(B2:D4, MATCH(D6, A2:A4, 0), MATCH(D7, B1:D1, 0))संचालित द्वारा

यह ऐसे काम करता है: INDEX() रेंज B2:D4 से मान खींचता है, पर उसे ठीक-ठीक देखने के लिए पंक्ति और स्तंभ संख्या चाहिए। इसलिए पहला MATCH(D6, A2:A4, 0) पंक्ति संख्या निकालता है। यदि D6 में Furniture है, तो यह कॉलम A2:A4 में ढूँढकर इसे दूसरी पंक्ति में पाता है।

अगला MATCH(D7, B1:D1, 0) स्तंभ संख्या तय करता है। यदि D7 में East है, तो यह B1:D1 में देखता है और इसे दूसरे कॉलम में पाता है।

एक बार जब INDEX() को पंक्ति और स्तंभ मिल जाते हैं, तो यह आउटपुट दिखा देता है। हमारे मामले में, Furniture की East रीजन में बिक्री 450 है।

Nested INDEX MATCH का उपयोग करें। चित्र: लेखक द्वारा।

मैं पंक्तियों और स्तंभों में मैन्युअल रूप से खोजने के बजाय यह फ़ॉर्मूला उपयोग करता हूँ, क्योंकि यह सब कुछ सटीकता से संभालता है। 

सामान्य चुनौतियाँ और समस्या-समाधान सुझाव

जब मैंने INDEX MATCH का उपयोग शुरू किया, तो कई चुनौतियाँ आईं, और मैं नहीं चाहता कि आपको वैसी झुंझलाहट हो। इसलिए मैं आम समस्याएँ और उनके समाधान साझा कर रहा हूँ।

INDEX MATCH फ़ॉर्मूलों में त्रुटियों को संभालें

#N/A और #VALUE! जैसी त्रुटियाँ शुरू में परेशान कर सकती हैं, लेकिन इन्हें ठीक करना काफ़ी आसान है। देखें कि समस्या किस वजह से है और उसे कैसे सरलता से सुलझाएँ।

#N/A त्रुटि तब आती है जब MATCH() वांछित मान नहीं ढूँढ पाता। कारण यह हो सकता है कि लुकअप मान सर्च array में मौजूद नहीं, या डेटा में छिपे स्पेसेज़ हों। उदाहरण के लिए, मैंने एक बार Employee Names खींचते समय गलत कॉलम रेफ़र कर दिया:

=INDEX(B2:B6,MATCH(E3,C2:C6,0))संचालित द्वारा

INDEX MATCH में #N/A त्रुटि। चित्र: लेखक द्वारा।

ऐसी दिक्कतें ठीक करने के लिए, सुनिश्चित करें कि लुकअप मान array में मौजूद है और छुपे स्पेसेज़ हटाने के लिए TRIM() का इस्तेमाल करें:

=TRIM(INDEX(B2:B6,MATCH(E3,A2:A6,0)))संचालित द्वारा

INDEX MATCH में #N/A त्रुटि ठीक। चित्र: लेखक द्वारा।

#VALUE! त्रुटि तब दिखती है जब फ़ॉर्मूला ऐरे फ़ॉर्मूला के रूप में सेट नहीं होता। उदाहरण के लिए, यदि मैं MATCH() में एक से अधिक रेंज शामिल करूँ, तो Excel इसे ऐरे फ़ॉर्मूला मानता है। लेकिन यदि यह सही तरह से सेट न हो, तो Excel #VALUE! त्रुटि फेंक देगा।

=INDEX(C2:C6,MATCH(D4&E4,A2:A6&B2:B6,0))संचालित द्वारा

INDEX MATCH में #Value त्रुटि। चित्र: लेखक द्वारा।

इसे सुलझाने के लिए, फ़ॉर्मूला दर्ज करने के बाद Ctrl + Shift + Enter दबाएँ। तब Excel फ़ॉर्मूले को कर्ली ब्रेसेज़ {} में लपेट देगा, जो दर्शाता है कि अब यह ऐरे फ़ॉर्मूला है। ब्रेसेज़ मैन्युअली टाइप न करें, वरना फ़ॉर्मूला टूट जाएगा।

INDEX MATCH में #Value त्रुटि ठीक। चित्र: लेखक द्वारा।

बड़े डेटासेट्स के साथ प्रदर्शन को बेहतर बनाएँ

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

• लुकअप रेंज सीमित करें: रेंज को सिर्फ़ ज़रूरत भर तक सीमित रखें। उदाहरण के लिए, A:A के बजाय A1:A100 उपयोग करें, ताकि गणना समय घटे।

• हेल्पर कॉलम का उपयोग करें: जटिल मानदंड पहले से कैल्क्युलेट कर लें। इससे ऐरे फ़ॉर्मूलों का कम्प्यूटेशनल लोड कम होगा।

• मैन्युअल कैलकुलेशन मोड सक्षम करें: Excel को मैन्युअल कैलकुलेशन मोड में स्विच करें ताकि लगातार रिकैल्क्युलेशन न हो। बदलावों के बाद F9 दबाकर फ़ॉर्मूले अपडेट करें।

• वोलाटाइल फ़ंक्शंस से बचें: NOW(), RAND(), और TODAY() जैसे वोलाटाइल फ़ंक्शंस का INDEX MATCH के साथ न्यूनतम उपयोग करें। ये हर अपडेट पर रिकैल्क्युलेशन ट्रिगर करते हैं।

अंतिम विचार 

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

और गहराई से सीखने के लिए हमारा Advanced Excel Functions कोर्स देखें ताकि और अधिक शक्तिशाली टूल्स में महारत हासिल कर सकें। लेकिन यदि आप Excel में डेटा विश्लेषण पर संपूर्ण विशेषज्ञता बनाना चाहते हैं, तो मैं हमारा Data Analysis in Excel कोर्स सुझाऊँगा। यह डेटा तैयारी से लेकर विज़ुअलाइज़ेशन तक सब कवर करता है।