एक्सेल में वीलुकअप कैसे करें - अल्टीमेट वीलुकअप गाइड

उदाहरण कार्यपुस्तिका डाउनलोड करें

उदाहरण कार्यपुस्तिका डाउनलोड करें

यह ट्यूटोरियल दर्शाता है कि कैसे उपयोग करना है एक्सेल वीलुकअप फंक्शन एक्सेल में एक मूल्य देखने के लिए।

VLOOKUP फंक्शन अवलोकन

VLOOKUP फंक्शन Vlookup वर्टिकल लुकअप के लिए है। यह किसी तालिका के सबसे बाएं कॉलम में एक मान की खोज करता है। फिर पाया गया मान से दाईं ओर एक निर्दिष्ट संख्या में कॉलम का मान लौटाता है। यह एक hlookup के समान है, सिवाय इसके कि यह मानों को क्षैतिज रूप से देखने के बजाय लंबवत रूप से देखता है।

(ध्यान दें कि सूत्र इनपुट कैसे दिखाई देते हैं)

VLOOKUP फ़ंक्शन सिंटैक्स और तर्क:

1	=VLOOKUP (लुकअप_वैल्यू, टेबल_एरे, col_index_num, रेंज_लुकअप)

पता लगाने का मूल्य - वह मान जिसे आप खोजना चाहते हैं।

तालिका सरणी - वह डेटा श्रेणी जिसमें वह मान होता है जिसे आप खोजना चाहते हैं और वह मान जो आप चाहते हैं कि vlookup वापस आए। खोज मानों वाला कॉलम सबसे बाईं ओर वाला कॉलम होना चाहिए।

col_index_num - डेटा श्रेणी का कॉलम नंबर, जिससे आप कोई मान वापस करना चाहते हैं।

रेंज देखना - सही या गलत। FALSE सटीक मिलान की खोज करता है। TRUE निकटतम मिलान की खोज करता है जो लुकअप_वैल्यू से कम या उसके बराबर है। यदि TRUE चुना जाता है तो सबसे बाएं कॉलम (लुकअप कॉलम) को आरोही (निम्नतम से उच्चतम) क्रमबद्ध किया जाना चाहिए।

वीलुकअप फंक्शन क्या है?

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

मूल उदाहरण

आइए एक ग्रेड बुक से डेटा का एक नमूना देखें। विशिष्ट छात्रों के लिए जानकारी निकालने के लिए हम कई उदाहरणों से निपटेंगे।

यदि हम यह जानना चाहते हैं कि बॉब किस वर्ग में है, तो हम सूत्र लिखेंगे:

1	=VLOOKUP ("बॉब", A2:C5, 2, FALSE)

याद रखने वाली महत्वपूर्ण बातें यह हैं कि जिस आइटम को हम (बॉब) खोज रहे हैं, वह हमारी खोज श्रेणी (A2:C5) के पहले कॉलम में होना चाहिए। हमने फ़ंक्शन को बताया है कि हम 2 . से एक मान वापस करना चाहते हैं^रा कॉलम, जो इस मामले में कॉलम बी है। अंत में, हमने संकेत दिया कि हम एक करना चाहते हैं सटीक मिलान असत्य को अंतिम तर्क के रूप में रखकर। यहां, उत्तर "पढ़ना" होगा।

साइड टिप: आप अंतिम तर्क के रूप में False के बजाय संख्या 0 का भी उपयोग कर सकते हैं, क्योंकि उनका मान समान है। कुछ लोग इसे पसंद करते हैं क्योंकि यह लिखने में तेज होता है। बस इतना जान लें कि दोनों स्वीकार्य हैं।

स्थानांतरित डेटा

हमारे पहले उदाहरण में कुछ स्पष्टीकरण जोड़ने के लिए, लुकअप आइटम को आपकी स्प्रैडशीट के कॉलम ए में नहीं होना चाहिए, केवल आपकी खोज श्रेणी के पहले कॉलम में होना चाहिए। आइए समान डेटा सेट का उपयोग करें:

आइए, अब विज्ञान की कक्षा के लिए ग्रेड ज्ञात करें। हमारा सूत्र होगा

1	=VLOOKUP ("विज्ञान", B2:C5, 2, FALSE)

यह अभी भी एक मान्य सूत्र है, क्योंकि हमारी खोज श्रेणी का पहला स्तंभ स्तंभ B है, जहां पर हमारा "विज्ञान" का खोज शब्द मिलेगा। हम 2 . से एक मान लौटा रहे हैं^रा खोज श्रेणी का कॉलम, जो इस मामले में कॉलम सी है। उत्तर तब "ए-" है।

वाइल्डकार्ड उपयोग

VLOOKUP फ़ंक्शन वाइल्डकार्ड "*" और "?" के उपयोग का समर्थन करता है खोज करते समय। उदाहरण के लिए, मान लें कि हम भूल गए हैं कि फ्रैंक का नाम कैसे लिखा जाता है, और हम केवल "F" से शुरू होने वाले नाम की खोज करना चाहते हैं। हम सूत्र लिख सकते हैं

1	=VLOOKUP("F*", A2:C5, 2, FALSE)

यह पंक्ति 5 में फ्रैंक नाम खोजने में सक्षम होगा, और फिर 2 . से मान वापस कर देगा^रा सापेक्ष स्तंभ। इस मामले में, उत्तर "विज्ञान" होगा।

गैर-सटीक मिलान

अधिकांश समय, आप यह सुनिश्चित करना चाहेंगे कि VLOOKUP में अंतिम तर्क गलत (या 0) है ताकि आपको सटीक मिलान मिल सके। हालांकि, कई बार आप एक गैर-सटीक मिलान की खोज कर रहे होंगे। यदि आपके पास सॉर्ट किए गए डेटा की एक सूची है, तो आप उस आइटम के परिणाम को वापस करने के लिए VLOOKUP का उपयोग कर सकते हैं जो या तो समान है, या अगला सबसे छोटा है। इसका उपयोग अक्सर संख्याओं की बढ़ती श्रेणियों, जैसे कर तालिका या कमीशन बोनस से निपटने के लिए किया जाता है।

मान लें कि आप सेल D2 में दर्ज आय के लिए कर की दर ज्ञात करना चाहते हैं। D4 में सूत्र हो सकता है:

1	=VLOOKUP(D2, A2:B6, 2, TRUE)

इस सूत्र में अंतर यह है कि हमारा अंतिम तर्क "सत्य" है। हमारे विशिष्ट उदाहरण में, हम देख सकते हैं कि जब हमारे व्यक्तिगत इनपुट $४५,००० की आय करते हैं तो उनकी कर दर १५% होगी।

ध्यान दें: यद्यपि हम आम तौर पर तर्क के रूप में गलत के साथ एक सटीक मिलान चाहते हैं, लेकिन आप 4 . निर्दिष्ट करना भूल जाते हैं^वां VLOOKUP में तर्क, डिफ़ॉल्ट सत्य है। यह आपको कुछ अनपेक्षित परिणाम प्राप्त करने का कारण बन सकता है, विशेष रूप से पाठ मानों के साथ व्यवहार करते समय।

गतिशील स्तंभ

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

आइए E2 और E4 में कुछ इनपुट के साथ हमारे ग्रेड बुक उदाहरण पर फिर से विचार करें। कॉलम संख्या प्राप्त करने के लिए, हम का सूत्र लिख सकते हैं

1	=MATCH(E2, A1:C1, 0)

यह A1:C1 की सीमा के भीतर "ग्रेड" की सटीक स्थिति खोजने का प्रयास करेगा। उत्तर 3 होगा। यह जानकर, हम इसे VLOOKUP फ़ंक्शन में प्लग कर सकते हैं और E6 में एक सूत्र लिख सकते हैं जैसे:

1	=VLOOKUP(E4, A2:C5, MATCH(E2, A1:C1, 0), 0)

तो, MATCH फ़ंक्शन 3 का मूल्यांकन करेगा, और यह VLOOKUP को 3 . से परिणाम वापस करने के लिए कहता है^{तृतीय} A2:C5 श्रेणी में कॉलम। कुल मिलाकर, हमें "सी" का वांछित परिणाम मिलता है। हमारा सूत्र अब गतिशील है जिसमें हम देखने के लिए कॉलम या खोजने के लिए नाम बदल सकते हैं।

वीलुकअप सीमाएं

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

Google पत्रक में VLOOKUP

VLOOKUP फ़ंक्शन ठीक उसी तरह काम करता है जैसे Google पत्रक में Excel में होता है:

अतिरिक्त नोट्स

वर्टिकल लुकअप करने के लिए VLOOKUP फ़ंक्शन का उपयोग करें। VLOOKUP सटीक मिलान की खोज करता है (रेंज देखना = FALSE) या निकटतम मिलान जो लुकअप_वैल्यू के बराबर या उससे कम है (रेंज देखना = TRUE, केवल संख्यात्मक मान) table_array की पहली पंक्ति में। इसके बाद यह मैच के नीचे पंक्तियों की संख्या, संबंधित मान देता है।

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

इसके बाद आप सीमा को परिभाषित करते हैं (जिसे कहा जाता है) तालिका सरणी) जिसमें शीर्ष पंक्ति में पहचानकर्ता होते हैं और जो भी मूल्य आप अंततः नीचे की पंक्तियों में खोजना चाहते हैं। महत्वपूर्ण: विशिष्ट पहचानकर्ता शीर्ष पंक्ति में होने चाहिए। यदि वे नहीं हैं, तो आपको या तो पंक्ति को शीर्ष पर ले जाना होगा, या VLOOKUP के बजाय MATCH / INDEX का उपयोग करना होगा।

तीसरा, पंक्ति संख्या को परिभाषित करें (पंक्ति_सूचकांक) का तालिका सरणी कि आप वापस लौटना चाहते हैं। ध्यान रखें कि अद्वितीय पहचानकर्ता वाली पहली पंक्ति पंक्ति 1 है। दूसरी पंक्ति पंक्ति 2 है, आदि।

अंत में, आपको यह बताना होगा कि सटीक मिलान (FALSE) की खोज करनी है या निकटतम मिलान (TRUE) में रेंज देखना. यदि सटीक मिलान विकल्प चुना जाता है, और सटीक मिलान नहीं मिलता है, तो एक त्रुटि दी जाती है (#N/A)। फ़ॉर्मूला को रिक्त या "नहीं मिला", या त्रुटि मान (#N/A) के बजाय कोई अन्य मान वापस करने के लिए VLOOKUP के साथ IFERROR फ़ंक्शन का उपयोग करें।

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

क्षैतिज लुकअप करने के लिए इसके बजाय HLOOKUP फ़ंक्शन का उपयोग करें।

VBA में VLOOKUP उदाहरण

आप VBA में VLOOKUP फ़ंक्शन का भी उपयोग कर सकते हैं। प्रकार:
application.worksheetfunction.vlookup(lookup_value,table_array,col_index_num,range_lookup)
फ़ंक्शन तर्कों (lookup_value, आदि) के लिए, आप या तो उन्हें सीधे फ़ंक्शन में दर्ज कर सकते हैं, या इसके बजाय उपयोग करने के लिए चर परिभाषित कर सकते हैं।

एक्सेल में सभी फंक्शन की सूची पर लौटें