Index Match Excel: डेटा खोजने का बेहतर तरीका

INDEX MATCH, दो Excel फ़ंक्शनों INDEX() और MATCH() का संयोजन, सामान्यतः उपयोग होने वाले VLOOKUP() फ़ंक्शन का एक विकल्प है।

इस गाइड में, मैं आपको बताऊँगा कि INDEX() और MATCH() अलग-अलग और साथ में कैसे काम करते हैं, वर्टिकल और हॉरिज़ॉन्टल लुकअप कैसे करें, और व्यावहारिक उदाहरणों के साथ INDEX MATCH उपयोग करने के सर्वोत्तम तरीके क्या हैं।

Excel में INDEX MATCH क्या है?

INDEX MATCH इन दोनों फ़ंक्शनों को नेस्ट करने का संक्षिप्त तरीका है। यह INDEX(MATCH()) के विचार के बराबर है। आइए देखें कि दोनों अलग-अलग और साथ में कैसे काम करते हैं।

Excel में INDEX() फ़ंक्शन

Excel में INDEX() फ़ंक्शन किसी रेंज या टेबल के भीतर से किसी मान या मान के रेफ़रेंस को लौटाता है। आप इसे दो तरीकों से उपयोग कर सकते हैं: ऐरे फ़ॉर्म और रेफ़रेंस फ़ॉर्म।

ऐरे फ़ॉर्म

जब आप एक तय टेबल या ऐरे के साथ काम कर रहे हों, तो आप ऐरे फ़ॉर्म का उपयोग कर सकते हैं। इस फ़ॉर्मेट में, INDEX() आपके द्वारा बताए गए रो और कॉलम नंबर के आधार पर मान निकालता है। इसका सिंटैक्स है:

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

आइए इन आर्गुमेंट्स को समझें:

• array (आवश्यक) वह रेंज या ऐरे है जिसके साथ आप काम कर रहे हैं।

• row_num (आवश्यक) वह रो नंबर है जिससे मान लौटाना है।

• column_num (वैकल्पिक) वह कॉलम नंबर है जिससे मान लौटाना है।

हमें कम से कम row_num या column_num में से एक बताना आवश्यक है ताकि Excel मान का पता लगा सके।

हालाँकि, इस सिंटैक्स के साथ काम करते समय ध्यान रखने के लिए कुछ त्वरित नोट्स:

• यदि ऐरे में केवल एक रो या कॉलम है, तो आप row_num या column_num छोड़ सकते हैं।

• यदि row_num और column_num दोनों दिए गए हैं, तो INDEX वह मान लौटाता है जहाँ वे एक-दूसरे को काटते हैं।

• यदि आप row_num या column_num को 0 सेट करते हैं, तो Excel पूरा कॉलम या रो लौटाता है (परिणाम देखने के लिए आपको इसे ऐरे फ़ॉर्मूला के रूप में दर्ज करना होगा)।

रेफ़रेंस फ़ॉर्म

जब आप एक से अधिक रेंज के साथ काम कर रहे हों और यह बताने की आवश्यकता हो कि किस रेंज को देखना है, तो आप रेफ़रेंस फ़ॉर्म का उपयोग कर सकते हैं।

इसका सिंटैक्स है:

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

आइए इन आर्गुमेंट्स को समझें:

• reference (आवश्यक) एक या अधिक सेल रेंज को संदर्भित करने के लिए उपयोग होता है।

• row_num (आवश्यक) चुने गए एरिया में रो नंबर है।

• column_num (वैकल्पिक) चुने गए एरिया में कॉलम नंबर है।

• area_num (वैकल्पिक) यदि एक से अधिक रेंज हों, तो Excel को बताता है कि किस रेंज का उपयोग करना है।

यहाँ कुछ अतिरिक्त बिंदु हैं जिन्हें आपको याद रखना चाहिए:

• यदि area_num छोड़ दिया जाता है, तो Excel डिफ़ॉल्ट रूप से पहली रेंज का उपयोग करता है।

• आप इस फ़ॉर्म में अलग-अलग शीट्स की रेंज को मिला नहीं सकते; ऐसा करने पर त्रुटि आती है।

• यदि रो और कॉलम दोनों छोड़ दिए जाएँ, तो यह निर्दिष्ट पूरे एरिया को लौटाता है।

Excel में MATCH() फ़ंक्शन

MATCH() फ़ंक्शन आपको किसी रो या कॉलम में किसी मान की स्थिति खोजने में मदद करता है। मान स्वयं लौटाने के बजाय, यह बताता है कि सूची में वह कहाँ स्थित है।

इसका सिंटैक्स है:

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

आइए इन आर्गुमेंट्स को समझें:

• lookup_value (आवश्यक) वह मान है जिसे आप खोजना चाहते हैं। यह संख्या, टेक्स्ट, लॉजिकल वैल्यू (TRUE/FALSE) या किसी सेल का रेफ़रेंस हो सकता है।

• lookup_array (आवश्यक) वह सेल रेंज है जिसमें Excel खोज करेगा।

• match_type (वैकल्पिक) परिभाषित करता है कि Excel मिलान कैसे खोजे। डिफ़ॉल्ट 1 है। यहाँ 1 lookup_value से कम या बराबर सबसे बड़ा मान खोजता है (आरोही क्रम आवश्यक)। 0 सटीक मिलान खोजता है (कोई भी क्रम)। -1 lookup_value से अधिक या बराबर सबसे छोटा मान खोजता है (अवरोही क्रम आवश्यक)।

MATCH फ़ंक्शन केस-सेंसिटिव नहीं है, यानी यह अपरकेस और लोअरकेस अक्षरों को समान मानता है। यदि आप टेक्स्ट के साथ काम कर रहे हैं और * (किसी भी संख्या के अक्षर) या ? (एक अक्षर) जैसे वाइल्डकार्ड का उपयोग करना चाहते हैं, तो सुनिश्चित करें कि match_type को 0 पर सेट करें। क्योंकि वाइल्डकार्ड सर्च केवल इसी मोड में समर्थित है।

Excel में INDEX() और MATCH() साथ में कैसे काम करते हैं?

MATCH() का एक आम उपयोग इसे INDEX() फ़ंक्शन के साथ जोड़ना है। जहाँ MATCH() किसी मान की स्थिति खोजता है, वहीं INDEX() उस स्थिति पर मौजूद मान को प्राप्त करता है। 

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

उदाहरण के लिए, मेरे पास एक सैंपल डेटासेट है। और यदि मैं यह पता लगाना चाहूँ कि कॉलम B में वही रो क्या है जो Banana के साथ कॉलम A में है, तो मैं INDEX MATCH को इस तरह साथ में उपयोग कर सकता/सकती हूँ:

=INDEX(B2:B5, MATCH("Banana", A2:A5, 0))संचालित द्वारा

यहाँ MATCH("Banana", A2:A5, 0) कॉलम A में Banana की स्थिति ढूँढता है और INDEX(B2:B5, ...) कॉलम B में उसी रो से मान लौटाता है।

INDEX MATCH बनाम VLOOKUP(): आपको कौन-सा उपयोग करना चाहिए?

INDEX MATCH वे काम कर सकता है जो VLOOKUP() नहीं कर सकता। हालाँकि, इन दोनों में से चुनना इस पर निर्भर करता है कि आपकी Excel शीट्स में आपको कितनी लचीलापन और प्रदर्शन चाहिए। आइए उनकी तुलना करें।

कॉलम लचीलापन और डेटा अखंडता

VLOOKUP() की सबसे बड़ी कमियों में से एक है इसका स्थिर कॉलम नंबरों पर निर्भर होना। यदि आप कॉलम जोड़ते या स्थानांतरित करते हैं, तो आपका फ़ॉर्मूला टूट सकता है।

यह उदाहरण देखें:

यदि कॉलम A और कॉलम B के बीच एक नया कॉलम जोड़ा जाता है, तो तीसरा कॉलम खिसक जाता है, और फ़ॉर्मूला गलत परिणाम देता है, जैसा कि नीचे की छवि में दिख रहा है।

=VLOOKUP("Apple", A2:C5, 2, FALSE)संचालित द्वारा

INDEX MATCH इस समस्या से बचाता है क्योंकि यह हार्डकोडेड कॉलम पोज़िशनों के बजाय परिभाषित रेंजों के साथ काम करता है:

=INDEX(B2:B5, MATCH("Apple", A2:A5, 0))संचालित द्वारा

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

लुकअप की दिशा और बहुमुखी प्रतिभा

VLOOKUP() केवल बाएँ से दाएँ खोज सकता है। इसका मतलब है कि आपकी लुकअप कॉलम हमेशा आपकी रेंज में पहली होनी चाहिए। यदि आपको जो मान चाहिए, वह लुकअप कॉलम के बाएँ है, तो VLOOKUP() उसे नहीं पा सकता।

हालाँकि, यदि हम दाएँ तरफ़ रंग खोजने के लिए फल के नाम से लुकअप करें, तो यह फ़ॉर्मूला काम करेगा। उदाहरण के लिए, यदि हम अपनी सूची से केले का रंग जानना चाहें, तो यह सटीक परिणाम देता है: 

INDEX MATCH के साथ यह सीमा नहीं है। आप किसी भी दिशा में खोज सकते हैं: बाएँ, दाएँ, ऊपर, या नीचे। यह हॉरिज़ॉन्टल लुकअप का भी समर्थन करता है, जिससे आपको अपने डेटा को व्यवस्थित करने में अधिक नियंत्रण मिलता है। 

आइए इस उदाहरण पर INDEX MATCH लागू करें और देखें कि यह कैसे बाएँ और दाएँ दोनों लुकअप की अनुमति देता है।

बाएँ लुकअप के लिए आप निम्न फ़ॉर्मूला उपयोग कर सकते हैं, और यह सटीक आउटपुट देगा: 

=INDEX(A2:A5, MATCH(“yellow”, B2:B5,0))संचालित द्वारा

दाएँ लुकअप के लिए आप निम्न फ़ॉर्मूला उपयोग कर सकते हैं, और यह सही आउटपुट देगा: 

=INDEX(B2:B5, MATCH(“Banana”,A2:A5,0))संचालित द्वारा

Excel में प्रदर्शन और दक्षता

VLOOKUP() एक ही बार में मिलान खोजने और मान प्राप्त करने के लिए पूरी टेबल स्कैन करता है। लेकिन INDEX MATCH कार्य को विभाजित करता है: MATCH() रो या कॉलम ढूँढता है, और INDEX() मान प्राप्त करता है। 

यह इसे तेज़ और मेमोरी-कुशल बनाता है, खासकर जटिल या हाई-वॉल्यूम वर्कबुक्स में।

यहाँ एक तालिका है जो आपको यह समझने में मदद करेगी कि कौन-सा फ़ंक्शन किस सुविधा का समर्थन करता है:

INDEX MATCH कब उपयोग करें

आप INDEX MATCH तब उपयोग कर सकते हैं जब:

• आप बड़े डेटा सेट्स के साथ काम कर रहे हों जहाँ गति मायने रखती है
• आपका मॉडल डायनेमिक हो, जिसमें कॉलम जोड़े या पुनर्व्यवस्थित किए जाते हों
• आपको बाएँ की ओर या हॉरिज़ॉन्टल रेंज में लुकअप करने की ज़रूरत हो

यदि आपकी Excel फ़ाइलें छोटी और सरल हैं, तो VLOOKUP() अब भी काम कर सकता है। लेकिन उन्नत मॉडलिंग और रिपोर्टिंग के लिए, INDEX MATCH अधिक सुरक्षित विकल्प है।

INDEX MATCH का उपयोग चरण-दर-चरण कैसे करें

मान लीजिए आपके पास दो कॉलम हैं: Fruits और Sales। आप “Apple” के लिए बिक्री मान खोजना चाहते हैं।

इसके लिए, संयुक्त INDEX MATCH फ़ॉर्मूला टाइप करें और Enter दबाएँ:

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

यहाँ MATCH("Apple", A2:A6, 0) Fruits सूची में “Apple” की स्थिति खोजता है। और INDEX(B2:B6, ...) उस स्थिति का उपयोग करके Sales कॉलम से संबंधित बिक्री मान लौटाता है।

आप देख सकते हैं कि फ़ॉर्मूला ने 12000 लौटाया, जो Apple के लिए बिक्री मान है। यही आपका परिणाम है।

INDEX MATCH त्रुटियों से बचना

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

#N/A त्रुटियाँ और उन्हें कैसे सुलझाएँ

#N/A त्रुटि का मतलब है कि Excel वह मान नहीं ढूँढ सका जिसे आप खोज रहे हैं। इसके कुछ आम कारण हो सकते हैं:

• टाइपो, अतिरिक्त स्पेस, या असंगत टेक्स्ट (जैसे "apple" बनाम "Apple")

• लुकअप रेंज में मान का न होना

• गलत match_type, जैसे सटीक मिलान के लिए 0 की जगह 1

• डेटा में छिपे हुए कैरेक्टर्स (जैसे नॉन-ब्रेकिंग स्पेसेज़)

इसे ठीक करने के दो तरीके हैं:

लीडिंग या ट्रेलिंग स्पेस साफ़ करने के लिए TRIM() का उपयोग करें:

=MATCH(TRIM("Apple"), A2:A6, 0)संचालित द्वारा

त्रुटि के बजाय एक फ़ॉलबैक मान दिखाने के लिए IFERROR() जोड़ें:

=IFERROR(INDEX(B2:B6, MATCH("Apple", A2:A6, 0)), "Not found")संचालित द्वारा

INDEX MATCH फ़ॉर्मूलों में #REF! त्रुटियाँ

#REF! त्रुटि आमतौर पर किसी टूटे हुए रेफ़रेंस की ओर इशारा करती है—अक्सर इन कारणों से:

• आपकी INDEX() और MATCH() रेंजों में रो की संख्या में असमानता

• आपके फ़ॉर्मूला में उपयोग की गई रो या कॉलम का हटाया जाना

• सेल रेफ़रेंस अपडेट किए बिना फ़ॉर्मूला का कॉपी-पेस्ट करना

लेकिन इसे ठीक करने के कुछ आसान तरीके हैं: 

• सुनिश्चित करें कि INDEX() और MATCH() रेंज एक ही आकार की हों:

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

• रेंज को एब्सोल्यूट रेफ़रेंस के साथ लॉक करें (जैसे $A$2:$A$6) ताकि कॉपी-पेस्ट के दौरान फ़ॉर्मूले स्थिर रहें।

• उन सेल्स को डिलीट करने से बचें जिन पर फ़ॉर्मूले निर्भर हैं।

धीमा प्रदर्शन और बड़े डेटा सेट्स

यदि आप हज़ारों रो के साथ काम कर रहे हैं, तो अनुकूलित न किए गए INDEX MATCH फ़ॉर्मूले Excel को धीमा कर सकते हैं।

इसे अनुकूलित करने के तरीके:

• पूरे कॉलम रेफ़रेंस करने के बजाय लुकअप रेंज सीमित करें:

=INDEX(B2:B1000, MATCH("Apple", A2:A1000, 0))संचालित द्वारा

• OFFSET(), INDIRECT(), और NOW() जैसे वोलाटाइल फ़ंक्शनों से अपने लुकअप्स के पास बचें।

• Excel टेबल्स या नामित रेंज का उपयोग करें ताकि चीज़ें संगठित रहें और प्रदर्शन बेहतर हो।

Excel 365 में स्पिल त्रुटियों का प्रबंधन

Excel 365 डायनेमिक ऐरे पेश करता है, जो सटे हुए सेल्स में परिणाम “स्पिल” कर सकते हैं। यदि उस प्रक्रिया में कोई समस्या हो, तो आपको #SPILL! त्रुटि मिलती है।

कुछ आम कारण हो सकते हैं:

• आसपास के सेल खाली नहीं हैं।
• फ़ॉर्मूला ब्लॉक या मर्ज्ड रेंज में एक से अधिक मान लौटाता है।
• जहाँ केवल एक मान चाहिए, वहाँ आप डायनेमिक ऐरे का उपयोग कर रहे हैं।

लेकिन ये समस्याएँ ठीक की जा सकती हैं। इन्हें दूर करने के तरीके:

• फ़ॉर्मूला के नीचे या बगल के सेल साफ़ करें।
• स्पिल एरिया में किसी भी सेल को अनमर्ज करें।
• यदि आपको केवल एक मान चाहिए, तो @ के साथ सिंगल रिज़ल्ट फ़ोर्स करें:

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

अंतिम विचार

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