एक्सेल के VLOOKUP फ़ंक्शन को COLUMN फ़ंक्शन के साथ जोड़कर आप एक लुकअप फॉर्मूला बना सकते हैं जो डेटाबेस या डेटा की तालिका की एक पंक्ति से कई मान लौटाता है। एक डेटा रिकॉर्ड से कई मान लौटाने वाला लुकअप फ़ॉर्मूला बनाने का तरीका जानें.
इस आलेख में निर्देश एक्सेल 2019, 2016, 2013, 2010 पर लागू होते हैं; और माइक्रोसॉफ्ट 365 के लिए एक्सेल।
नीचे की रेखा
लुकअप सूत्र के लिए आवश्यक है कि COLUMN फ़ंक्शन VLOOKUP के अंदर नेस्ट किया जाए। किसी फ़ंक्शन को नेस्ट करना पहले फ़ंक्शन के तर्कों में से एक के रूप में दूसरे फ़ंक्शन को दर्ज करना शामिल है।
ट्यूटोरियल डेटा दर्ज करें
इस ट्यूटोरियल में, कॉलम फ़ंक्शन को VLOOKUP के लिए कॉलम इंडेक्स नंबर तर्क के रूप में दर्ज किया गया है। ट्यूटोरियल के अंतिम चरण में चुने हुए भाग के लिए अतिरिक्त मान प्राप्त करने के लिए लुकअप फॉर्मूला को अतिरिक्त कॉलम में कॉपी करना शामिल है।
इस ट्यूटोरियल में पहला कदम डेटा को एक्सेल वर्कशीट में दर्ज करना है। इस ट्यूटोरियल के चरणों का पालन करने के लिए, नीचे दी गई छवि में दिखाए गए डेटा को निम्नलिखित कक्षों में दर्ज करें:
- डेटा की शीर्ष श्रेणी को कक्ष D1 से G1 में दर्ज करें।
- सेल D4 से G10 में दूसरी श्रेणी दर्ज करें।
इस ट्यूटोरियल में बनाए गए खोज मानदंड और लुकअप फॉर्मूला वर्कशीट की पंक्ति 2 में दर्ज किए गए हैं।
इस ट्यूटोरियल में इमेज में दिखाया गया बेसिक एक्सेल फॉर्मेटिंग शामिल नहीं है, लेकिन यह लुकअप फॉर्मूला के काम करने के तरीके को प्रभावित नहीं करता है।
डेटा तालिका के लिए एक नामांकित श्रेणी बनाएं
एक नामित श्रेणी किसी सूत्र में डेटा की श्रेणी को संदर्भित करने का एक आसान तरीका है। डेटा के लिए सेल संदर्भ टाइप करने के बजाय, श्रेणी का नाम टाइप करें।
नामांकित श्रेणी का उपयोग करने का दूसरा लाभ यह है कि इस श्रेणी के लिए सेल संदर्भ कभी नहीं बदलते हैं, भले ही सूत्र को वर्कशीट में अन्य कक्षों में कॉपी किया गया हो। फ़ार्मुलों की प्रतिलिपि बनाते समय त्रुटियों को रोकने के लिए श्रेणी नाम निरपेक्ष सेल संदर्भों का उपयोग करने का एक विकल्प है।
श्रेणी के नाम में डेटा के लिए शीर्षक या फ़ील्ड नाम शामिल नहीं हैं (जैसा कि पंक्ति 4 में दिखाया गया है), केवल डेटा।
-
वर्कशीट में सेल D5 से G10 हाइलाइट करें।
Image -
कर्सर को कॉलम A के ऊपर स्थित नेम बॉक्स में रखें, Table टाइप करें, फिर Enter दबाएं। कक्ष D5 से G10 में तालिका का श्रेणी नाम है।
Image - VLOOKUP तालिका सरणी तर्क के लिए श्रेणी नाम का उपयोग इस ट्यूटोरियल में बाद में किया गया है।
VLOOKUP डायलॉग बॉक्स खोलें
यद्यपि वर्कशीट के सेल में लुकअप फॉर्मूला को सीधे टाइप करना संभव है, कई लोगों को सिंटैक्स को सीधा रखना मुश्किल लगता है - विशेष रूप से एक जटिल फॉर्मूला जैसे कि इस ट्यूटोरियल में इस्तेमाल किया गया।
विकल्प के रूप में, VLOOKUP Function Arguments डायलॉग बॉक्स का उपयोग करें। एक्सेल के लगभग सभी फंक्शंस में एक डायलॉग बॉक्स होता है जहां प्रत्येक फंक्शन के तर्कों को एक अलग लाइन पर दर्ज किया जाता है।
-
वर्कशीट का सेल E2 चुनें। यह वह स्थान है जहां द्वि-आयामी लुकअप सूत्र के परिणाम प्रदर्शित होंगे।
Image -
रिबन पर, सूत्र टैब पर जाएं और लुकअप एंड रेफरेंस चुनें।
Image -
SelectVLOOKUP को Function Arguments डायलॉग बॉक्स खोलने के लिए चुनें।
Image - फंक्शन आर्ग्युमेंट्स डायलॉग बॉक्स वह जगह है जहां VLOOKUP फ़ंक्शन के पैरामीटर दर्ज किए जाते हैं।
लुकअप मान तर्क दर्ज करें
आम तौर पर, लुकअप मान डेटा तालिका के पहले कॉलम में डेटा के क्षेत्र से मेल खाता है। इस उदाहरण में, लुकअप मान उस भाग के नाम को संदर्भित करता है जिसे आप जानकारी खोजना चाहते हैं। लुकअप मान के लिए स्वीकार्य प्रकार के डेटा टेक्स्ट डेटा, तार्किक मान, संख्याएं और सेल संदर्भ हैं।
पूर्ण सेल संदर्भ
जब एक्सेल में फ़ार्मुलों की प्रतिलिपि बनाई जाती है, तो नए स्थान को दर्शाने के लिए सेल संदर्भ बदल जाते हैं। यदि ऐसा होता है, तो D2, लुकअप मान के लिए सेल संदर्भ, F2 और G2 कक्षों में परिवर्तन और त्रुटियाँ उत्पन्न करता है।
सूत्रों की प्रतिलिपि बनाने पर पूर्ण सेल संदर्भ नहीं बदलते हैं।
त्रुटियों को रोकने के लिए सेल रेफरेंस D2 को एब्सोल्यूट सेल रेफरेंस में बदलें। एक पूर्ण सेल संदर्भ बनाने के लिए, F4 कुंजी दबाएं। यह $D$2 जैसे सेल संदर्भ के आसपास डॉलर के संकेत जोड़ता है।
-
फंक्शन आर्ग्युमेंट्स डायलॉग बॉक्स में, कर्सर को lookup_value टेक्स्ट बॉक्स में रखें। फिर, वर्कशीट में, इस सेल संदर्भ को lookup_value में जोड़ने के लिए cell D2 चुनें। सेल D2 वह जगह है जहां भाग का नाम दर्ज किया जाएगा।
Image -
सम्मिलन बिंदु को हिलाए बिना, D2 को पूर्ण सेल संदर्भ $D$2 में बदलने के लिए F4 कुंजी दबाएं।
Image - ट्यूटोरियल में अगले चरण के लिए VLOOKUP फंक्शन डायलॉग बॉक्स को खुला छोड़ दें।
तालिका सरणी तर्क दर्ज करें
एक तालिका सरणी डेटा की तालिका है जिसे लुकअप सूत्र आपकी इच्छित जानकारी को खोजने के लिए खोजता है। तालिका सरणी में डेटा के कम से कम दो स्तंभ होने चाहिए।
पहले कॉलम में लुकअप वैल्यू तर्क होता है (जो पिछले सेक्शन में सेट किया गया था), जबकि दूसरे कॉलम को आपके द्वारा निर्दिष्ट जानकारी को खोजने के लिए लुकअप फॉर्मूला द्वारा खोजा जाता है।
तालिका सरणी तर्क को या तो एक श्रेणी के रूप में दर्ज किया जाना चाहिए जिसमें डेटा तालिका के लिए सेल संदर्भ हों या एक श्रेणी नाम के रूप में दर्ज किया जाना चाहिए।
VLOOKUP फ़ंक्शन में डेटा तालिका जोड़ने के लिए, संवाद बॉक्स में table_array टेक्स्ट बॉक्स में कर्सर रखें और तालिका टाइप करेंइस तर्क के लिए श्रेणी नाम दर्ज करने के लिए।
कॉलम फ़ंक्शन को नेस्ट करें
आम तौर पर, VLOOKUP केवल डेटा तालिका के एक कॉलम से डेटा लौटाता है।यह कॉलम कॉलम इंडेक्स नंबर तर्क द्वारा निर्धारित किया गया है। इस उदाहरण में, हालांकि, तीन कॉलम हैं, और लुकअप फॉर्मूला को संपादित किए बिना कॉलम इंडेक्स नंबर को बदलने की जरूरत है। इसे पूरा करने के लिए, VLOOKUP फ़ंक्शन के अंदर COLUMN फ़ंक्शन को Col_index_num तर्क के रूप में नेस्ट करें।
नेस्टिंग फंक्शन के दौरान, एक्सेल दूसरे फंक्शन के डायलॉग बॉक्स को उसके तर्कों को दर्ज करने के लिए नहीं खोलता है। COLUMN फ़ंक्शन को मैन्युअल रूप से दर्ज किया जाना चाहिए। COLUMN फ़ंक्शन में केवल एक तर्क है, संदर्भ तर्क, जो एक सेल संदर्भ है।
कॉलम फ़ंक्शन संदर्भ तर्क के रूप में प्रदान किए गए कॉलम की संख्या देता है। यह कॉलम के अक्षर को एक संख्या में बदल देता है।
किसी वस्तु का मूल्य ज्ञात करने के लिए, डेटा तालिका के कॉलम 2 में डेटा का उपयोग करें। यह उदाहरण Col_index_num तर्क में 2 डालने के लिए संदर्भ के रूप में स्तंभ B का उपयोग करता है।
-
Function Arguments डायलॉग बॉक्स में, कर्सर को Col_index_num टेक्स्ट बॉक्स में रखें और COLUMN() टाइप करें । (खुले गोल ब्रैकेट को शामिल करना सुनिश्चित करें।)
Image -
वर्कशीट में, उस सेल संदर्भ को संदर्भ तर्क के रूप में दर्ज करने के लिए cell B1 चुनें।
Image - COLUMN फ़ंक्शन को पूरा करने के लिए क्लोजिंग राउंड ब्रैकेट टाइप करें।
VLOOKUP रेंज लुकअप तर्क दर्ज करें
VLOOKUP का Range_lookup तर्क एक तार्किक मान (TRUE या FALSE) है जो इंगित करता है कि VLOOKUP को Lookup_value से सटीक या अनुमानित मिलान मिलना चाहिए या नहीं।
- TRUE या छोड़े गए: VLOOKUP, Lookup_value से एक करीबी मैच देता है। यदि सटीक मिलान नहीं मिलता है, तो VLOOKUP अगला सबसे बड़ा मान लौटाता है। Table_array के पहले कॉलम में डेटा को आरोही क्रम में क्रमबद्ध किया जाना चाहिए।
- FALSE: VLOOKUP, Lookup_value के सटीक मिलान का उपयोग करता है। यदि Table_array के पहले कॉलम में दो या अधिक मान हैं जो लुकअप मान से मेल खाते हैं, तो पाया गया पहला मान उपयोग किया जाता है। यदि सटीक मिलान नहीं मिलता है, तो N/A त्रुटि वापस आ जाती है।
इस ट्यूटोरियल में, किसी विशेष हार्डवेयर आइटम के बारे में विशिष्ट जानकारी देखी जाएगी, इसलिए Range_lookup FALSE पर सेट है।
फंक्शन आर्ग्युमेंट्स डायलॉग बॉक्स में, कर्सर को रेंज_लुकअप टेक्स्ट बॉक्स में रखें और False टाइप करें ताकि VLOOKUP को डेटा के लिए सटीक मिलान वापस करने के लिए कहा जा सके।
लुकअप फॉर्मूला पूरा करने और डायलॉग बॉक्स बंद करने के लिए ठीक चुनें। सेल E2 में N/A त्रुटि होगी क्योंकि सेल D2 में लुकअप मानदंड दर्ज नहीं किया गया है। यह त्रुटि अस्थायी है। इस ट्यूटोरियल के अंतिम चरण में लुकअप मानदंड जोड़े जाने पर इसे ठीक किया जाएगा।
लुकअप फॉर्मूला कॉपी करें और मानदंड दर्ज करें
लुकअप फॉर्मूला एक बार में डेटा टेबल के कई कॉलम से डेटा पुनर्प्राप्त करता है। ऐसा करने के लिए, लुकअप फ़ॉर्मूला उन सभी फ़ील्ड में होना चाहिए जिनसे आप जानकारी चाहते हैं।
डेटा तालिका के कॉलम 2, 3 और 4 से डेटा पुनर्प्राप्त करने के लिए (कीमत, भाग संख्या और आपूर्तिकर्ता का नाम), लुकअप_वैल्यू के रूप में आंशिक नाम दर्ज करें।
चूंकि डेटा वर्कशीट में एक नियमित पैटर्न में रखा गया है, लुकअप फॉर्मूला को सेल E2 से cells F2 में कॉपी करें और G2 जैसे ही सूत्र की प्रतिलिपि बनाई जाती है, Excel सूत्र के नए स्थान को दर्शाने के लिए COLUMN फ़ंक्शन (सेल B1) में संबंधित सेल संदर्भ को अद्यतन करता है। एक्सेल निरपेक्ष सेल संदर्भ (जैसे $D$2) और नामित श्रेणी (तालिका) को नहीं बदलता है क्योंकि सूत्र की प्रतिलिपि बनाई गई है।
एक्सेल में डेटा कॉपी करने के एक से अधिक तरीके हैं, लेकिन फिल हैंडल का उपयोग करना सबसे आसान तरीका है।
-
चुनें सेल E2, जहां लुकअप फॉर्मूला स्थित है, इसे सक्रिय सेल बनाने के लिए।
Image -
भरने वाले हैंडल को सेल G2 तक खींचें। सेल F2 और G2 सेल E2 में मौजूद N/A त्रुटि प्रदर्शित करते हैं।
Image -
डेटा तालिका से जानकारी प्राप्त करने के लिए लुकअप फ़ार्मुलों का उपयोग करने के लिए, कार्यपत्रक में सेल D2 चुनें, विजेट टाइप करें, और दबाएं दर्ज करें.
Image निम्न जानकारी सेल E2 से G2 में प्रदर्शित होती है।
- E2: $14.76 - एक विजेट की कीमत
- F2: PN-98769 - एक विजेट के लिए भाग संख्या
- G2: Widgets Inc. - विजेट के लिए आपूर्तिकर्ता का नाम
-
VLOOKUP सरणी सूत्र का परीक्षण करने के लिए, कक्ष D2 में अन्य भागों के नाम टाइप करें और कक्ष E2 से G2 में परिणाम देखें।
Image - लुकअप फॉर्मूला वाले प्रत्येक सेल में आपके द्वारा खोजे गए हार्डवेयर आइटम के बारे में डेटा का एक अलग टुकड़ा होता है।
COLUMN जैसे नेस्टेड फ़ंक्शन के साथ VLOOKUP फ़ंक्शन, लुकअप संदर्भ के रूप में अन्य डेटा का उपयोग करके, तालिका के अंदर डेटा देखने के लिए एक शक्तिशाली विधि प्रदान करता है।
