एक्सेल में INDEX और MATCH फंक्शन का उपयोग कैसे करें
पता करने के लिए क्या
- INDEX फ़ंक्शन का अकेले उपयोग किया जा सकता है, लेकिन इसके अंदर MATCH फ़ंक्शन को नेस्ट करने से एक उन्नत लुकअप बनता है।
- यह नेस्टेड फ़ंक्शन VLOOKUP की तुलना में अधिक लचीला है और परिणाम तेज़ी से प्राप्त कर सकता है।
यह आलेख बताता है कि Excel 2019 और Microsoft 365 सहित Excel के सभी संस्करणों में INDEX और MATCH फ़ंक्शंस का एक साथ उपयोग कैसे करें।
INDEX और MATCH फंक्शन क्या हैं?
INDEX और MATCH एक्सेल लुकअप हैं कार्यों. जबकि वे दो पूरी तरह से अलग कार्य हैं जिनका उपयोग स्वयं किया जा सकता है, उन्हें उन्नत सूत्र बनाने के लिए भी जोड़ा जा सकता है।
INDEX फ़ंक्शन किसी विशेष चयन के भीतर से एक मान या किसी मान का संदर्भ देता है। उदाहरण के लिए, इसका उपयोग डेटा सेट की दूसरी पंक्ति में या पांचवीं पंक्ति और तीसरे कॉलम में मान खोजने के लिए किया जा सकता है।
जबकि INDEX का अकेले उपयोग किया जा सकता है, MATCH को सूत्र में नेस्ट करना इसे थोड़ा अधिक उपयोगी बनाता है। MATCH फ़ंक्शन सेल की श्रेणी में निर्दिष्ट आइटम की खोज करता है और फिर श्रेणी में आइटम की सापेक्ष स्थिति लौटाता है। उदाहरण के लिए, इसका उपयोग यह निर्धारित करने के लिए किया जा सकता है कि नामों की सूची में एक विशिष्ट नाम तीसरा आइटम है।
INDEX और MATCH सिंटैक्स और तर्क
एक्सेल को समझने के लिए दोनों कार्यों को इस प्रकार लिखा जाना चाहिए:
=इंडेक्स(सरणी, पंक्ति_संख्या, [कॉलम_संख्या])
- सरणी कक्षों की वह श्रेणी है जिसका उपयोग सूत्र करेगा। यह एक या अधिक पंक्तियाँ और स्तंभ हो सकते हैं, जैसे A1:D5। यह आवश्यक है।
- पंक्ति_संख्या सरणी में वह पंक्ति है जिससे मान वापस करना है, जैसे कि 2 या 18। यह आवश्यक है जब तक कॉलम_संख्या उपस्थित है।
- कॉलम_संख्या सरणी का वह स्तंभ है जिससे मान लौटाना है, जैसे कि 1 या 9. यह वैकल्पिक है।
=मैच(पता लगाने का मूल्य, देखें_श्रेणी, [मिलान के प्रकार])
- पता लगाने का मूल्य वह मान है जिसका आप मिलान करना चाहते हैं देखें_श्रेणी. यह एक संख्या, पाठ या तार्किक मान हो सकता है जिसे मैन्युअल रूप से टाइप किया गया हो या सेल संदर्भ के माध्यम से संदर्भित किया गया हो। यह आवश्यक है।
- देखें_श्रेणी देखने के लिए कोशिकाओं की श्रेणी है। यह एकल पंक्ति या एकल स्तंभ हो सकता है, जैसे A2:D2 या G1:G45। यह आवश्यक है।
- मिलान के प्रकार हो सकता है -1, 0, या 1. यह निर्दिष्ट करता है कि कैसे पता लगाने का मूल्य मूल्यों के साथ मेल खाता है देखें_श्रेणी (निचे देखो)। 1 डिफ़ॉल्ट मान है यदि यह तर्क छोड़ा गया है।
| किस मिलान प्रकार का उपयोग करना है | |||
|---|---|---|---|
| मिलान के प्रकार | यह क्या करता है | नियम | उदाहरण |
| 1 | सबसे बड़ा मान ढूँढता है जो से कम या उसके बराबर हो पता लगाने का मूल्य. | NS देखें_श्रेणी मानों को आरोही क्रम में रखा जाना चाहिए (उदा., -2, -1, 0, 1, 2; या A-Z;, या FALSE, TRUE। | पता लगाने का मूल्य 25 है लेकिन यह गायब है देखें_श्रेणी, इसलिए अगली सबसे छोटी संख्या, जैसे 22, की स्थिति इसके बजाय लौटा दी जाती है। |
| 0 | पहला मान ढूँढता है जो के ठीक बराबर है पता लगाने का मूल्य. | NS देखें_श्रेणी मान किसी भी क्रम में हो सकते हैं। | पता लगाने का मूल्य 25 है, इसलिए यह 25 की स्थिति लौटाता है। |
| -1 | वह सबसे छोटा मान ढूँढता है जो से बड़ा या उसके बराबर हो पता लगाने का मूल्य. | NS देखें_श्रेणी मानों को अवरोही क्रम में रखा जाना चाहिए (जैसे, 2, 1, 0, -1, -2)। | पता लगाने का मूल्य 25 है लेकिन यह गायब है देखें_श्रेणी, इसलिए इसके बजाय अगली सबसे बड़ी संख्या, जैसे 34, की स्थिति लौटा दी जाती है। |
उपयोग 1 या -1 ऐसे समय के लिए जब आपको पैमाने के साथ अनुमानित लुकअप चलाने की आवश्यकता होती है, जैसे संख्याओं के साथ काम करते समय और सन्निकटन ठीक होने पर। लेकिन याद रखें कि यदि आप निर्दिष्ट नहीं करते हैं मिलान के प्रकार, 1 डिफ़ॉल्ट होगा, जो यदि आप वास्तव में एक सटीक मिलान चाहते हैं तो परिणाम तिरछा कर सकते हैं।
उदाहरण INDEX और MATCH फ़ार्मुले
इससे पहले कि हम देखें कि INDEX और MATCH को एक सूत्र में कैसे संयोजित किया जाए, हमें यह समझने की आवश्यकता है कि ये फ़ंक्शन अपने आप कैसे काम करते हैं।
सूचकांक उदाहरण
=इंडेक्स(ए1:बी2,2,2)
=इंडेक्स(ए1:बी1,1)
=इंडेक्स(2:2,1)
=इंडेक्स(बी1:बी2,1)
इस पहले उदाहरण में, चार INDEX सूत्र हैं जिनका उपयोग हम विभिन्न मान प्राप्त करने के लिए कर सकते हैं:
- =इंडेक्स(ए1:बी2,2,2) दूसरे कॉलम और दूसरी पंक्ति में मान खोजने के लिए A1:B2 के माध्यम से देखता है, जो है स्टेसी.
- =इंडेक्स(ए1:बी1,1) पहले कॉलम में मान खोजने के लिए A1:B1 के माध्यम से देखता है, जो है जॉन.
- =इंडेक्स(2:2,1) पहले कॉलम में मान का पता लगाने के लिए दूसरी पंक्ति में सब कुछ देखता है, जो है टिम।
- =इंडेक्स(बी1:बी2,1) पहली पंक्ति में मान का पता लगाने के लिए B1:B2 के माध्यम से देखता है, जो है एमी.
मैच उदाहरण
=MATCH("स्टेसी",A2:D2,0)
=MATCH(14,D1:D2)
=MATCH(14,D1:D2,-1)
=MATCH(13,A1:D1,0)
यहां MATCH फ़ंक्शन के चार आसान उदाहरण दिए गए हैं:
- =MATCH("स्टेसी",A2:D2,0) ढूंढ रहा है स्टेसी सीमा में A2:D2 और रिटर्न 3 परिणाम के रूप में।
- =MATCH(14,D1:D2) ढूंढ रहा है 14 सीमा में डी1:डी2, लेकिन चूंकि यह तालिका में नहीं मिला है, इसलिए MATCH को अगला सबसे बड़ा मान मिलता है जो इससे कम या उसके बराबर है 14, जो इस मामले में है 13, जो स्थिति में है 1 का देखें_श्रेणी.
- =MATCH(14,D1:D2,-1) इसके ऊपर के सूत्र के समान है, लेकिन चूंकि सरणी अवरोही क्रम में नहीं है जैसे -1 की आवश्यकता है, हमें एक त्रुटि मिलती है।
- =MATCH(13,A1:D1,0) तलाश में है 13 शीट की पहली पंक्ति में, जो वापस आती है 4 चूंकि यह इस सरणी में चौथा आइटम है।
INDEX-MATCH उदाहरण
यहां दो उदाहरण दिए गए हैं जहां हम INDEX और MATCH को एक सूत्र में जोड़ सकते हैं:
तालिका में सेल संदर्भ खोजें
=इंडेक्स(बी2:बी5,मैच(एफ1,ए2:ए5))
यह उदाहरण INDEX सूत्र के भीतर MATCH सूत्र को नेस्ट कर रहा है। लक्ष्य आइटम नंबर का उपयोग करके आइटम के रंग की पहचान करना है।
यदि आप छवि को देखते हैं, तो आप "पृथक" पंक्तियों में देख सकते हैं कि सूत्र अपने आप कैसे लिखे जाएंगे, लेकिन चूंकि हम उन्हें नेस्ट कर रहे हैं, यही हो रहा है:
- मैच (F1,A2:A5) की तलाश में है एफ1 डेटा सेट में मान (8795) A2:A5. यदि हम कॉलम को काउंट डाउन करते हैं, तो हम इसे देख सकते हैं: 2, इसलिए MATCH फ़ंक्शन का अभी-अभी पता चला है।
- INDEX सरणी है बी2:बी5 चूंकि हम अंततः उस कॉलम में मान की तलाश कर रहे हैं।
- INDEX फ़ंक्शन को अब इस तरह से फिर से लिखा जा सकता है 2 MATCH को क्या मिला: INDEX(B2:B5, 2, [column_num]).
- तब से कॉलम_संख्या वैकल्पिक है, हम इसे इसके साथ छोड़े जाने के लिए हटा सकते हैं: इंडेक्स (बी 2: बी 5,2).
- तो अब, यह एक सामान्य INDEX सूत्र की तरह है जहाँ हम दूसरे आइटम का मान ज्ञात कर रहे हैं बी2:बी5, जो है लाल.
पंक्ति और स्तंभ शीर्षकों द्वारा लुकअप
=इंडेक्स(बी2:ई13,मैच(जी1,ए2:ए13,0), मैच(जी2,बी1:ई10))
MATCH और INDEX के इस उदाहरण में, हम टू-वे लुकअप कर रहे हैं। विचार यह देखना है कि हमने कितना पैसा कमाया हरा में आइटम मई. यह वास्तव में ऊपर दिए गए उदाहरण के समान है, लेकिन एक अतिरिक्त MATCH सूत्र INDEX में नेस्टेड है।
- मैच (G1,A2:A13,0) इस सूत्र में हल किया गया पहला आइटम है। यह ढूंढ रहा है G1 (शब्द "मई") in A2:A13 एक विशेष मूल्य प्राप्त करने के लिए। हम इसे यहाँ नहीं देखते हैं, लेकिन यह है 5.
- मैच (G2,B1:E1,0) दूसरा MATCH फॉर्मूला है, और यह वास्तव में पहले के समान है, लेकिन इसके बजाय ढूंढ रहा है G2 (शब्द "हरा") कॉलम शीर्षकों में बी1:ई1. यह संकल्प करता है 3.
- क्या हो रहा है इसकी कल्पना करने के लिए अब हम INDEX सूत्र को इस तरह फिर से लिख सकते हैं: =इंडेक्स(बी2:ई13,5,3). यह पूरी तालिका में देख रहा है, बी2:ई13, पाँचवीं पंक्ति और तीसरे स्तंभ के लिए, जो लौटता है $180.
मैच और इंडेक्स नियम
इन कार्यों के साथ सूत्र लिखते समय कई बातों का ध्यान रखना चाहिए:
- मैच नहीं है अक्षर संवेदनशील, इसलिए टेक्स्ट मानों का मिलान करते समय अपरकेस और लोअरकेस अक्षरों के साथ समान व्यवहार किया जाता है।
- मैच रिटर्न #एन/ए कई कारणों से: अगर मिलान के प्रकार है 0 तथा पता लगाने का मूल्य नहीं मिला है तो मिलान के प्रकार है -1 तथा देखें_श्रेणी अवरोही क्रम में नहीं है, अगर मिलान के प्रकार है 1 तथा देखें_श्रेणी आरोही क्रम में नहीं है, और यदि देखें_श्रेणी एक पंक्ति या स्तंभ नहीं है।
- आप में वाइल्डकार्ड वर्ण का उपयोग कर सकते हैं पता लगाने का मूल्य तर्क अगर मिलान के प्रकार है 0 तथा पता लगाने का मूल्य एक टेक्स्ट स्ट्रिंग है। एक प्रश्न चिह्न किसी एकल वर्ण से मेल खाता है और एक तारांकन वर्णों के किसी भी क्रम से मेल खाता है (जैसे, =MATCH("जो*",1:1,0)). वास्तविक प्रश्न चिह्न या तारांकन खोजने के लिए MATCH का उपयोग करने के लिए, पहले ~ टाइप करें।
- सूचकांक रिटर्न #संदर्भ! अगर पंक्ति_संख्या तथा कॉलम_संख्या सरणी के भीतर किसी सेल को इंगित न करें।
संबंधित एक्सेल कार्य
MATCH फ़ंक्शन लुकअप के समान है, लेकिन MATCH लौटाता है पद आइटम के बजाय आइटम का ही।
VLOOKUP एक और लुकअप फ़ंक्शन है जिसे आप Excel में उपयोग कर सकते हैं, लेकिन MATCH के विपरीत जिसमें उन्नत लुकअप के लिए INDEX की आवश्यकता होती है, VLOOKUP फ़ार्मुलों को केवल एक फ़ंक्शन की आवश्यकता होती है।