जब भी हमें किसी अन्य कार्यपत्रक से या उसी कार्यपत्रक के भीतर कोई मान खींचने की आवश्यकता होती है, तो हम VLOOKUP फ़ंक्शन के बारे में सोचने में मदद नहीं कर सकते। एक्सेल में। लेकिन मुख्य समस्या VLOOKUP . के साथ है फ़ंक्शन यह है कि इसे केवल एक बार एक मान वापस करने के लिए डिज़ाइन किया गया है। इसलिए, हमें इस फ़ंक्शन के साथ कई मानों को देखने के लिए बॉक्स से थोड़ा हटकर सोचना होगा जिसका वर्णन हम इस लेख में करेंगे।
हम अन्य एक्सेल फ़ंक्शंस जैसे INDEX . का भी उपयोग कर सकते हैं , छोटा , मिलान , पंक्ति , कॉलम , आदि, उपयुक्त रूप से उन्हें vlookup में संयोजित करना और कई मान वापस करना। हम कई एक्सेल सुविधाओं का भी उपयोग कर सकते हैं जैसे उन्नत फ़िल्टर , स्वतः फ़िल्टर , और तालिका के रूप में प्रारूपित करें औजार। इसलिए, इस ट्यूटोरियल में हम कुल 1 . पर चर्चा करने जा रहे हैं विधि VLOOKUP फ़ंक्शन का उपयोग कर रही है , 4 अन्य एक्सेल फ़ंक्शंस का उपयोग करने वाली विधियाँ, और 3 विभिन्न एक्सेल टूल और विकल्पों का उपयोग करके आसानी से एक्सेल में कई मानों को देखने और वापस करने के लिए।
आपको एक्सेल फ़ाइल डाउनलोड करने और उसके साथ अभ्यास करने की सलाह दी जाती है।
VLOOKUP के 8 तरीके और Excel में एकाधिक मान लौटाएं
इस ट्यूटोरियल में, हम एक कर्मचारी डेटाबेस . का उपयोग करेंगे पूरे लेख में सभी 8 विधियों को प्रदर्शित करने के लिए।
तो, बिना किसी और चर्चा के, आइए एक-एक करके सभी 8 तरीकों के बारे में जानें।
<एच3>1. Excel में VLOOKUP फ़ंक्शन का उपयोग करके एकाधिक मान लौटाएंहम जानते हैं कि VLOOKUP फ़ंक्शन एक समय में केवल एक मान लौटा सकता है। लेकिन हमें कई मान वापस करने होंगे। हां, ऐसा करने के लिए अन्य विकल्प उपलब्ध हैं। लेकिन अगर आप विशेष रूप से VLOOKUP . का उपयोग करना चाहते हैं समारोह, आशा मत खोना। एक रास्ता है। इस खंड में, आप सीखेंगे कि आप VLOOKUP . का उपयोग कैसे कर सकते हैं अपने डेटासेट को थोड़ा सा ट्वीव करके कई मान वापस करने के लिए कार्य करें। इस पद्धति में हम जिन कार्यों का उपयोग करेंगे वे हैं COUNTIF और वीलुकअप . अब नीचे दिए गए चरणों का पालन करें:
🔗 चरण:
सबसे पहले, हमें सभी विभागों के नामों को विशिष्ट बनाना होगा। ऐसा करने के लिए,
❶ चुनें सेल E5 ।
❷ अब टाइप करें सूत्र
=B5&COUNTIF(B5:B$13,B5) सेल के भीतर।
❸ उसके बाद, ENTER . दबाएं बटन।
❹ खींचें हैंडल भरें Dept._Unique . के नीचे आइकन कॉलम।
❺ अब सेल चुनें C16 और टाइप करें सूत्र
=VLOOKUP(B16,E5:F13,2,0) इसके साथ।
❻ बाद में, ENTER . दबाएं बटन।
❼ अंत में, हैंडल भरें . को खींचें कर्मचारी . के अंत में आइकन कॉलम।
बस।
<एच3>2. एक्सेल में लंबवत रूप से देखें और एकाधिक मानों को खींचेमान लीजिए, आप इंजीनियरिंग . के तहत काम करने वाले कर्मचारियों की संख्या का पता लगाना चाहते हैं विभाग और उन नामों को स्तंभों में लंबवत रूप से व्यवस्थित करें। यदि ऐसा है तो इसे कैसे करना है यह जानने के लिए नीचे दिए गए चरणों का पालन करें। इस पद्धति में हम जिन कार्यों का उपयोग करेंगे वे हैं INDEX , छोटा , मिलान , और पंक्ति ।
🔗 चरण:
❶ सबसे पहले, चुनें सेल F5 ▶ सूत्र परिणाम संग्रहीत करने के लिए।
❷ उसके बाद, टाइप करें सूत्र
=INDEX($C$5:$C$13, SMALL(IF(($E$5=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), ""), ROWS($A$1:A1))) सेल के भीतर।
❸ फिर ENTER . दबाएं बटन।
❹ अब भरें हैंडल . को खींचें कर्मचारी . में नीचे की ओर आइकन कॉलम।
␥ फॉर्मूला ब्रेकडाउन
- ROW($B$5:$B$13) ▶ पंक्ति संख्या को सरणी रूप में लौटाता है:{5;6;7;8;9;10;11;12;13}
- MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)) ▶ पिछली सरणी को निम्न में रूपांतरित करता है:{1;2;3;4;5;6;7;8;9}
- IF(($C$15=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), "") ▶ शर्त से मेल खाने वाली पंक्ति संख्या देता है, अन्यथा शून्य लौटाता है:{“”;2;””;4;””;””;””;”;9}
- SMALL(IF(($E$5=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), " ”) ▶ सरणी के भीतर पहली छोटी संख्या देता है:{“”;2;””;4;””;””;””;””;9}
- INDEX($C$5:$C$13, SMALL(IF(($E$5=$B$5:$B$13),MATCH(ROW($B$5:$B$13), ROW($ बी$5:$बी$13)), "") ▶ SMALL. . द्वारा लौटाई गई पंक्ति अनुक्रमणिका संख्या के आधार पर कर्मचारी के नाम लौटाता है
मान लीजिए, अब आप इंजीनियरिंग के तहत काम करने वाले कर्मचारियों की संख्या का पता लगाना चाहते हैं विभाग और उन नामों को पंक्तियों में क्षैतिज रूप से व्यवस्थित करें। इस पद्धति में हम जिन कार्यों का उपयोग करेंगे वे हैं INDEX , छोटा , मिनट , और पंक्ति ।
🔗 चरण:
❶ सबसे पहले, चुनें सेल C16 ▶ सूत्र परिणाम संग्रहीत करने के लिए।
❷ उसके बाद, टाइप करें सूत्र
=INDEX($C$5:$C$13, SMALL(IF($C$15=$B$5:$B$13, ROW($B$5:$B$13)-MIN(ROW($B$5:$B$13))+1, ""), COLUMNS($A$1:A1))) सेल के भीतर।
❸ फिर ENTER . दबाएं बटन।
❹ अब भरें हैंडल . को खींचें कर्मचारी . के दाईं ओर आइकन पंक्ति।
␥ फॉर्मूला ब्रेकडाउन
- ROW($B$5:$B$13) ▶ पंक्ति संख्या को सरणी रूप में लौटाता है:{5;6;7;8;9;10;11;12;13}
- SMALL(IF($C$15=$B$5:$B$13,ROW($B$5:$B$13)-MIN(ROW($B$5:$B$13))+1, " ”) ▶ सरणी के भीतर पहली छोटी संख्या देता है।
- INDEX($C$5:$C$13, SMALL(IF($C$15=$B$5:$B$13,ROW($B$5:$B$13)-MIN(ROW($B$5) :$B$13))+1, "") ▶ SMALL. . द्वारा लौटाई गई पंक्ति अनुक्रमणिका संख्या के आधार पर कर्मचारी के नाम लौटाता है
इस खंड में, हम कर्मचारी . में सभी कर्मचारियों के नाम वापस कर देंगे कॉलम जो इंजीनियरिंग . के अंतर्गत काम करता है विभाग और सुबह . में भी काम करते हैं खिसक जाना। इस पद्धति में हम जिन कार्यों का उपयोग करेंगे वे हैं INDEX , छोटा , IFERROR , और पंक्ति ।
🔗 चरण:
❶ सबसे पहले, चुनें सेल H5 ▶ सूत्र परिणाम संग्रहीत करने के लिए।
❷ उसके बाद, टाइप करें सूत्र
=IFERROR(INDEX($C$5:$C$13,SMALL(IF(1=((--($F$5=$B$5:$B$13)) *(--($G$5=$D$5:$D$13))), ROW($C$5:$C$13)-4,""), ROW()-4)),"") सेल के भीतर।
❸ फिर ENTER . दबाएं बटन।
❹ अब भरें हैंडल . को खींचें कर्मचारी . में नीचे की ओर आइकन कॉलम।
␥ फॉर्मूला ब्रेकडाउन
यह सूत्र पिछले दो सूत्रों से काफी मिलता-जुलता है। यहां अपवाद हैं:
- ROW($C$5:$C$13)-4 ▶ सरणी के भीतर पंक्ति संख्या देता है:{5;6;7;8;9;10;11;12;13}। यहां, संख्या 4 उस पंक्ति संख्या को संदर्भित करती है जिस पर स्तंभ शीर्षलेख कर्मचारी रहता है।
- ROW()-4 ▶ यहां संख्या 4 आउटपुट पंक्ति शुरू होने से पहले पिछली पंक्ति संख्या को संदर्भित करती है।
- IFERROR ▶ यहां IFERROR यदि कोई त्रुटि होती है तो एक अनुकूलित आउटपुट को संश्लेषित करने के लिए फ़ंक्शन का उपयोग किया जाता है।
5. एक्सेल (ऑल इन वन सेल) में व्यूअप और एक्सट्रैक्ट मल्टीपल वैल्यू
अब हम उन सभी कर्मचारियों के नाम एक ही सेल में डालेंगे जो इंजीनियरिंग विभाग के तहत काम करते हैं। इस पद्धति में हम जिन कार्यों का उपयोग करेंगे वे हैं TEXTJOIN और अगर .
🔗 चरण:
❶ सबसे पहले, चुनें सेल H5 ▶ सूत्र परिणाम संग्रहीत करने के लिए।
❷ उसके बाद, टाइप करें सूत्र
=TEXTJOIN(",",TRUE,IF($B$5:$B$13=$C$15,$C$5:$C$13,"")) सेल के भीतर।
❸ फिर ENTER . दबाएं बटन।
बस।
␥ फॉर्मूला ब्रेकडाउन
यहाँ, IF फ़ंक्शन उन सभी कर्मचारियों के नाम लौटाता है जो शर्त से मेल खाते हैं और TEXTJOIN फ़ंक्शन IF . द्वारा लौटाए गए सभी कर्मचारी नामों में शामिल हो जाता है समारोह।
और पढ़ें: एक्सेल में एक सेल में एकाधिक मानों को कैसे देखें (2 आसान तरीके)
<एच3>6. AutoFilter का उपयोग करके Excel में एकाधिक मान लौटाने के लिए Vlookupयदि आप एक्सेल फ़ार्मुलों के उपयोग से बचना चाहते हैं, तो आप डेटा तालिकाओं के माध्यम से देख सकते हैं और ऑटोफ़िल्टर सुविधा का उपयोग करके कई मान निकाल सकते हैं। यहां, हम इंजीनियरिंग विभाग के तहत काम करने वाले सभी कर्मचारियों के नामों को छाँटने की कोशिश करेंगे।
🔗 चरण:
❶ सबसे पहले, चुनें संपूर्ण डेटा तालिका।
❷ फिर डेटा . पर जाएं रिबन।
❸ उसके बाद, फ़िल्टर . पर क्लिक करें विकल्प।
❹ अब विभाग . के ठीक नीचे फ़िल्टर आइकन पर क्लिक करें कॉलम हेडर।
❺ अब चिह्नित करें इंजीनियरिंग . पर निशान लगाएं पॉप-अप मेनू से विकल्प।
❻ फिर ठीक . दबाएं बटन।
वाहवाही! आप इसके साथ कर रहे हैं। अब आप इस प्रकार परिणाम देंगे:
एक्सेल में उन्नत फ़िल्टर नामक एक सुविधा है जिसका उपयोग आप डेटा तालिका के माध्यम से लंबवत रूप से देखने और एक ही समय में कई मान निकालने के लिए कर सकते हैं। निम्नलिखित उदाहरण में, हमने विभाग कॉलम हेडर के तहत इंजीनियरिंग विभाग को चुना। इंजीनियरिंग विभाग के तहत कौन से कर्मचारी काम करते हैं, यह पता लगाने के लिए हम पूरे डेटा टेबल में खोज करने के लिए उपयोग करेंगे। अब, यहां अनुसरण करने के चरण दिए गए हैं:
🔗 चरण:
❶ सबसे पहले, चुनें संपूर्ण डेटा तालिका।
❷ फिर डेटा . पर जाएं रिबन।
❸ उसके बाद, उन्नत . पर क्लिक करें विकल्प।
❹ अंतिम चरण पूरा करने के बाद, उन्नत फ़िल्टर . नामक एक संवाद बॉक्स स्क्रीन पर दिखाई देगा।
❺ अब दर्ज करें
$B$4:$C$13 सूची सीमा के भीतर: बार और
'Advanced Filter'!$E$4:$E$5 मानदंड सीमा के भीतर: छड़।
❻ उसके बाद, आप ठीक . दबा सकते हैं बटन।
जब आप पिछले सभी चरणों को पूरा कर लेंगे, तो आपको निम्न प्रकार से आउटपुट मिलेगा:
8. तालिका के रूप में प्रारूप का उपयोग करके एक्सेल में एकाधिक मान प्राप्त करने के लिए Vlookup
आप ऑटोफ़िल्टर . के विकल्प के रूप में इस विधि का उपयोग कर सकते हैं और उन्नत फ़िल्टर विकल्प। इस पद्धति में, हम तालिका के रूप में प्रारूप . का उपयोग करेंगे इंजीनियरिंग . के अंतर्गत सभी कर्मचारियों के नामों को क्रमबद्ध करने का विकल्प श्रेणी। यहां अनुसरण करने के चरण दिए गए हैं:
🔗 चरण:
❶ सबसे पहले, चुनें संपूर्ण डेटा तालिका।
❷ फिर होम . पर जाएं रिबन।
❸ उसके बाद तालिका के रूप में प्रारूपित करें . चुनें विकल्प।
❹ फिर चुनें कोई भी तालिका शैली जिसे आप पसंद करते हैं।
अब आपकी डेटा तालिका निम्न छवि की तरह दिखाई देगी।
❺ इस चरण में, फ़िल्टर आइकन . पर क्लिक करें विभाग . के अंतर्गत कॉलम हेडर।
❻ पॉप-अप मेनू से, टिक करें केवल इंजीनियरिंग . पर निशान लगाएं विकल्प।
❼ फिर ठीक . पर क्लिक करें बटन।
जब आप पिछले सभी चरणों को पूरा कर लें, तो आपका परिणाम इस तरह दिखना चाहिए:
याद रखने वाली बातें
📌 VLOOKUP फ़ंक्शन अकेले एक से अधिक मान नहीं ला सकता है।
फ़ंक्शन के सिंटैक्स के बारे में सावधान रहें।
डेटा श्रेणियों को फ़ार्मुलों में सावधानी से डालें।
निष्कर्ष
To wrap up, we have discussed 8 distinct methods to vlookup and return multiple values in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.
संबंधित लेख
- How to Perform VLOOKUP with Multiple Rows in Excel (5 Methods)