अनेक मानदंडों के साथ एक्सेल लुकअप फॉर्मूला कैसे बनाएं

क्या जानना है

• सबसे पहले, एक इंडेक्स फ़ंक्शन बनाएं, फिर लुकअप_वैल्यू तर्क दर्ज करके नेस्टेड MATCH फ़ंक्शन प्रारंभ करें।
• अगला, Lookup_array तर्क और उसके बाद Match_type . जोड़ें तर्क, फिर कॉलम श्रेणी निर्दिष्ट करें।
• फिर, Ctrl . दबाकर नेस्टेड फ़ंक्शन को सरणी सूत्र में बदलें +शिफ्ट +दर्ज करें . अंत में, कार्यपत्रक में खोज शब्द जोड़ें।

यह आलेख बताता है कि किसी सरणी सूत्र का उपयोग करके किसी डेटाबेस या डेटा तालिका में जानकारी खोजने के लिए Excel में एकाधिक मानदंड का उपयोग करने वाला लुकअप सूत्र कैसे बनाया जाए। सरणी सूत्र में INDEX फ़ंक्शन के अंदर MATCH फ़ंक्शन को नेस्ट करना शामिल है। सूचना में Microsoft 365 के लिए Excel, Excel 2019, Excel 2016, Excel 2013, Excel 2010 और Mac के लिए Excel शामिल हैं।

ट्यूटोरियल के साथ फॉलो करें

इस ट्यूटोरियल के चरणों का पालन करने के लिए, नमूना डेटा को निम्न कक्षों में दर्ज करें, जैसा कि नीचे की छवि में दिखाया गया है। इस ट्यूटोरियल के दौरान बनाए गए सरणी सूत्र को समायोजित करने के लिए पंक्तियों 3 और 4 को खाली छोड़ दिया गया है। (ध्यान दें कि इस ट्यूटोरियल में इमेज में दिखाई देने वाली फ़ॉर्मेटिंग शामिल नहीं है।)

• डेटा की शीर्ष श्रेणी को कक्ष D1 से F2 में दर्ज करें।
• सेल D5 से F11 में दूसरी श्रेणी दर्ज करें।

एक्सेल में एक इंडेक्स फंक्शन बनाएं

INDEX फंक्शन एक्सेल के कुछ फंक्शन्स में से एक है जिसमें कई फॉर्म होते हैं। फ़ंक्शन में एक ऐरे फॉर्म और एक संदर्भ फॉर्म है। ऐरे फॉर्म डेटाबेस या डेटा की तालिका से डेटा लौटाता है। संदर्भ प्रपत्र तालिका में सेल संदर्भ या डेटा का स्थान देता है।

इस ट्यूटोरियल में, डेटाबेस में इस आपूर्तिकर्ता के सेल संदर्भ के बजाय, टाइटेनियम विजेट के लिए आपूर्तिकर्ता का नाम खोजने के लिए ऐरे फॉर्म का उपयोग किया जाता है।

INDEX फ़ंक्शन बनाने के लिए इन चरणों का पालन करें:

1. सेल चुनें F3 इसे सक्रिय सेल बनाने के लिए। यह सेल वह जगह है जहां नेस्टेड फ़ंक्शन दर्ज किया जाएगा।

2. सूत्र . पर जाएं ।

   

3. लुकअप और संदर्भ चुनें फ़ंक्शन ड्रॉप-डाउन सूची खोलने के लिए।

4. इंडेक्स Select चुनें तर्क चुनें . खोलने के लिए डायलॉग बॉक्स।

5. सरणी, row_num,column_num Choose चुनें ।

6. ठीक Select चुनें फ़ंक्शन तर्क . खोलने के लिए संवाद बकस। मैक के लिए एक्सेल में फॉर्मूला बिल्डर खुलता है।

7. कर्सर को सरणी . में रखें टेक्स्ट बॉक्स।

8. सेल हाइलाइट करें D6 F11 . के माध्यम से वर्कशीट में डायलॉग बॉक्स में रेंज दर्ज करने के लिए।

   फ़ंक्शन तर्क संवाद बॉक्स को खुला छोड़ दें। सूत्र समाप्त नहीं हुआ है। आप नीचे दिए गए निर्देशों में सूत्र को पूरा करेंगे।

   

नेस्टेड MATCH फंक्शन शुरू करें

एक फ़ंक्शन को दूसरे के अंदर नेस्ट करते समय, आवश्यक तर्कों को दर्ज करने के लिए दूसरे, या नेस्टेड, फ़ंक्शन के फॉर्मूला बिल्डर को खोलना संभव नहीं है। नेस्टेड फ़ंक्शन को पहले फ़ंक्शन के तर्कों में से एक के रूप में दर्ज किया जाना चाहिए।

मैन्युअल रूप से फ़ंक्शन दर्ज करते समय, फ़ंक्शन के तर्क एक दूसरे से अल्पविराम द्वारा अलग किए जाते हैं।

नेस्टेड MATCH फ़ंक्शन में प्रवेश करने का पहला चरण लुकअप_वैल्यू तर्क दर्ज करना है। लुकअप_वैल्यू डेटाबेस में मिलान किए जाने वाले खोज शब्द के लिए स्थान या सेल संदर्भ है।

लुकअप_वैल्यू केवल एक खोज मानदंड या पद को स्वीकार करता है। एकाधिक मानदंड खोजने के लिए, एम्परसेंड प्रतीक (&) का उपयोग करके दो या दो से अधिक सेल संदर्भों को जोड़कर, या जुड़कर लुकअप_वैल्यू का विस्तार करें।

1. कार्य तर्कों . में डायलॉग बॉक्स में, कर्सर को Row_num . में रखें टेक्स्ट बॉक्स।

2. MATCH( दर्ज करें ।

3. सेल चुनें D3 उस सेल संदर्भ को डायलॉग बॉक्स में दर्ज करने के लिए।

4. & दर्ज करें (एम्पर्सेंड) सेल संदर्भ के बाद D3 दूसरा सेल संदर्भ जोड़ने के लिए।

5. सेल चुनें E3 दूसरा सेल संदर्भ दर्ज करने के लिए।

6. दर्ज करें , (एक अल्पविराम) MATCH फ़ंक्शन के Lookup_value तर्क की प्रविष्टि को पूरा करने के लिए सेल संदर्भ E3 के बाद।

   

   ट्यूटोरियल के अंतिम चरण में, लुकअप_वैल्यू को वर्कशीट के सेल D3 और E3 में दर्ज किया जाएगा।

नेस्टेड MATCH फंक्शन को पूरा करें

इस चरण में नेस्टेड MATCH फ़ंक्शन के लिए Lookup_array तर्क जोड़ना शामिल है। लुकअप_एरे उन कक्षों की श्रेणी है जिन्हें MATCH फ़ंक्शन ट्यूटोरियल के पिछले चरण में जोड़े गए लुकअप_वैल्यू तर्क को खोजने के लिए खोजता है।

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

1. डेटा के अंत में कर्सर को Row_num . में रखें पाठ बॉक्स। वर्तमान प्रविष्टि के अंत में अल्पविराम के बाद कर्सर दिखाई देता है।

2. सेल हाइलाइट करें D6 D11 . के माध्यम से कार्यपत्रक में श्रेणी में प्रवेश करने के लिए। यह श्रेणी पहली सरणी है जिसे फ़ंक्शन खोजता है।

3. & दर्ज करें (एक एम्परसेंड) सेल संदर्भ के बाद D6:D11 . यह प्रतीक फ़ंक्शन को दो सरणियों की खोज करने का कारण बनता है।

4. सेल हाइलाइट करें E6 E11 . के माध्यम से कार्यपत्रक में श्रेणी में प्रवेश करने के लिए। यह श्रेणी दूसरी सरणी है जिसे फ़ंक्शन खोजता है।

5. , Enter दर्ज करें (एक अल्पविराम) सेल संदर्भ के बाद E3 MATCH फ़ंक्शन के लुकअप_एरे तर्क की प्रविष्टि को पूरा करने के लिए।

   

6. ट्यूटोरियल में अगले चरण के लिए डायलॉग बॉक्स खुला छोड़ दें।

MATCH प्रकार तर्क जोड़ें

. का तीसरा और अंतिम तर्क MATCH फ़ंक्शन Match_type है तर्क। यह तर्क एक्सेल को बताता है कि कैसे लुकअप_वेल्यू को लुकअप_एरे में मानों के साथ मिलाना है। उपलब्ध विकल्प 1, 0, या -1 हैं।

यह तर्क वैकल्पिक है। यदि इसे छोड़ दिया जाता है, तो फ़ंक्शन 1 के डिफ़ॉल्ट मान का उपयोग करता है।

• यदि Match_type =1 या छोड़ दिया जाता है, तो MATCH को सबसे बड़ा मान मिलता है जो Lookup_value से कम या उसके बराबर होता है। लुकअप_एरे डेटा को आरोही क्रम में क्रमबद्ध किया जाना चाहिए।
• यदि Match_type =0, MATCH को पहला मान मिलता है जो Lookup_value के बराबर है। लुकअप_एरे डेटा को किसी भी क्रम में क्रमबद्ध किया जा सकता है।
• यदि Match_type =-1, MATCH सबसे छोटा मान ढूँढता है जो Lookup_value से बड़ा या उसके बराबर है। लुकअप_एरे डेटा को अवरोही क्रम में क्रमबद्ध किया जाना चाहिए।

INDEX फ़ंक्शन में Row_num लाइन पर पिछले चरण में अल्पविराम दर्ज करने के बाद इन चरणों को दर्ज करें:

1. 0 दर्ज करें (एक शून्य) Row_num . में अल्पविराम के बाद पाठ बॉक्स। यह संख्या नेस्टेड फ़ंक्शन को कक्ष D3 और E3 में दर्ज शर्तों से सटीक मिलान करने का कारण बनती है।

2. दर्ज करें ) (एक क्लोजिंग राउंड ब्रैकेट) MATCH फंक्शन को पूरा करने के लिए।

   

3. ट्यूटोरियल में अगले चरण के लिए डायलॉग बॉक्स खुला छोड़ दें।

INDEX फ़ंक्शन समाप्त करें

MATCH फ़ंक्शन किया जाता है। यह डायलॉग बॉक्स के Column_num टेक्स्ट बॉक्स में जाने और INDEX फ़ंक्शन के लिए अंतिम तर्क दर्ज करने का समय है। यह तर्क एक्सेल को बताता है कि स्तंभ संख्या D6 से F11 की श्रेणी में है। यह रेंज वह जगह है जहां यह फ़ंक्शन द्वारा लौटाई गई जानकारी ढूंढती है। इस मामले में, टाइटेनियम विजेट के लिए एक आपूर्तिकर्ता।

1. कर्सर को Column_num . में रखें टेक्स्ट बॉक्स।

2. 3 दर्ज करें (नंबर तीन)। यह संख्या D6 से F11 की श्रेणी के तीसरे कॉलम में डेटा देखने के लिए सूत्र बताती है।

   

3. ट्यूटोरियल में अगले चरण के लिए डायलॉग बॉक्स खुला छोड़ दें।

ऐरे फॉर्मूला बनाएं

डायलॉग बॉक्स बंद करने से पहले, नेस्टेड फ़ंक्शन को एक सरणी सूत्र में बदल दें। यह सरणी फ़ंक्शन को डेटा की तालिका में एकाधिक शब्दों की खोज करने की अनुमति देती है। इस ट्यूटोरियल में, दो शब्दों का मिलान किया गया है:कॉलम 1 से विजेट और कॉलम 2 से टाइटेनियम।

Excel में सरणी सूत्र बनाने के लिए, CTRL press दबाएं , SHIFT , और ENTER एक साथ चाबियाँ। एक बार दबाए जाने पर, फ़ंक्शन घुंघराले ब्रेसिज़ से घिरा होता है, जो दर्शाता है कि फ़ंक्शन अब एक सरणी है।

1. ठीक Select चुनें डायलॉग बॉक्स को बंद करने के लिए। Mac के लिए Excel में, संपन्न select चुनें ।

2. सेल चुनें F3 फ़ॉर्मूला देखने के लिए, फ़ॉर्मूला बार में सूत्र के अंत में कर्सर रखें।

3. सूत्र को सरणी में बदलने के लिए, CTRL press दबाएं +SHIFT +ENTER ।

4. ए #N/A सेल F3 में त्रुटि दिखाई देती है। यह वह सेल है जहां फ़ंक्शन दर्ज किया गया था।

5. सेल F3 में #N/A त्रुटि दिखाई देती है क्योंकि सेल D3 और E3 खाली हैं। D3 और E3 वे सेल हैं जहां फ़ंक्शन लुकअप_वैल्यू को ढूंढता है। इन दो कक्षों में डेटा जोड़ने के बाद, त्रुटि को डेटाबेस से जानकारी द्वारा बदल दिया जाता है।

   

खोज मानदंड जोड़ें

अंतिम चरण कार्यपत्रक में खोज शब्द जोड़ना है। यह चरण कॉलम 1 और . से विजेट्स की शर्तों से मेल खाता है कॉलम 2 से टाइटेनियम।

यदि सूत्र डेटाबेस में उपयुक्त कॉलम में दोनों शब्दों के लिए एक मेल पाता है, तो यह तीसरे कॉलम से मान लौटाता है।

1. सेल चुनें D3 ।

2. विजेट दर्ज करें ।

3. सेल चुनें E3 ।

4. टाइप करें टाइटेनियम , और Enter . दबाएं ।

5. आपूर्तिकर्ता का नाम, विजेट्स इंक., सेल F3 में दिखाई देता है। यह सूचीबद्ध एकमात्र आपूर्तिकर्ता है जो टाइटेनियम विजेट बेचता है।

6. सेल चुनें F3 . कार्यपत्रक के ऊपर सूत्र पट्टी में फ़ंक्शन दिखाई देता है।

   {=INDEX(D6:F11,MATCH(D3&E3,D6:D11&E6:E11,0),3)}
   
   

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