एकाधिक मापदंड के साथ एक्सेल लुकअप सूत्र कैसे बनाएं

ट्यूटोरियल के साथ अनुसरण करें

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

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

एक्सेल में INDEX फ़ंक्शन बनाएं

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

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

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

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

2. फॉर्मूला पर जाएं।

   

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

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

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

6. फंक्शन आर्ग्यूमेंट्स डायलॉग बॉक्स खोलने के लिए ओके चुनें। एक्सेल फॉर मैक में, फॉर्मूला बिल्डर खुलता है।

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

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

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

   

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

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

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

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

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

1. फंक्शन आर्ग्यूमेंट्स डायलॉग बॉक्स में, Row_num टेक्स्ट बॉक्स में कर्सर रखें।

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

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

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

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

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

   

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

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

यह चरण नेस्टेड MATCH फ़ंक्शन के लिए Lookup_array तर्क जोड़ने को कवर करता है। Lookup_array वह सेल रेंज है जिसे MATCH फ़ंक्शन पिछले चरण में जोड़े गए Lookup_value तर्क को खोजने के लिए खोजता है।

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

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

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

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

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

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

   

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

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

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

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

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

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

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

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

   

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

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

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

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

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

   

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

सरणी सूत्र बनाएं

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

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

1. डायलॉग बॉक्स बंद करने के लिए ओके चुनें। एक्सेल फॉर मैक में, डन चुनें।

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

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

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

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

   

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

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

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

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

2. Widgets दर्ज करें।

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

4. Titanium टाइप करें, और एंटर दबाएं।

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

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

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

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