VLOOKUP फ़ंक्शन Excel में सबसे लोकप्रिय फ़ंक्शन में से एक है। यह ट्यूटोरियल चरण दर चरण दर्जनों बुनियादी और उन्नत उदाहरणों के साथ Excel में VLOOKUP फ़ंक्शन का उपयोग करने का तरीका बताएगा।

VLOOKUP फ़ंक्शन का परिचय - सिंटैक्स और तर्क

Excel में, VLOOKUP फ़ंक्शन अधिकांश Excel उपयोगकर्ताओं के लिए एक शक्तिशाली फ़ंक्शन है, यह आपको डेटा रेंज के सबसे बाईं ओर एक मान देखने की अनुमति देता है, और आपके द्वारा निर्दिष्ट कॉलम से उसी पंक्ति में एक मिलान मान लौटाता है जैसा कि स्क्रीनशॉट में दिखाया गया है .

VLOOKUP फ़ंक्शन का सिंटैक्स:

तर्क:

फ़ंक्शन नोट्स:

बुनियादी VLOOKUP उदाहरण

इस अनुभाग में, हम आपके द्वारा अक्सर उपयोग किए जाने वाले कुछ Vlookup फ़ार्मुलों के बारे में बात करेंगे।

2.1 सटीक मिलान और अनुमानित मिलान VLOOKUP

 2.1.1 VLOOKUP का सटीक मिलान करें

आम तौर पर, यदि आप VLOOKUP फ़ंक्शन के साथ सटीक मिलान की तलाश में हैं, तो आपको केवल अंतिम तर्क के रूप में FALSE का उपयोग करना होगा।

उदाहरण के लिए, विशिष्ट आईडी संख्याओं के आधार पर संबंधित गणित अंक प्राप्त करने के लिए, कृपया यह करें:

कृपया नीचे दिए गए फॉर्मूले को कॉपी करके एक रिक्त सेल में पेस्ट करें (यहां, मैं G2 का चयन करता हूं), और दबाएँ दर्ज परिणाम प्राप्त करने की कुंजी:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

VLOOKUP फॉर्मूला कैसे काम करता है?

सबसे पहले, यह तालिका के सबसे बाएं कॉलम में आईडी - C1005 की तलाश करता है। यह ऊपर से नीचे तक जाता है और सेल A6 में मान ढूंढता है।

जैसे ही उसे वैल्यू मिल जाती है, वह तीसरे कॉलम में दाईं ओर जाता है और उसमें वैल्यू निकाल लेता है।

तो, आपको नीचे दिखाए गए स्क्रीनशॉट के अनुसार परिणाम मिलेगा:

 2.1.2 अनुमानित मिलान VLOOKUP करें

डेटा श्रेणियों के बीच मान खोजने के लिए अनुमानित मिलान उपयोगी है। यदि सटीक मिलान नहीं मिलता है, तो अनुमानित VLOOKUP सबसे बड़ा मान लौटाएगा जो लुकअप मान से छोटा है।

उदाहरण के लिए, यदि आपके पास डेटा की निम्न श्रेणी है, और निर्दिष्ट ऑर्डर ऑर्डर कॉलम में नहीं हैं, तो कॉलम बी में इसकी निकटतम छूट कैसे प्राप्त करें?

चरण 1: VLOOKUP फॉर्मूला लागू करें और इसे अन्य सेल में भरें

निम्नलिखित सूत्र को उस सेल में कॉपी और पेस्ट करें जहां आप परिणाम डालना चाहते हैं, और फिर इस सूत्र को अन्य कक्षों पर लागू करने के लिए भरण हैंडल को नीचे खींचें।

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

रिजल्ट:

अब, आपको दिए गए मानों के आधार पर अनुमानित मिलान मिलेगा, स्क्रीनशॉट देखें:

2.2 एक्सेल में केस सेंसिटिव VLOOKUP करें

डिफ़ॉल्ट रूप से, VLOOKUP फ़ंक्शन केस असंवेदनशील लुकअप करता है जिसका अर्थ है कि यह लोअरकेस और अपरकेस वर्णों को समान मानता है। कभी-कभी, आपको एक्सेल में केस-सेंसिटिव लुकअप करने की आवश्यकता हो सकती है, सामान्य VLOOKUP फ़ंक्शन इसे हल नहीं कर सकता है। इस मामले में, आप EXACT फ़ंक्शन, या LOOKUP और EXACT फ़ंक्शन के साथ INDEX और MATCH जैसे वैकल्पिक फ़ंक्शन का उपयोग कर सकते हैं।

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

चरण 1: किसी एक सूत्र को लागू करें और इसे अन्य कक्षों में भरें

कृपया नीचे दिए गए सूत्रों में से किसी एक को कॉपी करके रिक्त कक्ष में चिपकाएँ जहाँ आप परिणाम प्राप्त करना चाहते हैं। फिर, सूत्र सेल का चयन करें, भरण हैंडल को उन कक्षों तक नीचे खींचें जहां आप इस सूत्र को भरना चाहते हैं।

फ़ॉर्मूला 1: फॉर्मूला पेस्ट करने के बाद कृपया दबाएं Ctrl + Shift + Enter चांबियाँ।

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

फ़ॉर्मूला 2: फॉर्मूला पेस्ट करने के बाद कृपया दबाएं दर्ज कुंजी।

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

रिजल्ट:

तभी आपको आवश्यक सही परिणाम मिलेंगे। स्क्रीनशॉट देखें:

2.3 एक्सेल में दाएँ से बाएँ VLOOKUP मान

VLOOKUP फ़ंक्शन हमेशा डेटा रेंज के सबसे बाएं कॉलम में एक मान खोजता है और एक कॉलम से दाईं ओर संबंधित मान लौटाता है। यदि आप एक रिवर्स VLOOKUP करना चाहते हैं, जिसका अर्थ है दाएं कॉलम में एक विशिष्ट मान को देखना और उसके संबंधित मान को बाएं कॉलम में वापस करना, जैसा कि नीचे स्क्रीनशॉट में दिखाया गया है:

इस कार्य के बारे में चरण दर चरण विवरण जानने के लिए क्लिक करें...

2.4 एक्सेल में दूसरा, वां या अंतिम मिलान मान VLOOKUP करें

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

 2.4.1 VLOOKUP और दूसरा या nवाँ मिलान मान लौटाएँ

मान लीजिए कि आपके पास कॉलम ए में नामों की एक सूची है, कॉलम बी में उनके द्वारा खरीदा गया प्रशिक्षण पाठ्यक्रम है। अब, आप दिए गए ग्राहक द्वारा खरीदा गया दूसरा या नौवां प्रशिक्षण पाठ्यक्रम ढूंढना चाह रहे हैं। स्क्रीनशॉट देखें:

यहां, VLOOKUP फ़ंक्शन इस कार्य को सीधे हल नहीं कर सकता है। लेकिन, आप विकल्प के रूप में INDEX फ़ंक्शन का उपयोग कर सकते हैं।

चरण 1: अन्य कक्षों पर सूत्र लागू करें और भरें

उदाहरण के लिए, दिए गए मानदंड के आधार पर दूसरा मिलान मान प्राप्त करने के लिए, कृपया निम्नलिखित सूत्र को एक रिक्त कक्ष में लागू करें, और दबाएँ Ctrl + Shift + Enter पहला परिणाम प्राप्त करने के लिए कुंजियाँ एक साथ। और फिर, सूत्र सेल का चयन करें, भरण हैंडल को उन कक्षों तक नीचे खींचें जहां आप इस सूत्र को भरना चाहते हैं।

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

रिजल्ट:

अब, दिए गए नामों के आधार पर सभी दूसरे मिलान किए गए मान एक ही बार में प्रदर्शित किए गए हैं।

 2.4.2 VLOOKUP और अंतिम मिलान मान लौटाएँ

यदि आप वीलुकअप करना चाहते हैं और नीचे दिखाए गए स्क्रीनशॉट के अनुसार अंतिम मिलान मूल्य वापस करना चाहते हैं, तो यह VLOOKUP और अंतिम मिलान मान लौटाएँ ट्यूटोरियल आपको विवरण में अंतिम मिलान मूल्य प्राप्त करने में मदद कर सकता है।

2.5 वीलुकअप दो दिए गए मानों या तिथियों के बीच मिलान मान

कभी-कभी, आप दो मानों या तिथियों के बीच मानों को देखना चाहते हैं और संबंधित परिणाम लौटाना चाहते हैं जैसा कि नीचे स्क्रीनशॉट में दिखाया गया है। ऐसे मामले में, आप क्रमबद्ध तालिका के साथ VLOOKUP फ़ंक्शन के बजाय LOOKUP फ़ंक्शन का उपयोग कर सकते हैं।

 2.5.1 वीलुकअप दो दिए गए मानों या दिनांकों के बीच सूत्र के साथ मानों का मिलान करता है

चरण 1: डेटा व्यवस्थित करें और निम्नलिखित सूत्र लागू करें

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

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

रिजल्ट:

और अब, आपको दिए गए मान के आधार पर सभी मिलान किए गए रिकॉर्ड मिलेंगे, स्क्रीनशॉट देखें:

 2.5.2 वीलुकअप एक उपयोगी सुविधा के साथ दो दिए गए मानों या तिथियों के बीच मानों का मिलान करता है

यदि आपको उपरोक्त सूत्र को याद रखना और समझना मुश्किल लगता है, तो यहां, मैं एक आसान टूल पेश करूंगा - एक्सेल के लिए कुटूल, के साथ अपने दो मानों के बीच लुकअप करें सुविधा, आप दो मूल्यों या तिथियों के बीच विशिष्ट मूल्य या तिथि के आधार पर संबंधित आइटम को आसानी से वापस कर सकते हैं।

1. क्लिक करें कुटूल > सुपर लुकअप > दो मानों के बीच लुकअप करें इस सुविधा को सक्षम करने के लिए
2. फिर अपने डेटा के आधार पर संवाद बॉक्स से संचालन निर्दिष्ट करें।

2.6 VLOOKUP फ़ंक्शन में आंशिक मिलान के लिए वाइल्डकार्ड का उपयोग करना

एक्सेल में, वाइल्डकार्ड का उपयोग VLOOKUP फ़ंक्शन के भीतर किया जा सकता है, जो आपको लुकअप मान पर आंशिक मिलान करने की अनुमति देता है। उदाहरण के लिए, आप लुकअप मान के भाग के आधार पर तालिका से मिलान किए गए मान को वापस करने के लिए VLOOKUP का उपयोग कर सकते हैं।

मान लीजिए, मेरे पास नीचे दिखाए गए स्क्रीनशॉट के अनुसार डेटा की एक श्रृंखला है, अब, मैं पहले नाम (पूरा नाम नहीं) के आधार पर स्कोर निकालना चाहता हूं। एक्सेल में इस समस्या को कैसे हल किया जा सकता है?

चरण 1: अन्य कक्षों पर सूत्र लागू करें और भरें

कृपया निम्नलिखित सूत्र को किसी रिक्त कक्ष में कॉपी करें या दर्ज करें, और फिर, इस सूत्र को अपनी आवश्यकतानुसार अन्य कक्षों में भरने के लिए भरण हैंडल को खींचें:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

रिजल्ट:

और सभी मिलान किए गए स्कोर नीचे दिखाए गए स्क्रीनशॉट के अनुसार लौटा दिए गए हैं:

2.7 किसी अन्य वर्कशीट से VLOOKUP मान

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

उदाहरण के लिए, आपके पास नीचे दिखाए गए स्क्रीनशॉट के अनुसार दो वर्कशीट हैं, आपके द्वारा निर्दिष्ट वर्कशीट से संबंधित डेटा को देखने और वापस करने के लिए, कृपया निम्नलिखित चरणों का पालन करें:

चरण 1: अन्य कक्षों पर सूत्र लागू करें और भरें

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

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

रिजल्ट:

आपको आवश्यकतानुसार अनुरूप परिणाम मिलेंगे, स्क्रीनशॉट देखें:

2.8 किसी अन्य कार्यपुस्तिका से VLOOKUP मान

यह अनुभाग लुकअप के बारे में बात करेगा और VLOOKUP फ़ंक्शन का उपयोग करके एक अलग कार्यपुस्तिका से मिलान मान लौटाएगा।

उदाहरण के लिए, मान लें कि आपके पास दो कार्यपुस्तिकाएँ हैं। पहली कार्यपुस्तिका में उत्पादों और उनकी संबंधित लागतों की एक सूची होती है। दूसरी कार्यपुस्तिका में, आप नीचे दिखाए गए स्क्रीनशॉट के अनुसार प्रत्येक उत्पाद आइटम के लिए संबंधित लागत निकालना चाहते हैं।

चरण 1: फॉर्मूला लागू करें और भरें

आप जिन दोनों कार्यपुस्तिकाओं का उपयोग करना चाहते हैं उन्हें खोलें, फिर निम्नलिखित सूत्र को उस सेल में लागू करें जहां आप परिणाम को दूसरी कार्यपुस्तिका में रखना चाहते हैं। फिर, इस सूत्र को अपनी आवश्यकतानुसार अन्य कक्षों में खींचें और कॉपी करें

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

रिजल्ट:

2.9 0 या #एन/ए त्रुटि के बजाय रिक्त या विशिष्ट पाठ लौटाएँ

आमतौर पर, जब आप संबंधित मान लौटाने के लिए VLOOKUP फ़ंक्शन का उपयोग करते हैं, यदि मिलान करने वाला सेल खाली है, तो यह 0 लौटाएगा। और यदि मिलान मान नहीं मिलता है, तो आपको #N/A का त्रुटि मान मिलेगा जैसा कि दिखाया गया है नीचे स्क्रीनशॉट. यदि आप 0 या #N/A के बजाय एक रिक्त सेल या एक विशिष्ट मान प्रदर्शित करना चाहते हैं, तो यह VLOOKUP 0 या N/A के बजाय रिक्त या विशिष्ट मान लौटाने के लिए ट्यूटोरियल आपका भला कर सकता है।

उन्नत VLOOKUP उदाहरण

3.1 दो-तरफ़ा लुकअप (पंक्ति और स्तंभ में VLOOKUP)

कभी-कभी, आपको 2-आयामी लुकअप करने की आवश्यकता हो सकती है, जिसका अर्थ है एक ही समय में पंक्ति और स्तंभ दोनों में मान खोजना। उदाहरण के लिए, यदि आपके पास निम्नलिखित डेटा रेंज है, और आपको एक निर्दिष्ट तिमाही में किसी विशेष उत्पाद के लिए मूल्य प्राप्त करने की आवश्यकता हो सकती है। यह अनुभाग Excel में इस कार्य से निपटने के लिए एक सूत्र प्रस्तुत करेगा।

एक्सेल में, आप दो-तरफ़ा लुकअप करने के लिए VLOOKUP और MATCH फ़ंक्शंस के संयोजन का उपयोग कर सकते हैं।

कृपया निम्नलिखित सूत्र को एक रिक्त कक्ष में लागू करें, और फिर दबाएँ दर्ज परिणाम प्राप्त करने की कुंजी.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

3.2 दो या दो से अधिक मानदंडों के आधार पर VLOOKUP मिलान मान

आपके लिए एक मानदंड के आधार पर मिलान मान को देखना आसान है, लेकिन यदि आपके पास दो या दो से अधिक मानदंड हैं, तो आप क्या कर सकते हैं?

 3.2.1 सूत्रों के साथ दो या दो से अधिक मानदंडों के आधार पर VLOOKUP मिलान मूल्य

इस मामले में, एक्सेल में लुकअप या मैच और इंडेक्स फ़ंक्शन आपको इस काम को जल्दी और आसानी से हल करने में मदद कर सकते हैं।

उदाहरण के लिए, मेरे पास नीचे दी गई डेटा तालिका है, विशिष्ट उत्पाद और आकार के आधार पर मिलान मूल्य वापस करने के लिए, निम्नलिखित सूत्र आपकी मदद कर सकते हैं।

चरण 1: कोई भी एक फॉर्मूला लागू करें

फ़ॉर्मूला 1: फॉर्मूला पेस्ट करने के बाद कृपया दबाएं दर्ज कुंजी।

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

फ़ॉर्मूला 2: फॉर्मूला पेस्ट करने के बाद कृपया दबाएं Ctrl + Shift + Enter चांबियाँ।

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

रिजल्ट:

 3.2.2 स्मार्ट सुविधा के साथ दो या दो से अधिक मानदंडों के आधार पर वीलुकअप मिलान मूल्य

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

1. क्लिक करें कुटूल > सुपर लुकअप > मल्टी-कंडीशन लुकअप इस सुविधा को सक्षम करने के लिए
2. फिर अपने डेटा के आधार पर संवाद बॉक्स से संचालन निर्दिष्ट करें।

3.3 एक या अधिक मानदंडों के साथ एकाधिक मान लौटाने के लिए VLOOKUP

एक्सेल में, VLOOKUP फ़ंक्शन एक मान की खोज करता है और यदि कई संबंधित मान पाए जाते हैं तो केवल पहला मिलान मान लौटाता है। कभी-कभी, आप सभी संबंधित मानों को एक पंक्ति में, एक कॉलम में या एक सेल में वापस करना चाह सकते हैं। यह अनुभाग इस बारे में बात करेगा कि किसी कार्यपुस्तिका में एक या अधिक शर्तों के साथ एकाधिक मिलान मानों को कैसे लौटाया जाए।

 3.3.1 क्षैतिज रूप से एक या अधिक स्थितियों के आधार पर सभी मिलान मानों को VLOOKUP करें

यह मानते हुए कि आपके पास डेटा की एक तालिका है जिसमें देश, शहर और नाम A1:C14 श्रेणी में हैं, और अब, आप क्षैतिज रूप से उन सभी नामों को वापस करना चाहते हैं जो "US" से हैं जैसा कि नीचे स्क्रीनशॉट में दिखाया गया है। कृपया इस कार्य को हल करें चरण दर चरण परिणाम प्राप्त करने के लिए यहां क्लिक करें.

 3.3.2 एक या अधिक स्थितियों के आधार पर सभी मिलान मानों को लंबवत रूप से VLOOKUP करें

यदि आपको नीचे दिखाए गए स्क्रीनशॉट के अनुसार विशिष्ट मानदंडों के आधार पर Vlookup करने और सभी मिलान मानों को लंबवत रूप से वापस करने की आवश्यकता है, कृपया विवरण में समाधान प्राप्त करने के लिए यहां क्लिक करें.

 3.3.3 एक या अधिक स्थितियों के आधार पर सभी मिलान मानों को एकल कक्ष में VLOOKUP करें

यदि आप Vlookup करना चाहते हैं और निर्दिष्ट विभाजक के साथ एक ही सेल में कई मिलान किए गए मान लौटाना चाहते हैं, TEXTJOIN का नया फ़ंक्शन आपको इस काम को जल्दी और आसानी से हल करने में मदद कर सकता है.

3.4 मिलान किए गए सेल की पूरी पंक्ति वापस करने के लिए VLOOKUP

इस अनुभाग में, मैं VLOOKUP फ़ंक्शन का उपयोग करके मिलान किए गए मान की संपूर्ण पंक्ति को पुनः प्राप्त करने के तरीके के बारे में बात करूंगा।

चरण 1: निम्नलिखित सूत्र लागू करें और भरें

कृपया नीचे दिए गए फॉर्मूले को एक रिक्त सेल में कॉपी करें या टाइप करें जहां आप परिणाम आउटपुट करना चाहते हैं, और दबाएँ दर्ज पहला मान प्राप्त करने के लिए कुंजी. फिर, फॉर्मूला सेल को दाईं ओर तब तक खींचें जब तक कि पूरी पंक्ति का डेटा प्रदर्शित न हो जाए।

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

रिजल्ट:

अब, आप देख सकते हैं कि संपूर्ण पंक्ति डेटा वापस आ गया है। स्क्रीनशॉट देखें:

3.5 एक्सेल में नेस्टेड वीलुकअप

कभी-कभी, आपको उन मानों को देखने की आवश्यकता हो सकती है जो कई तालिकाओं में आपस में जुड़े हुए हैं। इस मामले में, आप अंतिम मान प्राप्त करने के लिए एकाधिक VLOOKUP फ़ंक्शन को एक साथ जोड़ सकते हैं।

उदाहरण के लिए, मेरे पास एक वर्कशीट है जिसमें दो अलग-अलग तालिकाएँ हैं। पहली तालिका सभी उत्पाद नामों को उनके संबंधित सेल्समैन के साथ सूचीबद्ध करती है। दूसरी तालिका प्रत्येक विक्रेता की कुल बिक्री को सूचीबद्ध करती है। अब, यदि आप प्रत्येक उत्पाद की बिक्री का पता लगाना चाहते हैं, जैसा कि निम्नलिखित स्क्रीनशॉट में दिखाया गया है, तो आप इस कार्य को पूरा करने के लिए VLOOKUP फ़ंक्शन को नेस्ट कर सकते हैं।

नेस्टेड VLOOKUP फ़ंक्शन का सामान्य सूत्र है:

चरण 1: निम्नलिखित सूत्र लागू करें और भरें

कृपया निम्नलिखित सूत्र को एक रिक्त कक्ष में लागू करें, और फिर भरण हैंडल को उन कक्षों तक नीचे खींचें जिन पर आप इस सूत्र को लागू करना चाहते हैं।

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

रिजल्ट:

अब, आपको निम्न स्क्रीनशॉट में दिखाए अनुसार परिणाम मिलेगा:

3.6 जांचें कि क्या किसी अन्य कॉलम में सूची डेटा के आधार पर मान मौजूद है

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

चरण 1: निम्नलिखित सूत्र लागू करें और भरें

कृपया निम्नलिखित सूत्र को एक रिक्त कक्ष में लागू करें, फिर, भरण हैंडल को उन कक्षों तक नीचे खींचें जिनमें आप इस सूत्र को भरना चाहते हैं।

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

रिजल्ट:

और आपको आवश्यकतानुसार परिणाम मिलेगा, स्क्रीनशॉट देखें:

3.7 VLOOKUP और सभी मिलान किए गए मानों को पंक्तियों या स्तंभों में जोड़ें

संख्यात्मक डेटा के साथ काम करते समय, आपको किसी तालिका से मिलान किए गए मान निकालने और संख्याओं को कई कॉलम या पंक्तियों में जोड़ने की आवश्यकता हो सकती है। यह अनुभाग कुछ सूत्रों का परिचय देगा जो इस कार्य को पूरा करने में आपकी सहायता कर सकते हैं।

 3.7.1 VLOOKUP और एक पंक्ति या एकाधिक पंक्तियों में सभी मिलान किए गए मानों का योग करें

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

चरण 1: निम्नलिखित सूत्र लागू करें और भरें

कृपया निम्नलिखित सूत्र को किसी रिक्त कक्ष में कॉपी करें या दर्ज करें, और फिर दबाएँ Ctrl + Shift + Enter पहला परिणाम प्राप्त करने के लिए कुंजियाँ एक साथ। फिर, इस फ़ॉर्मूले को अपनी ज़रूरत की अन्य कोशिकाओं में कॉपी करने के लिए भरण हैंडल को नीचे खींचें।

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

रिजल्ट:

पहले मिलान मूल्य की एक पंक्ति के सभी मानों को एक साथ जोड़ दिया गया है, स्क्रीनशॉट देखें:

 3.7.2 वीलुकअप करें और सभी मिलान किए गए मानों को एक कॉलम या एकाधिक कॉलम में जोड़ें

यदि आप विशिष्ट महीनों के लिए कुल मूल्य का योग करना चाहते हैं जैसा कि नीचे स्क्रीनशॉट में दिखाया गया है। सामान्य VLOOKUP फ़ंक्शन आपकी मदद नहीं कर सकता है, यहां आपको फॉर्मूला बनाने के लिए SUM, INDEX और MATCH फ़ंक्शन को एक साथ लागू करना चाहिए।

चरण 1: निम्नलिखित सूत्र लागू करें

नीचे दिए गए सूत्र को एक रिक्त कक्ष में लागू करें, और फिर इस सूत्र को अन्य कक्षों में कॉपी करने के लिए भरण हैंडल को नीचे खींचें।

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

रिजल्ट:

अब, किसी कॉलम में विशिष्ट महीने के आधार पर पहले मिलान किए गए मानों को एक साथ जोड़ दिया गया है, स्क्रीनशॉट देखें:

 3.7.3 VLOOKUP और पहले मिलान किए गए या सभी मिलान किए गए मानों को एक शक्तिशाली सुविधा के साथ जोड़ें

हो सकता है कि उपरोक्त सूत्र आपके लिए याद रखना कठिन हो, इस मामले में, मैं एक शक्तिशाली सुविधा की अनुशंसा करूंगा - लुकअप और योग of एक्सेल के लिए कुटूल, इस सुविधा के साथ, आप पंक्तियों या स्तंभों में पहले मिलान या सभी मिलान मानों को यथासंभव आसानी से Vlookup और जोड़ सकते हैं।

1. क्लिक करें कुटूल > सुपर लुकअप > लुकअप और योग इस सुविधा को सक्षम करने के लिए
2. फिर अपनी आवश्यकता के आधार पर संवाद बॉक्स से संचालन निर्दिष्ट करें।

 3.7.4 VLOOKUP और पंक्तियों और स्तंभों दोनों में सभी मिलान किए गए मानों का योग करें

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

यहां, आप इस कार्य को पूरा करने के लिए SUMPRODCT फ़ंक्शन का उपयोग कर सकते हैं।

कृपया निम्नलिखित सूत्र को एक सेल में लागू करें, और फिर दबाएँ दर्ज परिणाम प्राप्त करने के लिए कुंजी, स्क्रीनशॉट देखें:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

3.8 कुंजी कॉलम के आधार पर दो तालिकाओं को मर्ज करने के लिए VLOOKUP

अपने दैनिक कार्य में, डेटा का विश्लेषण करते समय, आपको एक या अधिक प्रमुख स्तंभों के आधार पर सभी आवश्यक जानकारी को एक तालिका में एकत्र करने की आवश्यकता हो सकती है। इस कार्य को पूरा करने के लिए, आप VLOOKUP फ़ंक्शन के बजाय INDEX और MATCH फ़ंक्शन का उपयोग कर सकते हैं।

 3.8.1 एक कुंजी कॉलम के आधार पर दो तालिकाओं को मर्ज करने के लिए VLOOKUP

उदाहरण के लिए, आपके पास दो तालिकाएँ हैं, पहली तालिका में उत्पाद और नाम डेटा हैं, और दूसरी तालिका में उत्पाद और ऑर्डर डेटा हैं, अब, आप सामान्य उत्पाद कॉलम को एक तालिका में मिलान करके इन दोनों तालिकाओं को संयोजित करना चाहते हैं।

चरण 1: निम्नलिखित सूत्र लागू करें और भरें

कृपया निम्नलिखित सूत्र को रिक्त कक्ष में लागू करें। फिर, भरण हैंडल को उन कक्षों तक नीचे खींचें जहां आप इस सूत्र को लागू करना चाहते हैं

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

रिजल्ट:

अब, आपको कुंजी कॉलम डेटा के आधार पर पहली तालिका में शामिल होने वाले ऑर्डर कॉलम के साथ एक मर्ज की गई तालिका मिलेगी।

 3.8.2 एकाधिक कुंजी कॉलम के आधार पर दो तालिकाओं को मर्ज करने के लिए VLOOKUP

यदि आप जिन दो तालिकाओं में शामिल होना चाहते हैं उनमें एकाधिक कुंजी कॉलम हैं, तो इन सामान्य कॉलमों के आधार पर तालिकाओं को मर्ज करने के लिए, कृपया नीचे दिए गए चरणों का पालन करें।

सामान्य सूत्र है:

चरण 1: निम्नलिखित सूत्र लागू करें

कृपया नीचे दिए गए सूत्र को उस रिक्त कक्ष में लागू करें जहां आप परिणाम डालना चाहते हैं, और फिर दबाएँ Ctrl + Shift + Enter पहला मिलान मूल्य प्राप्त करने के लिए कुंजियाँ एक साथ, स्क्रीनशॉट देखें:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

चरण 2: सूत्र को अन्य कक्षों में भरें

फिर, पहले सूत्र कक्ष का चयन करें, और इस सूत्र को अपनी आवश्यकतानुसार अन्य कक्षों में कॉपी करने के लिए भरण हैंडल को खींचें:

3.9 VLOOKUP अनेक कार्यपत्रकों में मिलान मान

क्या आपको कभी Excel में अनेक कार्यपत्रकों पर VLOOKUP निष्पादित करने की आवश्यकता पड़ी है? उदाहरण के लिए, यदि आपके पास डेटा श्रेणियों वाली तीन वर्कशीट हैं, और आप इन शीटों से मानदंड के आधार पर विशिष्ट मान पुनर्प्राप्त करना चाहते हैं, तो आप चरण-दर-चरण ट्यूटोरियल का अनुसरण कर सकते हैं एकाधिक कार्यपत्रकों में VLOOKUP मान इस कार्य को पूरा करने के लिए.

VLOOKUP मिलान किए गए मान सेल फ़ॉर्मेटिंग बनाए रखते हैं

मिलान किए गए मानों को देखते समय, मूल सेल स्वरूपण जैसे फ़ॉन्ट रंग, पृष्ठभूमि रंग, डेटा प्रारूप इत्यादि नहीं रखा जाएगा। सेल या डेटा फ़ॉर्मेटिंग को बनाए रखने के लिए, यह अनुभाग नौकरियों को हल करने के लिए कुछ तरकीबें पेश करेगा।

4.1 VLOOKUP मिलान मान और सेल रंग, फ़ॉन्ट फ़ॉर्मेटिंग रखें

जैसा कि हम सभी जानते हैं, सामान्य VLOOKUP फ़ंक्शन केवल किसी अन्य डेटा श्रेणी से मिलान मान प्राप्त कर सकता है। हालाँकि, ऐसे उदाहरण हो सकते हैं जहाँ आप सेल फ़ॉर्मेटिंग के साथ संबंधित मान प्राप्त करना चाहेंगे, जैसे कि भरण रंग, फ़ॉन्ट रंग और फ़ॉन्ट शैली। इस अनुभाग में, हम चर्चा करेंगे कि Excel में स्रोत स्वरूपण को संरक्षित करते हुए मिलान मानों को कैसे पुनः प्राप्त किया जाए।

कृपया सेल फ़ॉर्मेटिंग के साथ इसके संबंधित मान को देखने और वापस करने के लिए निम्नलिखित चरण अपनाएँ:

चरण 1: कोड 1 को शीट कोड मॉड्यूल में कॉपी करें

1. वर्कशीट में वह डेटा है जिसे आप VLOOKUP करना चाहते हैं, शीट टैब पर राइट क्लिक करें और चुनें कोड देखें संदर्भ मेनू से. स्क्रीनशॉट देखें:
   
2. खुले में अनुप्रयोगों के लिए माइक्रोसॉफ्ट विज़ुअल बेसिक विंडो, कृपया नीचे दिए गए VBA कोड को कोड विंडो में कॉपी करें।
3. वीबीए कोड 1: लुकअप वैल्यू के साथ सेल फ़ॉर्मेटिंग प्राप्त करने के लिए वीलुकअप

4. Sub Worksheet_Change(ByVal Target As Range)
   'Updateby Extendoffice
       Dim I As Long
       Dim xKeys As Long
       Dim xDicStr As String
       On Error Resume Next
       Application.ScreenUpdating = False
       xKeys = UBound(xDic.Keys)
       If xKeys >= 0 Then
           For I = 0 To UBound(xDic.Keys)
               xDicStr = xDic.Items(I)
               If xDicStr <> "" Then
                   Range(xDic.Keys(I)).Interior.Color = _
                   Range(xDic.Items(I)).Interior.Color
                   Range(xDic.Keys(I)).Font.FontStyle = _
                   Range(xDic.Items(I)).Font.FontStyle
                   Range(xDic.Keys(I)).Font.Size = _
                   Range(xDic.Items(I)).Font.Size
                   Range(xDic.Keys(I)).Font.Color = _
                   Range(xDic.Items(I)).Font.Color
                   Range(xDic.Keys(I)).Font.Name = _
                   Range(xDic.Items(I)).Font.Name
                   Range(xDic.Keys(I)).Font.Underline = _
                   Range(xDic.Items(I)).Font.Underline
               Else
                   Range(xDic.Keys(I)).Interior.Color = xlNone
               End If
           Next
           Set xDic = Nothing
       End If
       Application.ScreenUpdating = True
   End Sub
   

5. 

चरण 2: कोड 2 को मॉड्यूल विंडो में कॉपी करें

1. अभी भी में अनुप्रयोगों के लिए माइक्रोसॉफ्ट विज़ुअल बेसिक खिड़की, क्लिक करें सम्मिलित करें > मॉड्यूल, और फिर नीचे दिए गए VBA कोड 2 को मॉड्यूल विंडो में कॉपी करें।
2. वीबीए कोड 2: लुकअप वैल्यू के साथ सेल फ़ॉर्मेटिंग प्राप्त करने के लिए वीलुकअप

3. Public xDic As New Dictionary
   Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
       Dim xFindCell As Range
       On Error Resume Next
       Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
       If xFindCell Is Nothing Then
           LookupKeepFormat = ""
           xDic.Add Application.Caller.Address, ""
       Else
           LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
           xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
       End If
   End Function
   

4. 

चरण 3: VBAप्रोजेक्ट के लिए विकल्प चुनें

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

चरण 4: परिणाम प्राप्त करने के लिए सूत्र टाइप करें

1. अब, वर्कशीट पर वापस जाएँ, निम्नलिखित सूत्र लागू करें। और फिर, सभी परिणामों को उनके स्वरूपण के साथ प्राप्त करने के लिए भरण हैंडल को नीचे खींचें। स्क्रीनशॉट देखें:
   

   =LookupKeepFormat(E2,$A$1:$C$10,3)

   
   

4.2 VLOOKUP लौटाए गए मान से दिनांक प्रारूप रखें

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

चरण 1: निम्नलिखित सूत्र लागू करें और भरें

कृपया नीचे दिए गए सूत्र को एक रिक्त कक्ष में लागू करें। फिर, इस सूत्र को अन्य कक्षों में कॉपी करने के लिए भरण हैंडल को खींचें।

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

रिजल्ट:

नीचे दिखाए गए स्क्रीनशॉट के अनुसार सभी मिलान तिथियां वापस कर दी गई हैं:

4.3 VLOOKUP से सेल टिप्पणी लौटाएँ

क्या आपको कभी Excel में VLOOKUP का उपयोग करके मेल खाने वाले सेल डेटा और उससे संबंधित टिप्पणी दोनों को पुनः प्राप्त करने की आवश्यकता पड़ी है, जैसा कि निम्नलिखित स्क्रीनशॉट में दिखाया गया है? यदि हां, तो नीचे दिया गया उपयोगकर्ता परिभाषित फ़ंक्शन इस कार्य को पूरा करने में आपकी सहायता कर सकता है।

चरण 1: कोड को एक मॉड्यूल में कॉपी करें

1. नीचे पकड़ो ALT + F11 कुंजी को खोलने के लिए अनुप्रयोगों के लिए माइक्रोसॉफ्ट विज़ुअल बेसिक खिड़की.
2. क्लिक करें सम्मिलित करें > मॉड्यूल, फिर मॉड्यूल विंडो में निम्नलिखित कोड को कॉपी और पेस्ट करें।
   वीबीए कोड: वीलुकअप और सेल टिप्पणी के साथ मिलान मूल्य लौटाएं:

   Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
   'Updateby Extendoffice
       Application.Volatile
       Dim xRet As Variant 'could be an error
       Dim xCell As Range
       xRet = Application.Match(LookVal, FTable.Columns(1), FType)
       If IsError(xRet) Then
           VlookupComment = "Not Found"
       Else
           Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
           VlookupComment = xCell.Value
           With Application.Caller
               If Not .Comment Is Nothing Then
                   .Comment.Delete
               End If
               If Not xCell.Comment Is Nothing Then
                   .AddComment xCell.Comment.Text
               End If
           End With
       End If
   End Function

3. फिर कोड विंडो को सेव करें और बंद करें।

चरण 2: परिणाम प्राप्त करने के लिए सूत्र टाइप करें

1. अब, निम्न सूत्र दर्ज करें, और इस सूत्र को अन्य कक्षों में कॉपी करने के लिए भरण हैंडल को खींचें। यह मिलान किए गए मान और टिप्पणियाँ दोनों एक साथ लौटाएगा, स्क्रीनशॉट देखें:
   

   =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

   
   

4.4 वीलुकअप नंबर टेक्स्ट के रूप में संग्रहीत

उदाहरण के लिए, मेरे पास डेटा की एक श्रृंखला है जहां मूल तालिका में आईडी नंबर संख्या प्रारूप में है और लुकअप सेल में आईडी नंबर टेक्स्ट के रूप में संग्रहीत है, सामान्य VLOOKUP फ़ंक्शन का उपयोग करते समय आपको #N/A त्रुटि का सामना करना पड़ सकता है। इस मामले में, सही जानकारी प्राप्त करने के लिए, आप टेक्स्ट और वैल्यू फ़ंक्शन को VLOOKUP फ़ंक्शन के भीतर लपेट सकते हैं। इसे प्राप्त करने का सूत्र नीचे दिया गया है:

चरण 1: निम्नलिखित सूत्र लागू करें और भरें

कृपया निम्नलिखित सूत्र को एक रिक्त कक्ष में लागू करें, और फिर इस सूत्र की प्रतिलिपि बनाने के लिए भरण हैंडल को नीचे खींचें।

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

रिजल्ट:

अब, आपको नीचे दिखाए गए स्क्रीनशॉट के अनुसार सही परिणाम मिलेंगे: