डेटा मैपिंग एक्सेल का एक अनिवार्य हिस्सा है। इसलिए, डेटा मैप करने के कुछ आसान तरीकों को जानने से बहुत समय बच सकता है और वर्कफ़्लो में सुधार हो सकता है। इसे ध्यान में रखते हुए, यह लेख 4 उपयोगी तरीके प्रदर्शित करता है कि कैसे एक्सेल VLOOKUP में डेटा मैप करें। इसके अलावा, हम यह भी चर्चा करेंगे कि किसी मान की Nवीं घटना कैसे प्राप्त करें, और VLOOKUP फ़ंक्शन का उपयोग करके त्रुटियों को कैसे छिपाएं।
आप नीचे दिए गए लिंक से अभ्यास वर्कबुक डाउनलोड कर सकते हैं।
VLOOKUP का उपयोग करके एक्सेल में डेटा मैप करने के 4 तरीके
इस लेख में, हम डेटा मैप करने के लिए VLOOKUP फ़ंक्शन को MATCH, COUNTIF, INDIRECT, और IF फ़ंक्शन के साथ जोड़ेंगे। तो, बिना किसी देरी के, आइए गोता लगाएं! यहां, हमने माइक्रोसॉफ्ट एक्सेल 365 संस्करण का उपयोग किया है, आप अपनी सुविधा के अनुसार किसी अन्य संस्करण का उपयोग कर सकते हैं।
विधि-1: एक्सेल में डेटा मैप करने के लिए VLOOKUP फ़ंक्शन का उपयोग करना
आइए सबसे स्पष्ट विधि यानी एक्सेल में डेटा मैप करने के लिए VLOOKUP फ़ंक्शन का उपयोग करके शुरू करें। तो, शुरू करते हैं। B4:D14 सेल में दिखाए गए डेटासेट पर विचार करें। यहां, डेटासेट कर्मचारी ID, उनके नाम, और विभाग को दिखाता है जहां वे काम करते हैं।
चरण:
- सबसे पहले, G5 सेल पर जाएं और नीचे दिया गया व्यंजक दर्ज करें।
=VLOOKUP(G4,B5:D14,3,FALSE)
इस सूत्र में, G4 सेल ID 1008 को संदर्भित करता है और B5:D14 सेल की रेंज ID, नाम, और विभाग स्तंभों का प्रतिनिधित्व करती है।
सूत्र विश्लेषण:
- VLOOKUP(G4,B5:D14,3,FALSE) → एक तालिका के बाएं-most स्तंभ में किसी मान को खोजता है, और फिर आपके द्वारा निर्दिष्ट स्तंभ से उसी पंक्ति में एक मान लौटाता है। यहां, G4 ( lookup_value तर्क) को B5:D14 (table_array तर्क) सरणी से मैप किया गया है। अगला, 3 (col_index_num तर्क) लुकअप मान के स्तंभ संख्या का प्रतिनिधित्व करता है। अंत में, FALSE (range_lookup तर्क) लुकअप मान के सटीक मिलान को संदर्भित करता है।
- आउटपुट → मार्केटिंग
अंत में, परिणाम नीचे दी गई छवि की तरह दिखना चाहिए।
विधि-2: VLOOKUP और MATCH फ़ंक्शन के साथ डेटा मैपिंग (टू-वे VLOOKUP)
हमारी दूसरी विधि में, हम एक विशेष पंक्ति और स्तंभ के प्रतिच्छेदन पर किसी मान को मैप करने के लिए VLOOKUP और MATCH फ़ंक्शन को जोड़ेंगे। इसे टू-वे VLOOKUP के रूप में भी जाना जाता है। तो, आइए इसे कार्य करते हुए देखें। B4:E12 सेल में दिखाए गए बिक्री सूची डेटासेट को मान लें। यहां, हमारे पास आइटम की सूची और जनवरी, फरवरी, और मार्च में बिकी इकाइयां हैं।
चरण:
- प्रारंभ में, आइटम और महीना चुनें, उदाहरण के लिए, हमने क्रमशः टेलीविजन और मार्च को चुना है।
- अगला, H6 सेल पर जाएं और नीचे दिया गया सूत्र दर्ज करें।
=VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE)
यहां, H4 और H5 सेल क्रमशः आइटम और महीना को संदर्भित करते हैं जबकि B5:E5 स्तंभ शीर्षकों का प्रतिनिधित्व करता है।
सूत्र विश्लेषण:
- MATCH(H5, B5:E5, 0) → दिए गए मान से मेल खाने वाली सरणी में किसी आइटम की सापेक्ष स्थिति लौटाता है। यहां, H5 lookup_value तर्क है जो मार्च महीने को संदर्भित करता है। इसके बाद, B5:E5 lookup_array तर्क का प्रतिनिधित्व करता है जहां से मान का मिलान किया जाता है। हाल ही में, 0 वैकल्पिक match_type तर्क है जो सटीक मिलान मानदंड को इंगित करता है।
- आउटपुट → 4
- VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE) → बन जाता है
- VLOOKUP(H4, B6:E10, 4, FALSE) → यहां, H4 ( lookup_value तर्क) को B6:E10 (table_array तर्क) सरणी से मैप किया गया है। अगला, 4 (col_index_num तर्क) लुकअप मान के स्तंभ संख्या का प्रतिनिधित्व करता है। अंत में, FALSE (range_lookup तर्क) लुकअप मान के सटीक मिलान को संदर्भित करता है।
- आउटपुट → 243
अंत में, आपका परिणाम नीचे दी गई तस्वीर की तरह दिखना चाहिए।
विधि-3: डेटा मैप करने के लिए VLOOKUP और COUNTIF फ़ंक्शन का उपयोग करना
एक्सेल में डेटा मैप करने का एक और तरीका है COUNTIF फ़ंक्शन को VLOOKUP फ़ंक्शन के भीतर शामिल करना। यह सरल और आसान है, बस इन चरणों का पालन करें। B4:C11 सेल में दिखाए गए सबसे ज्यादा बिकने वाली किताबें डेटासेट पर विचार करें। इस डेटासेट में, हमारे पास बेस्ट सेलर के नाम और इसकी कीमत USD में हैं।
चरण:
- शुरू करने के लिए, F5 सेल पर जाएं और नीचे दिया गया व्यंजक दर्ज करें।
=IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0)
इस व्यंजक में, B5:C9 सेल की रेंज बेस्ट सेलर बुक और कीमत स्तंभों का प्रतिनिधित्व करती है। इसके विपरीत, F4 सेल बेस्ट सेलर को संदर्भित करता है (यहां, यह हाउस ऑफ विजडम है)
सूत्र विश्लेषण:
- COUNTIF(B5:B9,F4) → दिए गए शर्त को पूरा करने वाली सीमा के भीतर सेल की संख्या गिनता है। यहां, B5:B9 रेंज तर्क है जो बेस्ट सेलर बुक्स को संदर्भित करता है। इसके बाद, F4 मानदंड तर्क का प्रतिनिधित्व करता है जो मिलान किए गए मान की गिनती लौटाता है।
- आउटपुट → 1
- VLOOKUP(F4,B5:C9,2,TRUE) → यहां, F4 ( lookup_value तर्क) को B5:C9 (table_array तर्क) सरणी से मैप किया गया है। अगला, 2 (col_index_num तर्क) लुकअप मान के स्तंभ संख्या का प्रतिनिधित्व करता है। अंत में, TRUE (range_lookup तर्क) लुकअप मान के अनुमानित मिलान को संदर्भित करता है।
- आउटपुट → 25
- IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0) → बन जाता है
- IF(1,25,0) → जांचता है कि क्या कोई शर्त पूरी होती है और TRUE होने पर एक मान और FALSE होने पर दूसरा मान लौटाता है। यहां, 1 logical_test तर्क है जो IF फ़ंक्शन को 25 (value_if_true तर्क) लौटाने के लिए प्रेरित करता है अन्यथा यह 0 (value_if_false तर्क) लौटाता है।
- आउटपुट → $25
नतीजतन, परिणाम नीचे दिखाए गए स्क्रीनशॉट की तरह दिखना चाहिए।
विधि-4: एक्सेल में VLOOKUP और INDIRECT फ़ंक्शन का उपयोग करके डेटा मैपिंग
आप INDIRECT और VLOOKUP फ़ंक्शन को जोड़कर एक्सेल में डेटा मैप भी कर सकते हैं। अब, आइए चरणों से गुजरते हैं। B4:I10 सेल में दिखाए गए किराना सूची डेटासेट मान लें। डेटासेट US के 3 शहरों में समान आइटम की कीमत दिखाता है।
चरण:
- सबसे पहले, B5 सेल >> डेटा टैब पर क्लिक करें >> फिर डेटा सत्यापन ड्रॉप-डाउन दबाएं।
यह डेटा सत्यापन डायलॉग बॉक्स खोलता है।
- दूसरे, अनुमति दें ड्रॉप-डाउन में सूची विकल्प चुनें >> स्रोत फ़ील्ड में, B6:B10 सेल चुनें >> ओके बटन पर क्लिक करें।
- तीसरे, B6:C10 सेल चुनें >> फ़ॉर्मूला टैब पर जाएं >> नाम परिभाषित करें विकल्प पर डबल-क्लिक करें।
यह नाम संपादित करें विज़ार्ड खोलता है।
- यहां, डेटा रेंज के लिए एक उपयुक्त नाम दर्ज करें (इस मामले में, बोस्टन) >> ओके बटन पर क्लिक करें।
इसी तरह, अटलांटा के लिए नामित रेंज परिभाषित करें।
इसी तरह, डेनवर के लिए नामित रेंज परिभाषित करें।
- चौथे, C14 सेल पर जाएं >> डेटा टैब पर, और डेटा सत्यापन बटन पर क्लिक करें।
- इसी तरह, सूची विकल्प चुनें >> स्क्रीनशॉट में दिखाए अनुसार नामित रेंज दर्ज करें।
अब, ड्रॉप-डाउन से आइटम और स्थान चुनें। उदाहरण के लिए, हमने क्रमशः टमाटर और अटलांटा को चुना है।
- उसके बाद, D14 सेल में निम्न व्यंजक टाइप करें।
=VLOOKUP(B14,INDIRECT(C14),2,FALSE)
यहां, B14 और C14 सेल क्रमशः आइटम और स्थान को इंगित करते हैं।
सूत्र विश्लेषण:
- INDIRECT(C14) → एक टेक्स्ट स्ट्रिंग द्वारा निर्दिष्ट संदर्भ लौटाता है। यहां, C14 ref_text तर्क है जो नामित रेंज बोस्टन को संदर्भित करता है।
- VLOOKUP(B14,INDIRECT(C14),2,FALSE) → यहां, B14 ( lookup_value तर्क) को नामित रेंज, INDIRECT(C14) से मैप किया गया है जो table_array तर्क है। अगला, 2 (col_index_num तर्क) लुकअप मान के स्तंभ संख्या का प्रतिनिधित्व करता है। अंत में, FALSE (range_lookup तर्क) लुकअप मान के सटीक मिलान को संदर्भित करता है।
- आउटपुट → $1.2
इसके बाद, उपरोक्त चरणों को पूरा करने के बाद परिणाम नीचे दी गई तस्वीर की तरह दिखना चाहिए।
इसके अलावा, आप इस विधि का उपयोग उस मामले में भी कर सकते हैं जब आपके पास कई वर्कशीट में डेटा हो और आप लुकअप मान के आधार पर विशिष्ट वर्कशीट से इसे मैप करना चाहते हों।
Nवीं घटना प्राप्त करने के लिए VLOOKUP फ़ंक्शन लागू करना
मान लीजिए आपके पास B4:D13 सेल में दिखाए गए स्टेशनरी बिक्री सूची है। अब, आप उसी ग्राहक द्वारा खरीदे गए दूसरे या तीसरे आइटम को जानना चाहते हैं। इस मुद्दे को हल करने के लिए, हम परिणाम प्राप्त करने के लिए COUNTIF और VLOOKUP फ़ंक्शन का उपयोग कर सकते हैं। तो, आइए प्रक्रिया को विस्तार से देखें।
चरण:
- सबसे पहले, स्तंभ B में एक स्तंभ डालें और इसके हेडर का नाम हेल्पर कॉलम रखें >> B5 सेल में नीचे दिया गया सूत्र दर्ज करें।
=C5&COUNTIF($C$5:C5, C5)
📄 नोट: कृपया सुनिश्चित करें कि हेल्पर कॉलम को डेटासेट के सबसे बाएं तरफ डालें, क्योंकि डिफ़ॉल्ट रूप से VLOOKUP फ़ंक्शन बाएं से दाएं देखता है।
यहां, C5 सेल नाम जॉन को संदर्भित करता है और COUNTIF फ़ंक्शन दिए गए रेंज $C$5:C5 से जॉन की घटना गिनता है। अंत में, एम्परसैंड (&) ऑपरेटर टेक्स्ट और संख्या को जोड़ता है।
- अगला, नाम और उदाहरण दर्ज करें, उदाहरण के लिए, यहां यह जूली, और 3 है >> H6 सेल पर जाएं और नीचे दिया गया व्यंजक टाइप करें।
=VLOOKUP(H4&H5, B5:E13, 3, FALSE)
इस सूत्र में, H4 और H5 सेल नाम और उदाहरण को इंगित करते हैं।
उपरोक्त चरणों को पूरा करने के बाद, परिणाम नीचे दिखाए गए स्क्रीनशॉट की तरह दिखना चाहिए।
#N/A त्रुटि छिपाने के लिए VLOOKUP और IF फ़ंक्शन का उपयोग करना
जब एक अमान्य लुकअप मान दिया जाता है तो आप ISNA और VLOOKUP फ़ंक्शन को IF फ़ंक्शन में नियोजित करके #N/A त्रुटियों को छिपा सकते हैं। अब, मुझे नीचे प्रक्रिया प्रदर्शित करने दें।
चरण:
- सबसे पहले, G5 सेल पर जाएं और नीचे दिया गया व्यंजक दर्ज करें।
=IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), "",VLOOKUP(G4, B5:D14,3,FALSE))
इस सूत्र में, G4 सेल ID 1008 को संदर्भित करता है और B5:D14 सेल की रेंज ID, नाम, और विभाग स्तंभों का प्रतिनिधित्व करती है।
सूत्र विश्लेषण:
- ISNA(VLOOKUP(G4, B5:D14,3,FALSE)) → जांचता है कि कोई मान #NA है या नहीं, और TRUE या FALSE लौटाता है। यहां, G4 ( lookup_value तर्क) को B5:D14 (table_array तर्क) सरणी से मैप किया गया है। अगला, 3 (col_index_num तर्क) लुकअप मान के स्तंभ संख्या का प्रतिनिधित्व करता है। अंत में, FALSE (range_lookup तर्क) लुकअप मान के सटीक मिलान को संदर्भित करता है।
- आउटपुट → FALSE
- IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), “”,VLOOKUP(G4, B5:D14,3,FALSE)) → बन जाता है
- IF(FALSE, “”,VLOOKUP(G4, B5:D14,3,FALSE)) → जांचता है कि क्या कोई शर्त पूरी होती है और TRUE होने पर एक मान और FALSE होने पर दूसरा मान लौटाता है। यहां, FALSE logical_test तर्क है जो IF फ़ंक्शन को VLOOKUP फ़ंक्शन (value_if_true तर्क) से मान लौटाने के लिए प्रेरित करता है अन्यथा यह खाली “” (value_if_false तर्क) लौटाता है।
- आउटपुट → HR
आखिरकार, आपको नीचे दी गई तस्वीर में दिखाए गए परिणाम मिलने चाहिए।
इसके अलावा, यदि हम एक अमान्य ID नंबर (ID 1012) दर्ज करते हैं, तो सूत्र #N/A त्रुटि के बजाय एक खाली मान लौटाता है जो नीचे दिए गए स्क्रीनशॉट में दिखाया गया है।
अभ्यास अनुभाग
हमने प्रत्येक शीट के दाईं ओर एक अभ्यास अनुभाग प्रदान किया है ताकि आप स्वयं अभ्यास कर सकें। कृपया इसे स्वयं करना सुनिश्चित करें।
निष्कर्ष
मुझे आशा है कि VLOOKUP का उपयोग करके एक्सेल में डेटा कैसे मैप करें के सभी तरीके अब आपको अपने एक्सेल स्प्रेडशीट में उन्हें और अधिक प्रभावी ढंग से लागू करने के लिए प्रेरित करेंगे। यदि आपके कोई प्रश्न या प्रतिक्रिया है, तो कृपया मुझे कमेंट सेक्शन में बताएं। या आप इस वेबसाइट पर एक्सेल फ़ंक्शन से संबंधित हमारे अन्य लेख देख सकते हैं।
