विषय-सूची
हमने पहले शुरुआती लोगों को समझाया है कि VLOOKUP के बुनियादी कार्यों का उपयोग कैसे करें (अंग्रेजी VLOOKUP, संक्षिप्त नाम "ऊर्ध्वाधर लुकअप फ़ंक्शन") है। और अनुभवी उपयोगकर्ताओं को कई और जटिल सूत्र दिखाए गए।
और इस लेख में हम लंबवत खोज के साथ काम करने की एक और विधि के बारे में जानकारी देने का प्रयास करेंगे।
आप सोच रहे होंगे: "यह क्यों आवश्यक है?"। और सभी संभावित खोज विधियों को दिखाने के लिए यह आवश्यक है। इसके अलावा, कई VLOOKUP प्रतिबंध अक्सर वांछित परिणाम प्राप्त करने से रोकते हैं। इस संबंध में, INDEX ( ) MATCH ( ) बहुत अधिक कार्यात्मक और विविध है, और उनके पास कम प्रतिबंध भी हैं।
मूल बातें INDEX MATCH
चूंकि इस गाइड का उद्देश्य यह दिखाना है कि यह सुविधा कितनी अच्छी है, हम आइए इसके संचालन के सिद्धांतों के बारे में बुनियादी जानकारी देखें। और हम उदाहरण दिखाएंगे, और यह भी विचार करेंगे कि क्यों, यह VLOOKUP () से बेहतर है।
इंडेक्स फ़ंक्शन सिंटैक्स और उपयोग
यह फ़ंक्शन कॉलम या लाइन नंबर के आधार पर निर्दिष्ट खोज क्षेत्रों के बीच वांछित मूल्य खोजने में मदद करता है। सिंटेक्स:
= इंडेक्स (सरणी, पंक्ति संख्या, स्तंभ संख्या):
- सरणी - वह क्षेत्र जिसमें खोज होगी;
- पंक्ति संख्या - निर्दिष्ट सरणी में खोजी जाने वाली रेखा की संख्या। यदि पंक्ति संख्या अज्ञात है, तो स्तंभ संख्या निर्दिष्ट की जानी चाहिए;
- कॉलम नंबर - निर्दिष्ट सरणी में पाए जाने वाले कॉलम की संख्या। यदि मान अज्ञात है, तो एक पंक्ति संख्या आवश्यक है।
एक सरल सूत्र का उदाहरण:
= इंडेक्स (ए 1: एस 10,2,3)
फ़ंक्शन A1 से C10 तक की श्रेणी में खोज करेगा। संख्याएँ दिखाती हैं कि किस पंक्ति (2) और कॉलम (3) से वांछित मान दिखाना है। परिणाम सेल C2 होगा।
बहुत आसान है, है ना? लेकिन जब आप वास्तविक दस्तावेजों के साथ काम करते हैं, तो आपको कॉलम नंबर या सेल के बारे में जानकारी होने की संभावना नहीं है। यही MATCH() फ़ंक्शन के लिए है।
मैच फ़ंक्शन सिंटैक्स और उपयोग
MATCH () फ़ंक्शन वांछित मान की खोज करता है और निर्दिष्ट खोज क्षेत्र में इसकी अनुमानित संख्या दिखाता है।
सर्चपोस () सिंटैक्स इस तरह दिखता है:
=MATCH(लुकअप के लिए मान, देखने के लिए सरणी, मिलान प्रकार)
- खोज मूल्य - खोजने के लिए संख्या या पाठ;
- खोजी गई सरणी - वह क्षेत्र जहां खोज होगी;
- मिलान प्रकार - निर्दिष्ट करता है कि सटीक मान या उसके निकटतम मानों को देखना है या नहीं:
- 1 (या कोई मान निर्दिष्ट नहीं) - निर्दिष्ट किए गए मान के बराबर या उससे कम का सबसे बड़ा मान लौटाता है;
- 0 - खोजे गए मान के साथ सटीक मिलान दिखाता है। संयोजन में INDEX() MATCH() आपको लगभग हमेशा एक सटीक मिलान की आवश्यकता होगी, इसलिए हम 0 लिखते हैं;
- -1 - वह सबसे छोटा मान दिखाता है जो सूत्र में निर्दिष्ट मान से अधिक या उसके बराबर हो। छँटाई अवरोही क्रम में की जाती है।
उदाहरण के लिए, श्रेणी में B1:B3 न्यूयॉर्क, पेरिस, लंदन पंजीकृत हैं। नीचे दिया गया सूत्र नंबर 3 दिखाएगा क्योंकि सूची में लंदन तीसरे स्थान पर है:
=एक्सपोज़ (लंदन,बी1:बी3,0)
INDEX MATCH फंक्शन के साथ कैसे काम करें
आप शायद पहले से ही उस सिद्धांत को समझना शुरू कर चुके हैं जिसके द्वारा इन कार्यों का संयुक्त कार्य बनाया गया है। संक्षेप में तो INDEX() निर्दिष्ट पंक्तियों और स्तंभों के बीच वांछित मान की खोज करता है। और MATCH() इन मानों की संख्या दिखाता है:
=INDEX(कॉलम जिससे मान लौटाया जाता है, MATCH(खोज के लिए मान, खोजने के लिए कॉलम, 0))
अभी भी यह समझने में कठिनाई हो रही है कि यह कैसे काम करता है? शायद एक उदाहरण बेहतर व्याख्या करेगा। मान लीजिए आपके पास विश्व की राजधानियों और उनकी जनसंख्या की सूची है:
एक निश्चित राजधानी की जनसंख्या के आकार का पता लगाने के लिए, उदाहरण के लिए, जापान की राजधानी, हम निम्नलिखित सूत्र का उपयोग करते हैं:
=इंडेक्स(C2:C10, MATCH(जापान, A2:A10,0))
स्पष्टीकरण:
- MATCH() फ़ंक्शन A2:A10 सरणी में मान - "जापान" की तलाश करता है और नंबर 3 देता है, क्योंकि जापान सूची में तीसरा मान है।
- यह आंकड़ा जाता हैरेखा संख्याINDEX() सूत्र में और फ़ंक्शन को इस पंक्ति से एक मान प्रिंट करने के लिए कहता है।
तो उपरोक्त सूत्र मानक सूत्र बन जाता है इंडेक्स (सी 2: सी 10,3)। सूत्र सेल C2 से C10 तक खोज करता है और इस श्रेणी में तीसरे सेल से डेटा लौटाता है, जो कि C4 है, क्योंकि दूसरी पंक्ति से उलटी गिनती शुरू होती है।
सूत्र में शहर का नाम नहीं लिखना चाहते हैं? फिर इसे किसी भी सेल में लिखें, जैसे कि F1 और इसे MATCH () फॉर्मूले में संदर्भ के रूप में उपयोग करें। और आप एक गतिशील खोज सूत्र के साथ समाप्त होते हैं:
=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))
महत्वपूर्ण! लाइनों की संख्या सरणी INDEX() पंक्तियों की संख्या के समान होना चाहिए माना सरणी MATCH() में, अन्यथा आपको गलत परिणाम मिलेगा।
एक मिनट रुकिए, क्यों न केवल VLOOKUP () सूत्र का उपयोग किया जाए?
=VLOOKUP(F1, A2:C10, 3, असत्य)
INDEX MATCH की इन सभी जटिलताओं का पता लगाने में समय बर्बाद करने का क्या मतलब है?
इस मामले में, इससे कोई फर्क नहीं पड़ता कि किस फ़ंक्शन का उपयोग करना है। यह समझने के लिए सिर्फ एक उदाहरण है कि INDEX () और MATCH () फ़ंक्शन एक साथ कैसे काम करते हैं। अन्य उदाहरण दिखाएंगे कि ये फ़ंक्शन उन स्थितियों में क्या करने में सक्षम हैं जहां VLOOKUP शक्तिहीन है।
INDEX MATCH या VLOOKUP
यह तय करते समय कि किस खोज सूत्र का उपयोग करना है, कई लोग इस बात से सहमत हैं कि INDEX() और MATCH() VLOOKUP से काफी बेहतर हैं। हालाँकि, बहुत से लोग अभी भी VLOOKUP () का उपयोग करते हैं। सबसे पहले, VLOOKUP () सरल है, और दूसरी बात, उपयोगकर्ता INDEX () और MATCH () के साथ काम करने के सभी लाभों को पूरी तरह से नहीं समझते हैं। इस ज्ञान के बिना, कोई भी एक जटिल प्रणाली का अध्ययन करने में अपना समय व्यतीत करने के लिए सहमत नहीं होगा।
यहाँ VLOOKUP() पर INDEX () और MATCH () के प्रमुख लाभ दिए गए हैं:
- दाएं से बाएं खोजें। VLOOKUP () दाएं से बाएं नहीं खोज सकता है, इसलिए आप जो मान खोज रहे हैं वह हमेशा तालिका के सबसे बाएं कॉलम में होना चाहिए। लेकिन INDEX() और MATCH() बिना किसी समस्या के इसे संभाल सकते हैं। यह लेख आपको बताएगा कि यह व्यवहार में कैसा दिखता है: बाईं ओर वांछित मूल्य कैसे प्राप्त करें।
- स्तंभों को सुरक्षित रूप से जोड़ना या हटाना। कॉलम को हटाते या जोड़ते समय VLOOKUP () फॉर्मूला गलत परिणाम दिखाता है क्योंकि VLOOKUP () को सफल होने के लिए सटीक कॉलम नंबर की आवश्यकता होती है। स्वाभाविक रूप से, जब कॉलम जोड़े या हटाए जाते हैं, तो उनकी संख्या भी बदल जाती है।
और INDEX() और MATCH() फ़ार्मुलों में, स्तंभों की एक श्रेणी निर्दिष्ट की जाती है, अलग-अलग कॉलम नहीं। परिणामस्वरूप, आप हर बार सूत्र को अपडेट किए बिना सुरक्षित रूप से कॉलम जोड़ और हटा सकते हैं।
- खोज मात्रा की कोई सीमा नहीं. VLOOKUP() का उपयोग करते समय, खोज मानदंड की कुल संख्या 255 वर्णों से अधिक नहीं होनी चाहिए या आपको #VALUE! इसलिए यदि आपके डेटा में बड़ी संख्या में वर्ण हैं, तो INDEX() और MATCH() सबसे अच्छा विकल्प हैं।
- उच्च प्रसंस्करण गति। यदि आपकी टेबल अपेक्षाकृत छोटी हैं, तो आपको कोई अंतर नज़र आने की संभावना नहीं है। लेकिन, यदि तालिका में सैकड़ों या हजारों पंक्तियाँ हैं, और, तदनुसार, सैकड़ों और हजारों सूत्र हैं, INDEX () और MATCH () VLOOKUP () की तुलना में बहुत तेजी से सामना करेंगे। तथ्य यह है कि एक्सेल केवल संपूर्ण तालिका को संसाधित करने के बजाय सूत्र में निर्दिष्ट कॉलम को संसाधित करेगा।
VLOOKUP() का प्रदर्शन प्रभाव विशेष रूप से ध्यान देने योग्य होगा यदि आपकी वर्कशीट में VLOOKUP() और SUM() जैसे बड़ी संख्या में सूत्र हैं। सरणी में प्रत्येक मान को पार्स करने के लिए VLOOKUP () फ़ंक्शन की अलग-अलग जाँच की आवश्यकता होती है। तो एक्सेल को बड़ी मात्रा में जानकारी संसाधित करनी पड़ती है, और यह काम को काफी धीमा कर देती है।
सूत्र उदाहरण
हम पहले ही इन कार्यों की उपयोगिता का पता लगा चुके हैं, इसलिए हम सबसे दिलचस्प भाग पर आगे बढ़ सकते हैं: व्यवहार में ज्ञान का अनुप्रयोग।
दाएँ से बाएँ खोजने का सूत्र
जैसा कि पहले ही उल्लेख किया गया है, VLOOKUP इस प्रकार की खोज नहीं कर सकता है। इसलिए, यदि वांछित मान सबसे बाएं कॉलम में नहीं हैं, तो VLOOKUP() परिणाम नहीं देगा। INDEX () और MATCH () फ़ंक्शन अधिक बहुमुखी हैं, और मूल्यों का स्थान उनके काम करने के लिए एक बड़ी भूमिका नहीं निभाता है।
उदाहरण के लिए, हम अपनी तालिका के बाईं ओर एक रैंक कॉलम जोड़ेंगे और यह पता लगाने की कोशिश करेंगे कि हमारे देश की राजधानी जनसंख्या के मामले में किस रैंक पर है।
सेल G1 में, हम पाया जाने वाला मान लिखते हैं, और फिर C1:C10 श्रेणी में खोजने के लिए निम्न सूत्र का उपयोग करते हैं और A2:A10 से संबंधित मान लौटाते हैं:
=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))
तत्पर। यदि आप एकाधिक कक्षों के लिए इस सूत्र का उपयोग करने की योजना बना रहे हैं, तो सुनिश्चित करें कि आप पूर्ण पते का उपयोग करके श्रेणियों को ठीक करते हैं (उदाहरण के लिए, $A$2: $A$10 और $C$2: 4C$10).
इंडेक्स अधिक एक्सपोज़्ड अधिक एक्सपोज़्ड कॉलम और पंक्तियों में खोजने के लिए
उपरोक्त उदाहरणों में, हमने इन फ़ंक्शंस का उपयोग VLOOKUP () के प्रतिस्थापन के रूप में पंक्तियों की पूर्वनिर्धारित श्रेणी से मान वापस करने के लिए किया है। लेकिन क्या होगा अगर आपको मैट्रिक्स या दो-तरफा खोज करने की ज़रूरत है?
यह जटिल लगता है, लेकिन इस तरह की गणना के लिए सूत्र मानक INDEX() MATCH() सूत्र के समान है, केवल एक अंतर के साथ: MATCH () सूत्र का दो बार उपयोग किया जाना चाहिए। पहली बार पंक्ति संख्या प्राप्त करने के लिए, और दूसरी बार स्तंभ संख्या प्राप्त करने के लिए:
= इंडेक्स (सरणी, मिलान (ऊर्ध्वाधर खोज मान, खोज स्तंभ, 0)), मिलान (क्षैतिज खोज मान, खोज पंक्ति, 0))
आइए नीचे दी गई तालिका को देखें और एक सूत्र बनाने का प्रयास करें इंडेक्स () एक्सप्रेस () एक्सप्रेस () किसी चयनित वर्ष के लिए किसी विशिष्ट देश में जनसांख्यिकी प्रदर्शित करने के लिए।
लक्ष्य देश सेल G1 (वर्टिकल लुकअप) में है और लक्ष्य वर्ष सेल G2 (क्षैतिज लुकअप) में है। सूत्र इस तरह दिखेगा:
=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))
कैसे काम करता है यह फॉर्मूला
किसी भी अन्य जटिल फ़ार्मुलों की तरह, उन्हें अलग-अलग समीकरणों में तोड़कर समझना आसान होता है। और फिर आप समझ सकते हैं कि प्रत्येक व्यक्तिगत कार्य क्या करता है:
- मैच (G1,A2:A11,0) - श्रेणी A1:A2 में एक मान (G11) की तलाश करता है और इस मान की संख्या दिखाता है, हमारे मामले में यह 2 है;
- खोज (G2,B1:D1,0) - B2:D1 श्रेणी में मान (G1) की तलाश करता है। इस मामले में, परिणाम 3 था।
मिली पंक्ति और स्तंभ संख्या INDEX() सूत्र में संबंधित मान पर भेजी जाती हैं:
=इंडेक्स(बी2:डी11,2,3)
नतीजतन, हमारे पास एक मान है जो एक सेल में 2 पंक्तियों और 3 स्तंभों के चौराहे पर B2:D11 श्रेणी में है। और सूत्र वांछित मान दिखाता है, जो सेल D3 में है।
INDEX और MATCH . के साथ अनेक स्थितियों के आधार पर खोजें
यदि आपने हमारे VLOOKUP () गाइड को पढ़ा है, तो आपने शायद कई खोज फ़ार्मुलों को आज़माया है। लेकिन इस खोज पद्धति की एक महत्वपूर्ण सीमा है - एक सहायक कॉलम जोड़ने की आवश्यकता।
लेकिन अच्छी खबर यह है कि INDEX () और MATCH () के साथ आप अपनी वर्कशीट को संपादित या बदले बिना कई शर्तों की खोज कर सकते हैं।
यहां INDEX() MATCH() के लिए सामान्य बहु-शर्त खोज सूत्र है:
{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}
नोट: इस सूत्र का उपयोग कीबोर्ड शॉर्टकट के साथ किया जाना चाहिए CTRL+SHIFT+ENTER.
मान लीजिए कि आपको 2 शर्तों के आधार पर वह मान ढूंढना है जिसे आप ढूंढ रहे हैं: खरीददार и उत्पाद।
इसके लिए निम्न सूत्र की आवश्यकता है:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))
इस सूत्र में, C2:C10 वह श्रेणी है जिसमें खोज की जाएगी, F1 - यह स्थिति, A2:A10 - स्थिति की तुलना करने की सीमा है, F2 - शर्त 2, V2: V10 - स्थिति 2 की तुलना के लिए सीमा।
काम के अंत में सूत्र के साथ संयोजन को दबाना न भूलें CTRL+SHIFT+ENTER - एक्सेल स्वचालित रूप से घुंघराले ब्रेसिज़ के साथ सूत्र को बंद कर देगा, जैसा कि उदाहरण में दिखाया गया है:
यदि आप अपने काम के लिए एक सरणी सूत्र का उपयोग नहीं करना चाहते हैं, तो सूत्र में एक और INDEX() जोड़ें और ENTER दबाएं, यह उदाहरण में जैसा दिखेगा:
कैसे काम करते हैं ये सूत्र
यह सूत्र मानक INDEX() MATCH() सूत्र की तरह ही काम करता है। कई स्थितियों की खोज करने के लिए, आप बस कई गलत और सही स्थितियां बनाते हैं जो सही और गलत व्यक्तिगत स्थितियों का प्रतिनिधित्व करती हैं। और फिर ये शर्तें सरणी के सभी संगत तत्वों पर लागू होती हैं। सूत्र गलत और सही तर्कों को क्रमशः 0 और 1 में परिवर्तित करता है, और एक सरणी को आउटपुट करता है जहां 1 मिलान मान है जो स्ट्रिंग में पाए गए थे। MATCH() को पहला मान मिलेगा जो 1 से मेल खाता है और इसे INDEX() सूत्र में पास करता है। और यह, बदले में, वांछित कॉलम से निर्दिष्ट पंक्ति में पहले से ही वांछित मान लौटाएगा।
एक सरणी के बिना एक सूत्र INDEX () की क्षमता पर निर्भर करता है कि वह उन्हें स्वयं संभाल सके। सूत्र में दूसरा INDEX () मिथ्या (0) से मेल खाता है, इसलिए यह उन मानों के साथ संपूर्ण सरणी को MATCH () सूत्र में पास करता है।
यह इस सूत्र के पीछे के तर्क की एक लंबी व्याख्या है। अधिक जानकारी के लिए लेख पढ़ें "कई शर्तों के साथ INDEX MATCH'.
INDEX और MATCH में औसत, MAX और MIN
औसत, अधिकतम और न्यूनतम खोजने के लिए एक्सेल के अपने विशेष कार्य हैं। लेकिन क्या होगा यदि आप उन मूल्यों से जुड़े सेल से डेटा प्राप्त करना चाहते हैं? इस मामले में INDEX और MATCH के संयोजन में AVERAGE, MAX और MIN का उपयोग किया जाना चाहिए।
INDEX MATCH और MAX
स्तंभ D में सबसे बड़ा मान ज्ञात करने और उसे स्तंभ C में प्रदर्शित करने के लिए, सूत्र का उपयोग करें:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))
इंडेक्स मैच और मिन
स्तंभ D में सबसे छोटा मान ज्ञात करने और उसे स्तंभ C में प्रदर्शित करने के लिए, निम्न सूत्र का उपयोग करें:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))
खोज सूचकांक और सर्प
कॉलम D में औसत मान ज्ञात करने के लिए और इस मान को C में प्रदर्शित करने के लिए:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))
आपका डेटा कैसे लिखा जाता है, इस पर निर्भर करते हुए, MATCH() का तीसरा तर्क या तो 1, 0, या -1 है:
- यदि स्तंभों को आरोही क्रम में क्रमबद्ध किया जाता है, तो 1 सेट करें (तब सूत्र अधिकतम मान की गणना करेगा, जो औसत मान से कम या उसके बराबर है);
- यदि सॉर्ट अवरोही हो रहा है, तो -1 (सूत्र उस न्यूनतम मान को आउटपुट करेगा जो औसत से अधिक या उसके बराबर है);
- यदि लुकअप सरणी में एक मान है जो बिल्कुल औसत के बराबर है, तो इसे 0 पर सेट करें।
हमारे उदाहरण में, जनसंख्या को अवरोही क्रम में क्रमबद्ध किया गया है, इसलिए हम -1 डालते हैं। और परिणाम टोक्यो है, क्योंकि जनसंख्या मूल्य (13,189) औसत मूल्य (000) के सबसे करीब है।
VLOOKUP() ऐसी गणना भी कर सकता है, लेकिन केवल एक सरणी सूत्र के रूप में: VLOOKUP औसत, मिन और मैक्स के साथ.
इंडेक्स मैच और ESND/IFERROR
आप शायद पहले ही देख चुके हैं कि यदि सूत्र वांछित मान नहीं पा सकता है, तो यह एक त्रुटि फेंकता है # N / A. आप मानक त्रुटि संदेश को कुछ अधिक जानकारीपूर्ण से बदल सकते हैं। उदाहरण के लिए, सूत्र में तर्क सेट करें XNUMXवीं में:
=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)
इस सूत्र के साथ, यदि आप डेटा दर्ज करते हैं जो तालिका में नहीं है, तो प्रपत्र आपको निर्दिष्ट संदेश देगा।
यदि आप सभी त्रुटियों को पकड़ना चाहते हैं, तो को छोड़कर XNUMXवीं में इस्तेमाल किया जा सकता है IFERROR:
=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "कुछ गलत हो गया!")
लेकिन याद रखें कि इस तरह से त्रुटियों को छुपाना एक अच्छा विचार नहीं है, क्योंकि मानक त्रुटियां सूत्र में उल्लंघन की रिपोर्ट करती हैं।
हमें उम्मीद है कि आपको INDEX MATCH () फ़ंक्शन का उपयोग करने में हमारी मार्गदर्शिका मददगार लगी होगी।