जानने योग्य बातें
- INDEX फंक्शन का उपयोग अकेले किया जा सकता है, लेकिन इसके अंदर MATCH फंक्शन को नेस्ट करने से एक उन्नत लुकअप बनता है।
- यह नेस्टेड फंक्शन VLOOKUP से अधिक लचीला है और तेजी से परिणाम दे सकता है।
यह लेख सभी संस्करणों में Excel में INDEX और MATCH फंक्शन को एक साथ उपयोग करने का तरीका बताता है, जिसमें Excel 2019 और Microsoft 365 शामिल हैं।
INDEX और MATCH फंक्शन क्या हैं?
INDEX और MATCH Excel लुकअप फंक्शन हैं। हालांकि वे दो पूरी तरह से अलग फंक्शन हैं जिनका उपयोग अपने दम पर किया जा सकता है, उन्हें उन्नत फॉर्मूला बनाने के लिए भी जोड़ा जा सकता है।
INDEX फंक्शन किसी विशेष चयन के भीतर से एक मान या मान का संदर्भ लौटाता है। उदाहरण के लिए, इसका उपयोग डेटा सेट की दूसरी पंक्ति में मान खोजने के लिए, या पांचवीं पंक्ति और तीसरे कॉलम में किया जा सकता है।
जबकि INDEX का उपयोग अकेले किया जा सकता है, फॉर्मूला में MATCH को नेस्ट करने से यह थोड़ा अधिक उपयोगी हो जाता है। MATCH फंक्शन सेल्स की एक श्रेणी में एक निर्दिष्ट आइटम की खोज करता है और फिर श्रेणी में आइटम की सापेक्ष स्थिति लौटाता है। उदाहरण के लिए, इसका उपयोग यह निर्धारित करने के लिए किया जा सकता है कि कोई विशिष्ट नाम नामों की सूची में तीसरा आइटम है।
INDEX और MATCH सिंटैक्स और तर्क
Excel को समझने के लिए दोनों फंक्शन को इस तरह लिखने की आवश्यकता है:
=INDEX(array, row_num, [column_num])
- array वह सेल रेंज है जिसका उपयोग फॉर्मूला करेगा। यह एक या अधिक पंक्तियों और कॉलम हो सकते हैं, जैसे 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 हो सकता है। यह निर्दिष्ट करता है कि lookup_value का lookup_array में मानों से कैसे मिलान किया जाता है (नीचे देखें)। यदि यह तर्क छोड़ा जाता है तो 1 डिफ़ॉल्ट मान है।
| कौन सा मैच प्रकार उपयोग करें | |||
|---|---|---|---|
| मैच प्रकार | यह क्या करता है | नियम | उदाहरण |
| 1 | वह सबसे बड़ा मान ढूंढता है जो lookup_value से कम या बराबर है। | lookup_array मान आरोही क्रम में होने चाहिए (जैसे -2, -1, 0, 1, 2; या A-Z;, या FALSE, TRUE। | lookup_value 25 है लेकिन यह lookup_array से गायब है, इसलिए अगली सबसे छोटी संख्या की स्थिति, जैसे 22, लौटाई जाती है। |
| 0 | पहला मान ढूंढता है जो lookup_value के बिल्कुल बराबर है। | lookup_array मान किसी भी क्रम में हो सकते हैं। | lookup_value 25 है, इसलिए यह 25 की स्थिति लौटाता है। |
| -1 | वह सबसे छोटा मान ढूंढता है जो lookup_value से बड़ा या बराबर है। | lookup_array मान अवरोही क्रम में होने चाहिए (जैसे 2, 1, 0, -1, -2)। | lookup_value 25 है लेकिन यह lookup_array से गायब है, इसलिए अगली सबसे बड़ी संख्या की स्थिति, जैसे 34, लौटाई जाती है। |
1 या -1 का उपयोग उन समय के लिए करें जब आपको पैमाने पर एक अनुमानित लुकअप चलाने की आवश्यकता हो, जैसे संख्याओं के साथ काम करते समय और जब अनुमान ठीक हों। लेकिन याद रखें कि यदि आप match_type निर्दिष्ट नहीं करते हैं, तो 1 डिफ़ॉल्ट होगा, जो परिणामों को तिरछा कर सकता है यदि आप वास्तव में सटीक मिलान चाहते हैं।
INDEX और MATCH फॉर्मूला उदाहरण
इससे पहले कि हम देखें कि INDEX और MATCH को एक फॉर्मूला में कैसे जोड़ा जाए, हमें यह समझने की जरूरत है कि ये फंक्शन अपने दम पर कैसे काम करते हैं।
INDEX उदाहरण
=INDEX(A1:B2,2,2) =INDEX(A1:B1,1) =INDEX(2:2,1) =INDEX(B1:B2,1)
इस पहले उदाहरण में, चार INDEX फॉर्मूला हैं जिनका उपयोग हम विभिन्न मान प्राप्त करने के लिए कर सकते हैं:
- =INDEX(A1:B2,2,2) A1:B2 के माध्यम से दूसरी पंक्ति और दूसरे कॉलम में मान ढूंढता है, जो Stacy है।
- =INDEX(A1:B1,1) A1:B1 के माध्यम से पहले कॉलम में मान ढूंढता है, जो Jon है।
- =INDEX(2:2,1) दूसरी पंक्ति में हर चीज के माध्यम से पहले कॉलम में मान का पता लगाता है, जो Tim है।
- =INDEX(B1:B2,1) B1:B2 के माध्यम से पहली पंक्ति में मान का पता लगाता है, जो Amy है।
MATCH उदाहरण
=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 रेंज में Stacy खोज रहा है और परिणाम के रूप में 3 लौटाता है।
- =MATCH(14,D1:D2) D1:D2 रेंज में 14 खोज रहा है, लेकिन चूंकि यह तालिका में नहीं मिला, MATCH अगला सबसे बड़ा मान ढूंढता है जो 14 से कम या बराबर है, जो इस मामले में 13 है, जो lookup_array की स्थिति 1 में है।
- =MATCH(14,D1:D2,-1) ऊपर वाले फॉर्मूला के समान है, लेकिन चूंकि सरणी अवरोही क्रम में नहीं है जैसे -1 के लिए आवश्यक है, हमें एक त्रुटि मिलती है।
- =MATCH(13,A1:D1,0) शीट की पहली पंक्ति में 13 ढूंढ रहा है, जो 4 लौटाता है क्योंकि यह इस सरणी में चौथा आइटम है।
INDEX-MATCH उदाहरण
यहां दो उदाहरण हैं जहां हम INDEX और MATCH को एक फॉर्मूला में जोड़ सकते हैं:
तालिका में सेल संदर्भ खोजें
=INDEX(B2:B5,MATCH(F1,A2:A5))
यह उदाहरण INDEX फॉर्मूला के भीतर MATCH फॉर्मूला को नेस्ट कर रहा है। लक्ष्य आइटम नंबर का उपयोग करके आइटम रंग की पहचान करना है।
यदि आप छवि को देखते हैं, तो आप "Separated" पंक्तियों में देख सकते हैं कि फॉर्मूला अपने दम पर कैसे लिखे जाएंगे, लेकिन चूंकि हम उन्हें नेस्ट कर रहे हैं, यह हो रहा है:
- MATCH(F1,A2:A5) डेटा सेट A2:A5 में F1 मान (8795) ढूंढ रहा है। यदि हम कॉलम को नीचे गिनते हैं, तो हम देख सकते हैं कि यह 2 है, इसलिए MATCH फंक्शन ने यही निकाला।
- INDEX सरणी B2:B5 है क्योंकि हम अंततः उस कॉलम में मान ढूंढ रहे हैं।
- INDEX फंक्शन अब इसे इस तरह फिर से लिखा जा सकता है क्योंकि 2 वही है जो MATCH ने पाया: INDEX(B2:B5, 2, [column_num])।
- चूंकि column_num वैकल्पिक है, हम इसे हटाकर इसे छोड़ सकते हैं: INDEX(B2:B5,2)।
- तो अब, यह एक सामान्य INDEX फॉर्मूला की तरह है जहां हम B2:B5 में दूसरे आइटम का मान ढूंढ रहे हैं, जो red है।
पंक्ति और कॉलम हेडिंग द्वारा लुकअप
=INDEX(B2:E13,MATCH(G1,A2:A13,0),MATCH(G2,B1:E1,0))
MATCH और INDEX के इस उदाहरण में, हम टू-वे लुकअप कर रहे हैं। विचार यह देखना है कि हमने May में Green आइटम से कितना पैसा कमाया। यह वास्तव में ऊपर वाले उदाहरण के समान है, लेकिन INDEX में एक अतिरिक्त MATCH फॉर्मूला नेस्टेड है।
- MATCH(G1,A2:A13,0) इस फॉर्मूला में हल होने वाली पहली वस्तु है। यह A2:A13 में G1 (शब्द "May") ढूंढ रहा है एक विशेष मान प्राप्त करने के लिए। हम इसे यहां नहीं देखते, लेकिन यह 5 है।
- MATCH(G2,B1:E1,0) दूसरा MATCH फॉर्मूला है, और यह वास्तव में पहले के समान है लेकिन इसके बजाय B1:E1 पर कॉलम हेडिंग में G2 (शब्द "Green") ढूंढ रहा है। यह 3 पर हल होता है।
- अब हम INDEX फॉर्मूला को इस तरह फिर से लिख सकते हैं कि क्या हो रहा है: =INDEX(B2:E13,5,3)। यह पूरी तालिका, B2:E13, में पांचवीं पंक्ति और तीसरे कॉलम के लिए देख रहा है, जो $180 लौटाता है।
MATCH और INDEX नियम
इन फंक्शन के साथ फॉर्मूला लिखते समय ध्यान रखने योग्य कई बातें हैं:
- MATCH केस-संवेदी नहीं है, इसलिए टेक्स्ट मानों का मिलान करते समय अपरकेस और लोअरकेस अक्षरों को समान माना जाता है।
- MATCH कई कारणों से #N/A लौटाता है: यदि match_type 0 है और lookup_value नहीं मिला; यदि match_type -1 है और lookup_array अवरोही क्रम में नहीं है; यदि match_type 1 है और lookup_array आरोही क्रम में नहीं है; और यदि lookup_array एक पंक्ति या कॉलम नहीं है।
- यदि match_type 0 है और lookup_value एक टेक्स्ट स्ट्रिंग है, तो आप lookup_value तर्क में वाइल्डकार्ड वर्ण का उपयोग कर सकते हैं। एक प्रश्न चिह्न किसी एक वर्ण से मेल खाता है और एक तारक वर्णों के किसी भी क्रम से मेल खाता है (उदाहरण: =MATCH("Jo*",1:1,0))। MATCH का उपयोग करके वास्तविक प्रश्न चिह्न या तारक खोजने के लिए, पहले ~ टाइप करें।
- यदि row_num और column_num सरणी के भीतर किसी सेल को इंगित नहीं करते हैं तो INDEX #REF! लौटाता है।
संबंधित Excel फंक्शन
MATCH फंक्शन LOOKUP के समान है, लेकिन MATCH आइटम के बजाय आइटम की स्थिति लौटाता है।
VLOOKUP Excel में उपयोग करने योग्य एक अन्य लुकअप फंक्शन है, लेकिन MATCH के विपरीत जिसे उन्नत लुकअप के लिए INDEX की आवश्यकता होती है, VLOOKUP फॉर्मूला को केवल उस एक फंक्शन की आवश्यकता होती है।
