एक्सेल में VLOOKUP मास्टर करें: कुशल डेटा पुनर्प्राप्ति के लिए पूर्ण गाइड

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

एक्सेल में VLOOKUP फ़ंक्शन कई लोगों को डराता है क्योंकि इसमें कई पैरामीटर होते हैं और इसका उपयोग करने के कई तरीके हैं। इस लेख में आप VLOOKUP के सभी उपयोग के तरीके सीखेंगे और जानेंगे कि यह फ़ंक्शन इतना शक्तिशाली क्यों है।

एक्सेल में VLOOKUP पैरामीटर

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

आइए इनमें से प्रत्येक पैरामीटर और उनके अर्थ की जांच करें।

• lookup_value: वह मान जिसे आप स्प्रेडशीट में ढूंढ रहे हैं
• table_array: शीट में सेल की वह रेंज जिसमें आप खोज करना चाहते हैं
• col_index_num: वह कॉलम जहां से आप अपना परिणाम निकालना चाहते हैं
• [range_lookup]: मिलान मोड (TRUE = सन्निकट, FALSE = सटीक)

ये चार पैरामीटर आपको बहुत बड़े डेटासेट के अंदर डेटा के लिए कई अलग-अलग, उपयोगी खोज करने की अनुमति देते हैं।

एक सरल VLOOKUP एक्सेल उदाहरण

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

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

ऐसे बड़े डेटासेट में अपनी रुचि के स्कूल को खोजने के लिए खोजना बहुत समय लेने वाला होगा।

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

अब, आपको इन तीन फ़ील्ड को काम करने के लिए एक्सेल में VLOOKUP फ़ंक्शन का उपयोग करना होगा। पढ़ाई फ़ील्ड में, VLOOKUP फ़ंक्शन को इस प्रकार बनाएं:

1. =VLOOKUP( टाइप करें
2. स्कूल फ़ील्ड चुनें, जो इस उदाहरण में I2 है। एक कॉमा टाइप करें।
3. उन सेल की पूरी रेंज चुनें जिसमें वह डेटा है जिसे आप देखना चाहते हैं। एक कॉमा टाइप करें।

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

नोट: एक्सेल में VLOOKUP फ़ंक्शन केवल खोज कॉलम के दाईं ओर के सेल के माध्यम से खोज कर सकता है। इस उदाहरण में, स्कूल के नाम का कॉलम उस डेटा के बाईं ओर होना चाहिए जिसे आप देख रहे हैं।

4. पढ़ाई का स्कोर पुनर्प्राप्त करने के लिए, आपको बाएं चयनित कॉलम से तीसरे कॉलम का चयन करना होगा। तो, 3 टाइप करें और फिर एक और कॉमा टाइप करें।
5. अंत में, सटीक मिलान के लिए FALSE टाइप करें, और फ़ंक्शन को ) के साथ बंद करें।

आपका अंतिम VLOOKUP फ़ंक्शन कुछ इस तरह दिखना चाहिए:

=VLOOKUP(I2,B2:G461,3,FALSE)

जब आप पहली बार एंटर दबाते हैं और फ़ंक्शन पूरा करते हैं, तो आप देखेंगे कि पढ़ाई फ़ील्ड में #N/A होगा।

ऐसा इसलिए है क्योंकि स्कूल फ़ील्ड खाली है और VLOOKUP फ़ंक्शन के लिए खोजने के लिए कुछ भी नहीं है। हालांकि, यदि आप जिस हाई स्कूल को देखना चाहते हैं उसका नाम दर्ज करते हैं, तो आप उस पंक्ति के लिए पढ़ाई के स्कोर से सही परिणाम देखेंगे।

VLOOKUP के केस-सेंसिटिव होने से कैसे निपटें

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

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

इससे निपटने के लिए, आप जो खोज रहे हैं उसे लुकअप परिणाम देखने से पहले लोअरकेस में बदलने के लिए मजबूर कर सकते हैं। ऐसा करने के लिए, जिस कॉलम को आप खोज रहे हैं उसके बगल में एक नया कॉलम बनाएं। फ़ंक्शन टाइप करें:

=TRIM(LOWER(B2))

यह स्कूल के नाम को लोअरकेस करेगा और नाम के बाएं या दाएं तरफ मौजूद किसी भी अनावश्यक वर्ण (स्पेस) को हटा देगा।

Shift कुंजी दबाए रखें और पहले सेल के निचले दाएं कोने पर माउस कर्सर रखें जब तक कि यह दो क्षैतिज रेखाओं में न बदल जाए। पूरे कॉलम को ऑटोफिल करने के लिए माउस को डबल क्लिक करें।

अंत में, चूंकि VLOOKUP इन सेल में फॉर्मूला के बजाय टेक्स्ट का उपयोग करने की कोशिश करेगा, आपको उन सभी को केवल मान में बदलना होगा। ऐसा करने के लिए, पूरे कॉलम को कॉपी करें, पहले सेल में राइट-क्लिक करें, और केवल मान पेस्ट करें।

अब जब आपका सारा डेटा इस नए कॉलम में साफ हो गया है, तो अपने एक्सेल में VLOOKUP फ़ंक्शन को थोड़ा संशोधित करें ताकि पिछले वाले के बजाय इस नए कॉलम का उपयोग किया जा सके, लुकअप रेंज को B2 के बजाय C2 से शुरू करके।

=VLOOKUP(I2,C2:G461,3,FALSE)

अब आप देखेंगे कि यदि आप हमेशा अपना खोज लोअरकेस में टाइप करते हैं, तो आपको हमेशा एक अच्छा खोज परिणाम मिलेगा।

यह VLOOKUP के केस-सेंसिटिव होने की समस्या को दूर करने के लिए एक आसान एक्सेल टिप है।

VLOOKUP सन्निकट मिलान

जबकि इस लेख के पहले खंड में वर्णित सटीक मिलान लुकअप उदाहरण काफी सीधा है, सन्निकट मिलान थोड़ा अधिक जटिल है।

सन्निकट मिलान का उपयोग संख्या रेंज के माध्यम से खोज करने के लिए सबसे अच्छा है। इसे सही ढंग से करने के लिए खोज रेंज को ठीक से क्रमबद्ध करने की आवश्यकता होती है। इसका सबसे अच्छा उदाहरण एक VLOOKUP फ़ंक्शन है जो एक संख्या ग्रेड से मेल खाने वाले अक्षर ग्रेड को खोजने के लिए है।

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

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

अब, VLOOKUP फ़ंक्शन और सन्निकट मिलान का उपयोग करके, आप सही संख्यात्मक रेंज से मेल खाने वाले उचित अक्षर ग्रेड को ढूंढ सकते हैं।

इस VLOOKUP फ़ंक्शन में:

• lookup_value: F2, अंतिम औसत ग्रेड
• table_array: I2:J8, अक्षर ग्रेड लुकअप रेंज
• index_column: 2, लुकअप टेबल में दूसरा कॉलम
• [range_lookup]: TRUE, सन्निकट मिलान

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

ध्यान दें कि एक्सेल में VLOOKUP फ़ंक्शन असाइन किए गए अक्षर स्कोर के साथ ग्रेड रेंज के निचले सिरे से अगले अक्षर स्कोर की रेंज के शीर्ष तक खोजता है।

तो, "C" को निचली रेंज (75) को सौंपा गया अक्षर होना चाहिए, और B को अपने स्वयं के अक्षर रेंज के नीचे (न्यूनतम) को सौंपा गया है। VLOOKUP 60 (D) के परिणाम को 60 से 75 के बीच किसी भी चीज़ के लिए निकटतम सन्निकट मान के रूप में "ढूंढेगा"।

एक्सेल में VLOOKUP एक बहुत शक्तिशाली फ़ंक्शन है जो लंबे समय से उपलब्ध है। यह एक्सेल वर्कबुक में कहीं भी मिलान मूल्यों को खोजने के लिए भी उपयोगी है।

हालांकि, ध्यान रखें कि जिन Microsoft उपयोगकर्ताओं के पास मासिक Office 365 सदस्यता है, उनके पास अब एक नए XLOOKUP फ़ंक्शन तक पहुंच है। इस फ़ंक्शन में अधिक पैरामीटर और अतिरिक्त लचीलापन है। अर्ध-वार्षिक सदस्यता वाले उपयोगकर्ताओं को जुलाई 2020 में अपडेट रोल आउट होने तक इंतजार करना होगा।