जब भी हमें किसी अन्य वर्कशीट से या उसी वर्कशीट के भीतर से कोई मान निकालना होता है, हम एक्सेल में VLOOKUP फंक्शन के बारे में सोचे बिना नहीं रह सकते। लेकिन VLOOKUP फंक्शन की मुख्य समस्या यह है कि यह केवल एक बार एक ही मान लौटाने के लिए डिज़ाइन किया गया है। इसलिए, हमें इस फंक्शन के साथ VLOOKUP एकाधिक मान करने के लिए थोड़ा बॉक्स से बाहर सोचना होगा जिसका वर्णन हम इस लेख में करेंगे।
हम अन्य एक्सेल फंक्शन जैसे INDEX, SMALL, MATCH, ROW, COLUMNS, आदि का भी उपयोग कर सकते हैं, उन्हें उपयुक्त रूप से संयोजित करके VLOOKUP और एकाधिक मान लौटाने के लिए। हम कई एक्सेल सुविधाओं जैसे एडवांस्ड फिल्टर, ऑटोफिल्टर, और फॉर्मेट एज़ टेबल टूल का भी उपयोग कर सकते हैं। इसलिए, इस ट्यूटोरियल में हम VLOOKUP फंक्शन का उपयोग करके 1 विधि, अन्य एक्सेल फंक्शन का उपयोग करके 4 विधियाँ, और एक्सेल में VLOOKUP और एकाधिक मान आसानी से लौटाने के लिए विभिन्न एक्सेल टूल और विकल्पों का उपयोग करके 3 विधियों पर चर्चा करेंगे।
आपको सलाह दी जाती है कि एक्सेल फ़ाइल डाउनलोड करें और इसके साथ अभ्यास करें।
एक्सेल में VLOOKUP और एकाधिक मान लौटाने के 8 तरीके
इस ट्यूटोरियल में, हम पूरे लेख में सभी 8 विधियों को प्रदर्शित करने के लिए एक कर्मचारी डेटाबेस का उपयोग करेंगे।

तो, बिना किसी और चर्चा के, आइए एक-एक करके सभी 8 विधियों में उतरते हैं।
1. एक्सेल में VLOOKUP फंक्शन का उपयोग करके एकाधिक मान लौटाएं
हम जानते हैं कि VLOOKUP फंक्शन एक समय में केवल एक मान लौटा सकता है। लेकिन हमें एकाधिक मान लौटाने की आवश्यकता है। हां, ऐसा करने के लिए अन्य विकल्प उपलब्ध हैं। लेकिन यदि आप विशेष रूप से VLOOKUP फंक्शन का उपयोग करना चाहते हैं, तो आशा न खोएं। एक रास्ता है। इस खंड में, आप सीखेंगे कि आप अपने डेटासेट को थोड़ा बदलकर VLOOKUP फंक्शन का उपयोग करके एकाधिक मान कैसे लौटा सकते हैं। इस विधि में हम जिन फंक्शन का उपयोग करेंगे वे हैं COUNTIF और VLOOKUP। अब नीचे दिए गए चरणों का पालन करें:
🔗 चरण:
सबसे पहले, हमें सभी विभाग नामों को अद्वितीय बनाने की आवश्यकता है। ऐसा करने के लिए,
❶ सेल E5 चुनें।
❷ अब सेल के अंदर फॉर्मूला टाइप करें
=B5&COUNTIF(B5:B$13,B5)❸ उसके बाद, ENTER बटन दबाएं।

❹ फिर फिल हैंडल आइकन को Dept._Unique कॉलम तक नीचे खींचें।

❺ अब सेल C16 चुनें और इसके साथ फॉर्मूला टाइप करें
=VLOOKUP(B16,E5:F13,2,0)❻ बाद में, ENTER बटन दबाएं।

❼ अंत में, फिल हैंडल आइकन को कर्मचारी कॉलम के अंत तक खींचें।

बस यही है।
2. एक्सेल में VLOOKUP और एकाधिक मान लंबवत खींचें
मान लीजिए, आप इंजीनियरिंग विभाग के तहत काम करने वाले कर्मचारियों की संख्या जानना चाहते हैं और उन नामों को कॉलम में लंबवत व्यवस्थित करना चाहते हैं। यदि हां तो नीचे दिए गए चरणों का पालन करें कि यह कैसे करना है। इस विधि में हम जिन फंक्शन का उपयोग करेंगे वे हैं INDEX, SMALL, MATCH, और ROW।
🔗 चरण:
❶ सबसे पहले, फॉर्मूला परिणाम स्टोर करने के लिए सेल 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($B$5:$B$13)), "") ▶ SMALL द्वारा लौटाई गई पंक्ति सूचकांक संख्या के आधार पर कर्मचारी नाम लौटाता है।
3. एक्सेल में VLOOKUP और एकाधिक मान क्षैतिज रूप से लौटाएं
मान लीजिए, अब आप इंजीनियरिंग विभाग के तहत काम करने वाले कर्मचारियों की संख्या जानना चाहते हैं और उन नामों को पंक्तियों में क्षैतिज रूप से व्यवस्थित करना चाहते हैं। इस विधि में हम जिन फंक्शन का उपयोग करेंगे वे हैं INDEX, SMALL, MIN, और ROW।
🔗 चरण:
❶ सबसे पहले, फॉर्मूला परिणाम स्टोर करने के लिए सेल 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 द्वारा लौटाई गई पंक्ति सूचकांक संख्या के आधार पर कर्मचारी नाम लौटाता है।
4. मानदंड के साथ एक्सेल में VLOOKUP और एकाधिक मान प्राप्त करें
इस खंड में, हम कर्मचारी कॉलम में उन सभी कर्मचारी नामों को लौटाएंगे जो इंजीनियरिंग विभाग के तहत काम करते हैं और सुबह की शिफ्ट में भी काम करते हैं। इस विधि में हम जिन फंक्शन का उपयोग करेंगे वे हैं INDEX, SMALL, IFERROR, और ROW।
🔗 चरण:
❶ सबसे पहले, फॉर्मूला परिणाम स्टोर करने के लिए सेल 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. एक्सेल में VLOOKUP और एकाधिक मान निकालें (एक सेल में सभी)
अब हम इंजीनियरिंग विभाग के तहत काम करने वाले सभी कर्मचारी नामों को उसी सेल में एक साथ रखेंगे। इस विधि में हम जिन फंक्शन का उपयोग करेंगे वे हैं TEXTJOIN और IF।
🔗 चरण:
❶ सबसे पहले, फॉर्मूला परिणाम स्टोर करने के लिए सेल H5 चुनें ▶।
❷ उसके बाद, सेल के अंदर फॉर्मूला टाइप करें
=TEXTJOIN(",",TRUE,IF($B$5:$B$13=$C$15,$C$5:$C$13,""))❸ फिर ENTER बटन दबाएं।

बस यही है।
␥ फॉर्मूला ब्रेकडाउन
यहां, IF फंक्शन उन सभी कर्मचारी नामों को लौटाता है जो शर्त से मेल खाते हैं और TEXTJOIN फंक्शन IF फंक्शन द्वारा लौटाए गए सभी कर्मचारी नामों को जोड़ता है।
और पढ़ें: एक्सेल में एक सेल में VLOOKUP एकाधिक मान कैसे करें (2 आसान तरीके)
6. ऑटोफिल्टर का उपयोग करके एक्सेल में VLOOKUP से एकाधिक मान लौटाएं
यदि आप एक्सेल फॉर्मूला का उपयोग करने से बचना चाहते हैं, तो आप डेटा टेबल के माध्यम से VLOOKUP कर सकते हैं और ऑटोफिल्टर सुविधा का उपयोग करके एकाधिक मान निकाल सकते हैं। यहां, हम उन सभी कर्मचारी नामों को छांटने का प्रयास करेंगे जो इंजीनियरिंग विभाग के तहत काम करते हैं।
🔗 चरण:
❶ सबसे पहले, संपूर्ण डेटा टेबल चुनें।
❷ फिर डेटा रिबन पर जाएं।
❸ उसके बाद, फिल्टर विकल्प पर क्लिक करें।
❹ अब विभाग कॉलम हेडर के ठीक नीचे फिल्टर आइकन पर क्लिक करें।

❺ अब पॉप-अप मेनू से इंजीनियरिंग विकल्प पर टिक मार्क करें।
❻ फिर ओके बटन दबाएं।

बहुत बढ़िया! आप इसके साथ हो चुके हैं। अब आपको परिणाम निम्नानुसार मिलेगा:

7. एडवांस्ड फिल्टर का उपयोग करके एक्सेल में VLOOKUP और एकाधिक मान निकालें
एक्सेल में एडवांस्ड फिल्टर नामक एक सुविधा है जिसका उपयोग आप डेटा टेबल के माध्यम से लंबवत खोज करने और एक ही समय में एकाधिक मान निकालने के लिए कर सकते हैं। निम्नलिखित उदाहरण में, हमने विभाग कॉलम हेडर के तहत इंजीनियरिंग विभाग को चुना। जिसका उपयोग हम डेटा टेबल में खोज करने के लिए करेंगे कि कौन से कर्मचारी इंजीनियरिंग विभाग के तहत काम करते हैं। अब, पालन करने के चरण यहां दिए गए हैं:
🔗 चरण:
❶ सबसे पहले, संपूर्ण डेटा टेबल चुनें।
❷ फिर डेटा रिबन पर जाएं।
❸ उसके बाद, एडवांस्ड विकल्प पर क्लिक करें।

❹ एक बार जब आप अंतिम चरण के साथ हो जाते हैं, तो एडवांस्ड फिल्टर नामक एक डायलॉग बॉक्स स्क्रीन पर दिखाई देगा।
❺ अब लिस्ट रेंज: बार में $B$4:$C$13
दर्ज करें और 'Advanced Filter'!$E$4:$E$5
क्राइटेरिया रेंज: बार में दर्ज करें।
❻ उसके बाद, आप ओके बटन दबा सकते हैं।

जब आप पिछले सभी चरणों के साथ हो जाते हैं, तो आपको आउटपुट निम्नानुसार मिलेगा:

8. फॉर्मेट एज़ टेबल का उपयोग करके एक्सेल में VLOOKUP से एकाधिक मान प्राप्त करें
आप इस विधि का उपयोग ऑटोफिल्टर और एडवांस्ड फिल्टर विकल्प के विकल्प के रूप में कर सकते हैं। इस विधि में, हम इंजीनियरिंग श्रेणी के तहत सभी कर्मचारी नामों को छांटने के लिए फॉर्मेट एज़ टेबल विकल्प का उपयोग करेंगे। पालन करने के चरण यहां दिए गए हैं:
🔗 चरण:
❶ सबसे पहले, संपूर्ण डेटा टेबल चुनें।
❷ फिर होम रिबन पर जाएं।
❸ उसके बाद फॉर्मेट एज़ टेबल विकल्प चुनें।
❹ फिर चुनें कोई भी टेबल स्टाइल जो आपको पसंद हो।

अब आपका डेटा टेबल निम्नलिखित छवि की तरह दिखेगा।

❺ इस चरण में, विभाग कॉलम हेडर के नीचे फिल्टर आइकन पर क्लिक करें।
❻ पॉप-अप मेनू से, केवल इंजीनियरिंग विकल्प पर टिक मार्क करें।
❼ फिर ओके बटन पर क्लिक करें।

जब आप पिछले सभी चरणों के साथ हो जाते हैं, तो आपका परिणाम इस तरह दिखना चाहिए:

याद रखने योग्य बातें
📌 VLOOKUP फंक्शन अकेले एक से अधिक मान नहीं ला सकता।
📌 फंक्शन के सिंटैक्स के बारे में सावधान रहें।
📌 फॉर्मूला में डेटा रेंज सावधानी से डालें।
निष्कर्ष
समाप्त करने के लिए, हमने एक्सेल में VLOOKUP और एकाधिक मान लौटाने के 8 अलग-अलग तरीकों पर चर्चा की है। आपको सलाह दी जाती है कि इस लेख के साथ संलग्न अभ्यास वर्कबुक डाउनलोड करें और इसके साथ सभी विधियों का अभ्यास करें। और नीचे टिप्पणी अनुभाग में कोई भी प्रश्न पूछने में संकोच न करें। हम यथाशीघ्र सभी प्रासंगिक प्रश्नों का उत्तर देने का प्रयास करेंगे।
संबंधित लेख
- एक्सेल में कई पंक्तियों के साथ VLOOKUP कैसे करें (5 तरीके)