एक्सेल में INDEX और MATCH फंक्शन का उपयोग कैसे करें

2024 लेखक: Abigail Brown | [email protected]. अंतिम बार संशोधित: 2023-12-17 06:48

क्या पता

इंडेक्स फ़ंक्शन का अकेले उपयोग किया जा सकता है, लेकिन इसके अंदर MATCH फ़ंक्शन को नेस्ट करने से एक उन्नत लुकअप बनता है।
यह नेस्टेड फ़ंक्शन VLOOKUP की तुलना में अधिक लचीला है और परिणाम तेजी से प्राप्त कर सकता है।

यह लेख बताता है कि एक्सेल 2019 और माइक्रोसॉफ्ट 365 सहित एक्सेल के सभी संस्करणों में INDEX और MATCH फ़ंक्शंस का एक साथ उपयोग कैसे करें।

इंडेक्स और मैच फंक्शन क्या हैं?

INDEX और MATCH एक्सेल लुकअप फंक्शन हैं। जबकि वे दो पूरी तरह से अलग कार्य हैं जिनका उपयोग स्वयं किया जा सकता है, उन्हें उन्नत सूत्र बनाने के लिए भी जोड़ा जा सकता है।

इंडेक्स फ़ंक्शन किसी विशेष चयन के भीतर से एक मान या किसी मान का संदर्भ देता है। उदाहरण के लिए, इसका उपयोग डेटा सेट की दूसरी पंक्ति में या पांचवीं पंक्ति और तीसरे कॉलम में मान खोजने के लिए किया जा सकता है।

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

इंडेक्स और मैच सिंटैक्स और तर्क

इस प्रकार दोनों कार्यों को एक्सेल को समझने के लिए उन्हें लिखने की आवश्यकता है:

=INDEX(सरणी, row_num, [column_num])

सरणी उन कक्षों की श्रेणी है जिनका उपयोग सूत्र करेगा। यह एक या अधिक पंक्तियाँ और स्तंभ हो सकते हैं, जैसे A1:D5। यह आवश्यक है।
row_num सरणी में वह पंक्ति है जिससे मान लौटाना है, जैसे कि 2 या 18. यह तब तक आवश्यक है जब तक column_num मौजूद न हो।
column_num एरे में वह कॉलम है जिससे 1 या 9 जैसा कोई मान लौटाना है। यह वैकल्पिक है।

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value वह मान है जिसे आप lookup_array में मिलाना चाहते हैं। यह एक संख्या, पाठ या तार्किक मान हो सकता है जिसे मैन्युअल रूप से टाइप किया गया हो या सेल संदर्भ के माध्यम से संदर्भित किया गया हो। यह आवश्यक है।
lookup_array देखने के लिए कक्षों की श्रेणी है। यह एकल पंक्ति या एकल स्तंभ हो सकता है, जैसे A2:D2 या G1:G45। यह आवश्यक है।
match_type -1, 0, या 1 हो सकता है। यह निर्दिष्ट करता है कि कैसे लुकअप_वेल्यू का लुकअप_एरे में मानों के साथ मिलान किया जाता है (नीचे देखें)। 1 डिफ़ॉल्ट मान है यदि इस तर्क को छोड़ दिया जाता है।

कौन सा मिलान प्रकार उपयोग करना है
मिलान प्रकार	यह क्या करता है	नियम	उदाहरण
1	सबसे बड़ा मान ढूँढता है जो लुकअप_वैल्यू से कम या उसके बराबर है।	लुकअप_अरे मानों को आरोही क्रम में रखा जाना चाहिए (जैसे, -2, -1, 0, 1, 2; या A-Z;, या FALSE, TRUE।	lookup_value 25 है लेकिन यह लुकअप_एरे से गायब है, इसलिए इसके बजाय अगली सबसे छोटी संख्या, जैसे 22, की स्थिति लौटा दी जाती है।
0	लुकअप_वैल्यू के बिल्कुल बराबर पहला मान ढूंढता है।	लुकअप_अरे मान किसी भी क्रम में हो सकते हैं।	lookup_value 25 है, इसलिए यह 25 की स्थिति लौटाता है।
-1	सबसे छोटा मान ढूँढता है जो लुकअप_वैल्यू से बड़ा या बराबर है।	लुकअप_अरे मानों को अवरोही क्रम में रखा जाना चाहिए (जैसे, 2, 1, 0, -1, -2)।	lookup_value 25 है लेकिन यह लुकअप_एरे से गायब है, इसलिए इसके बजाय अगली सबसे बड़ी संख्या, जैसे 34, की स्थिति लौटा दी जाती है।

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

उदाहरण INDEX और MATCH फ़ॉर्मूला

इससे पहले कि हम देखें कि INDEX और MATCH को एक सूत्र में कैसे संयोजित किया जाए, हमें यह समझने की आवश्यकता है कि ये फ़ंक्शन अपने आप कैसे काम करते हैं।

इंडेक्स उदाहरण

=इंडेक्स(A1:B2, 2, 2)

=INDEX(A1:B1, 1)

=INDEX(2:2, 1)=INDEX(B1:B2, 1)

इस पहले उदाहरण में, चार INDEX सूत्र हैं जिनका उपयोग हम विभिन्न मान प्राप्त करने के लिए कर सकते हैं:

=INDEX(A1:B2, 2, 2) दूसरे कॉलम और दूसरी पंक्ति में मान खोजने के लिए A1:B2 को देखता है, जो स्टेसी है।
=INDEX(A1:B1, 1) पहले कॉलम में मान खोजने के लिए A1:B1 को देखता है, जो जॉन है।
=INDEX(2:2, 1) पहले कॉलम में मान का पता लगाने के लिए दूसरी पंक्ति में सब कुछ देखता है, जो टिम है।
=INDEX(B1:B2, 1) पहली पंक्ति में मान का पता लगाने के लिए B1:B2 को देखता है, जो एमी है।

मिलान उदाहरण

=MATCH("Stacy", A2:D2, 0)

=MATCH(14, D1:D2)

=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)

यहां MATCH फंक्शन के चार आसान उदाहरण दिए गए हैं:

=MATCH("Stacy", A2:D2, 0) स्टेसी को A2:D2 श्रेणी में खोज रहा है और परिणाम के रूप में 3 लौटाता है।
=MATCH(14, D1:D2) D1:D2 श्रेणी में 14 खोज रहा है, लेकिन चूंकि यह तालिका में नहीं मिला है, इसलिए MATCH को अगला सबसे बड़ा मान मिलता है यह 14 से कम या बराबर है, जो इस मामले में 13 है, जो लुकअप_एरे की स्थिति 1 में है।
=MATCH(14, D1:D2, -1) इसके ऊपर के सूत्र के समान है, लेकिन चूंकि सरणी अवरोही क्रम में नहीं है जैसे -1 की आवश्यकता है, हमें एक त्रुटि मिलती है।
=MATCH(13, A1:D1, 0) शीट की पहली पंक्ति में 13 ढूंढ रहा है, जो 4 देता है क्योंकि यह इस सरणी में चौथा आइटम है।

इंडेक्स-मैच उदाहरण

यहां दो उदाहरण हैं जहां हम INDEX और MATCH को एक सूत्र में मिला सकते हैं:

तालिका में सेल संदर्भ खोजें

=इंडेक्स(बी2:बी5, मैच(एफ1,ए2:ए5))

यह उदाहरण INDEX सूत्र के भीतर MATCH सूत्र को नेस्ट कर रहा है। लक्ष्य आइटम नंबर का उपयोग करके आइटम के रंग की पहचान करना है।

यदि आप छवि को देखते हैं, तो आप "पृथक" पंक्तियों में देख सकते हैं कि सूत्र अपने आप कैसे लिखे जाएंगे, लेकिन चूंकि हम उन्हें नेस्ट कर रहे हैं, यही हो रहा है:

MATCH(F1, A2:A5) डेटा सेट A2:A5 में F1 मान (8795) की तलाश कर रहा है। यदि हम कॉलम को काउंट डाउन करते हैं, तो हम देख सकते हैं कि यह 2 है, इसलिए MATCH फंक्शन का अभी-अभी पता चला है।
इंडेक्स सरणी B2:B5 है क्योंकि हम अंततः उस कॉलम में मान की तलाश कर रहे हैं।
INDEX फ़ंक्शन को अब इस तरह फिर से लिखा जा सकता है क्योंकि MATCH को 2 मिला है: INDEX(B2:B5, 2, [column_num])।
चूंकि column_num वैकल्पिक है, हम इसे इसके साथ छोड़े जाने के लिए हटा सकते हैं: INDEX(B2:B5, 2)।
तो अब, यह एक सामान्य INDEX सूत्र की तरह है जहाँ हम B2:B5 में दूसरे आइटम का मान ज्ञात कर रहे हैं, जो कि लाल है।

पंक्ति और स्तंभ शीर्षकों द्वारा लुकअप

=INDEX(B2:E13, MATCH(G1, A2:A13, 0), MATCH(G2, B1:E1, 0))

MATCH और INDEX के इस उदाहरण में, हम टू-वे लुकअप कर रहे हैं। विचार यह देखना है कि हमने मई में हरी वस्तुओं से कितना पैसा कमाया। यह वास्तव में ऊपर दिए गए उदाहरण के समान है, लेकिन एक अतिरिक्त MATCH सूत्र INDEX में नेस्टेड है।

MATCH(G1, A2:A13, 0) इस फॉर्मूले में हल किया गया पहला आइटम है। यह एक विशेष मान प्राप्त करने के लिए A2:A13 में G1 ("मई" शब्द) की तलाश कर रहा है। हम इसे यहाँ नहीं देखते हैं, लेकिन यह 5 है।
MATCH(G2, B1:E1, 0) दूसरा MATCH फॉर्मूला है, और यह वास्तव में पहले के समान है, लेकिन इसके बजाय G2 (शब्द "ग्रीन" की तलाश में है)) कॉलम शीर्षकों में B1:E1 पर। यह 3 को हल करता है।
अब हम INDEX फॉर्मूले को इस तरह फिर से लिख सकते हैं कि क्या हो रहा है: =INDEX(B2:E13, 5, 3)। यह पूरी तालिका में देख रहा है, B2:E13, पांचवीं पंक्ति और तीसरे कॉलम के लिए, जो $180 देता है।

मैच और इंडेक्स नियम

इन कार्यों के साथ सूत्र लिखते समय कई बातों का ध्यान रखना चाहिए:

MATCH केस संवेदी नहीं है, इसलिए टेक्स्ट मानों का मिलान करते समय अपरकेस और लोअरकेस अक्षरों को समान माना जाता है।
MATCH कई कारणों से N/A लौटाता है: यदि match_type 0 है और lookup_value नहीं मिलता है यदि match_type -1 है और lookup_array अवरोही क्रम में नहीं है, यदि match_type 1 है और lookup_array आरोही में नहीं है आदेश, और अगर लुकअप_एरे एक पंक्ति या स्तंभ नहीं है।
अगर मैच_टाइप 0 है और लुकअप_वैल्यू एक टेक्स्ट स्ट्रिंग है तो आप लुकअप_वैल्यू तर्क में वाइल्डकार्ड कैरेक्टर का उपयोग कर सकते हैं। एक प्रश्न चिह्न किसी एकल वर्ण से मेल खाता है और एक तारांकन वर्णों के किसी भी क्रम से मेल खाता है (उदा।जी।, =MATCH("जो", 1:1, 0))। वास्तविक प्रश्न चिह्न या तारांकन खोजने के लिए MATCH का उपयोग करने के लिए, ~ पहले टाइप करें।
INDEX REF लौटाता है! अगर row_num और column_num सरणी के भीतर किसी सेल को इंगित नहीं करते हैं।