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

Excel में एक सरणी सूत्र का उपयोग करके हम एक लुकअप फॉर्मूला बना सकते हैं जो किसी डेटाबेस या डेटा की तालिका में जानकारी खोजने के लिए एकाधिक मानदंडों का उपयोग करता है।

सरणी सूत्र में INDEX फ़ंक्शन के अंदर MATCH फ़ंक्शन को घोंसले करना शामिल है।

इस ट्यूटोरियल में लुकअप फॉर्मूला बनाने का एक चरण-दर-चरण उदाहरण शामिल है जो नमूना डेटाबेस में टाइटेनियम विजेट्स के आपूर्तिकर्ता को खोजने के लिए कई मानदंडों का उपयोग करता है।

नीचे दिए गए ट्यूटोरियल विषयों में दिए गए चरणों के बाद उपर्युक्त छवि में बनाए गए फॉर्मूला को बनाने और उपयोग करके आपको चलता है।

09 का 01

ट्यूटोरियल डेटा दर्ज करना

ट्यूटोरियल में पहला चरण डेटा वर्कशीट में डेटा दर्ज करना है।

ट्यूटोरियल में चरणों का पालन करने के लिए उपरोक्त छवि में उपरोक्त डेटा को निम्न कक्षों में दर्ज करें ।

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

इस ट्यूटोरियल के दौरान बनाए गए सरणी सूत्र को समायोजित करने के लिए पंक्तियों 3 और 4 को खाली छोड़ दिया गया है।

ट्यूटोरियल में छवि में स्वरूपित स्वरूपण शामिल नहीं है, लेकिन यह इस बात को प्रभावित नहीं करेगा कि लुकअप फॉर्मूला कैसे काम करता है।

ऊपर दिए गए लोगों के समान प्रारूपण विकल्पों पर जानकारी इस मूल एक्सेल स्वरूपण ट्यूटोरियल में उपलब्ध है।

02 में से 02

इंडेक्स फ़ंक्शन शुरू करना

INDEX फ़ंक्शन एक्सेल में कुछ में से एक है जिसमें कई रूप हैं। समारोह में एक ऐरे फॉर्म और एक संदर्भ प्रपत्र है ।

ऐरे फॉर्म डेटा या डेटाबेस की तालिका से वास्तविक डेटा देता है, जबकि संदर्भ फ़ॉर्म आपको तालिका में डेटा संदर्भ या डेटा का स्थान देता है।

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

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

ट्यूटोरियल कदम

1. इसे सक्रिय सेल बनाने के लिए सेल F3 पर क्लिक करें। यह वह जगह है जहां हम नेस्टेड फ़ंक्शन दर्ज करेंगे।
2. रिबन मेनू के सूत्र टैब पर क्लिक करें।
3. फ़ंक्शन ड्रॉप डाउन सूची खोलने के लिए रिबन से लुकअप और संदर्भ चुनें।
4. चयन तर्क संवाद बॉक्स लाने के लिए सूची में INDEX पर क्लिक करें ।
5. संवाद बॉक्स में सरणी, row_num, col_num विकल्प चुनें।
6. INDEX फ़ंक्शन संवाद बॉक्स खोलने के लिए ओके पर क्लिक करें।

03 का 03

INDEX फ़ंक्शन ऐरे तर्क दर्ज करना

आवश्यक पहला तर्क ऐरे तर्क है। यह तर्क वांछित डेटा के लिए खोज की जाने वाली कोशिकाओं की सीमा निर्दिष्ट करता है।

इस ट्यूटोरियल के लिए यह तर्क हमारे नमूना डेटाबेस होगा ।

ट्यूटोरियल कदम

1. INDEX फ़ंक्शन संवाद बॉक्स में , ऐरे लाइन पर क्लिक करें।
2. डायलॉग बॉक्स में रेंज दर्ज करने के लिए वर्कशीट में सेल्स डी 6 से F11 को हाइलाइट करें।

04 का 04

नेस्टेड मैच फ़ंक्शन शुरू करना

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

नेस्टेड फ़ंक्शन को पहले फ़ंक्शन के तर्कों में से एक के रूप में टाइप किया जाना चाहिए।

इस ट्यूटोरियल में, नेस्टेड MATCH फ़ंक्शन और इसके तर्क INDEX फ़ंक्शन संवाद बॉक्स - Row_num लाइन की दूसरी पंक्ति में दर्ज किए जाएंगे।

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

MATCH फ़ंक्शन के लुकअप_वैल्यू तर्क दर्ज करना

नेस्टेड MATCH फ़ंक्शन में प्रवेश करने का पहला चरण Lookup_value तर्क दर्ज करना है।

Lookup_value उस खोज शब्द के लिए स्थान या सेल संदर्भ होगा जिसे हम डेटाबेस में मिलान करना चाहते हैं।

आम तौर पर Lookup_value केवल एक खोज मानदंड या शब्द स्वीकार करता है। एकाधिक मानदंडों को खोजने के लिए, हमें Lookup_value का विस्तार करना होगा।

यह एम्परसैंड प्रतीक " और " का उपयोग करके दो या दो से अधिक सेल संदर्भों को एक साथ जोड़कर या जोड़कर किया जाता है।

ट्यूटोरियल कदम

1. INDEX फ़ंक्शन संवाद बॉक्स में, Row_num लाइन पर क्लिक करें।
2. एक खुले राउंड ब्रैकेट के बाद फ़ंक्शन नाम मिलान टाइप करें " ( "
3. संवाद बॉक्स में उस सेल संदर्भ को दर्ज करने के लिए सेल D3 पर क्लिक करें।
4. दूसरा सेल संदर्भ जोड़ने के लिए सेल संदर्भ डी 3 के बाद एक एम्पर्सेंड टाइप करें।
5. संवाद बॉक्स में इस दूसरे सेल संदर्भ को दर्ज करने के लिए सेल E3 पर क्लिक करें।
6. MATCH फ़ंक्शन के Lookup_value तर्क की प्रविष्टि को पूरा करने के लिए सेल संदर्भ E3 के बाद " कॉमा टाइप करें " ।
7. ट्यूटोरियल में अगले चरण के लिए INDEX फ़ंक्शन संवाद बॉक्स खोलें।

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

05 में से 05

MATCH फ़ंक्शन के लिए Lookup_array जोड़ना

इस चरण में नेस्टेड MATCH फ़ंक्शन के लिए Lookup_array तर्क जोड़ना शामिल है।

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

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

एकाधिक सरणी दर्ज करने के लिए हम फिर से एरे को एक साथ जोड़ने के लिए एम्पर्सेंड " & " का उपयोग करते हैं।

ट्यूटोरियल कदम

INDEX फ़ंक्शन संवाद बॉक्स में Row_num लाइन पर पिछले चरण में दर्ज अल्पविराम के बाद इन चरणों को दर्ज किया जाना है।

1. वर्तमान प्रविष्टि के अंत में सम्मिलन बिंदु रखने के लिए अल्पविराम के बाद Row_num लाइन पर क्लिक करें।
2. सीमा दर्ज करने के लिए वर्कशीट में सेल्स D6 से D11 को हाइलाइट करें। यह पहला सरणी है जो फ़ंक्शन खोजना है।
3. सेल संदर्भों के बाद एक एम्पर्सेंड टाइप करें " & " D6: D11 क्योंकि हम फ़ंक्शन को दो सरणी खोजना चाहते हैं।
4. सीमा दर्ज करने के लिए वर्कशीट में सेल्स E6 से E11 को हाइलाइट करें। यह दूसरा सरणी है जो फ़ंक्शन खोजना है।
5. MATCH फ़ंक्शन के Lookup_array तर्क की प्रविष्टि को पूरा करने के लिए सेल संदर्भ E3 के बाद " कॉमा टाइप करें " ।
6. ट्यूटोरियल में अगले चरण के लिए INDEX फ़ंक्शन संवाद बॉक्स खोलें।

06 का 06

मिलान प्रकार जोड़ना और MATCH फ़ंक्शन को पूरा करना

MATCH फ़ंक्शन का तीसरा और अंतिम तर्क Match_type तर्क है।

यह तर्क Excel को Lookup_valray में मूल्यों के साथ Lookup_value से मिलान करने का तरीका बताता है। विकल्प हैं: 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 से अधिक या बराबर है। लुकअप_एरे डेटा को अवरोही क्रम में क्रमबद्ध किया जाना चाहिए।

ट्यूटोरियल कदम

INDEX फ़ंक्शन संवाद बॉक्स में Row_num लाइन पर पिछले चरण में दर्ज अल्पविराम के बाद इन चरणों को दर्ज किया जाना है।

1. Row_num लाइन पर अल्पविराम के बाद, शून्य " 0 " टाइप करें क्योंकि हम चाहते हैं कि नेस्टेड फ़ंक्शन सेल्स डी 3 और ई 3 में दर्ज शर्तों के सटीक मिलान वापस कर दें।
2. MATCH फ़ंक्शन को पूरा करने के लिए एक समापन राउंड ब्रैकेट टाइप करें " ) "।
3. ट्यूटोरियल में अगले चरण के लिए INDEX फ़ंक्शन संवाद बॉक्स खोलें।

07 का 07

INDEX फ़ंक्शन पर वापस जाएं

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

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

ट्यूटोरियल कदम

1. संवाद बॉक्स में Column_num लाइन पर क्लिक करें।
2. इस लाइन पर नंबर तीन " 3 " (कोई उद्धरण) दर्ज करें क्योंकि हम श्रेणी D6 से F11 के तीसरे कॉलम में डेटा की तलाश में हैं।
3. ठीक क्लिक न करें या INDEX फ़ंक्शन संवाद बॉक्स को बंद न करें। यह ट्यूटोरियल में अगले चरण के लिए खुला रहता है - सरणी सूत्र बना रहा है ।

08 का 08

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

संवाद बॉक्स को बंद करने से पहले हमें अपने नेस्टेड फ़ंक्शन को सरणी सूत्र में बदलना होगा।

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

Excel में एक सरणी सूत्र बनाना एक ही समय में कीबोर्ड पर CTRL , SHIFT , और ENTER कुंजी दबाकर किया जाता है।

इन चाबियों को एक साथ दबाए जाने का प्रभाव घुंघराले ब्रेसिज़ के साथ फ़ंक्शन को घेरना है: {} यह इंगित करता है कि यह अब एक सरणी सूत्र है।

ट्यूटोरियल कदम

1. पूरा ट्यूटोरियल बॉक्स अभी भी इस ट्यूटोरियल के पिछले चरण से खुलता है, कीबोर्ड पर CTRL और SHIFT कुंजी दबाकर दबाए रखें और फिर एंटर कुंजी दबाएं और छोड़ दें।
2. यदि सही तरीके से किया जाता है, तो संवाद बॉक्स बंद हो जाएगा और सेल F3 में एक # एन / ए त्रुटि दिखाई देगी - वह कक्ष जहां हमने फ़ंक्शन दर्ज किया था।
3. सेल एफ 3 में # एन / ए त्रुटि दिखाई देती है क्योंकि कक्ष डी 3 और ई 3 खाली हैं। डी 3 और ई 3 वे कक्ष हैं जहां हमने ट्यूटोरियल के चरण 5 में Lookup_values ​​को खोजने के लिए फ़ंक्शन को बताया था। एक बार इन दो कोशिकाओं में डेटा जोड़ा जाता है, तो त्रुटि डेटाबेस से जानकारी द्वारा प्रतिस्थापित किया जाएगा।

09 में से 09

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

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

जैसा कि पिछले चरण में बताया गया है, हम कॉलम 1 से टाइम्सियम और कॉलम 2 से टाइम्सियम से मेल खाते हैं।

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

ट्यूटोरियल कदम

1. सेल डी 3 पर क्लिक करें।
2. विजेट टाइप करें और कीबोर्ड पर एंटर कुंजी दबाएं।
3. सेल ई 3 पर क्लिक करें।
4. टाइटेनियम टाइप करें और कीबोर्ड पर एंटर कुंजी दबाएं।
5. आपूर्तिकर्ता का नाम विजेट इंक सेल एफ 3 में दिखाई देना चाहिए - फ़ंक्शन का स्थान क्योंकि यह एकमात्र सप्लायर सूचीबद्ध है जो टाइटेनियम विजेट बेचता है।
6. जब आप सेल F3 पर पूर्ण फ़ंक्शन पर क्लिक करते हैं
   {= INDEX (डी 6: एफ 11, MATCH (डी 3 और ई 3, डी 6: डी 11 और ई 6: ई 11, 0), 3)}
   वर्कशीट के ऊपर सूत्र बार में दिखाई देता है।

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