एक्सेल और गूगल शीट्स के लिए आवश्यक VLOOKUP गाइड

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

हम VLOOKUP से संबंधित विभिन्न प्रश्नों और सामान्य नुकसानों से भी निपटते हैं जिनका सामना आप कमांड का उपयोग करने की कोशिश करते समय कर सकते हैं।

VLOOKUP क्या है?

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

उदाहरण के लिए: आपके पास "नाम," "नंबर," और "पता" लेबल वाले तीन कॉलम हो सकते हैं, और ये कॉलम जानकारी से भरे हो सकते हैं। VLOOKUP का उपयोग करके, आप "नाम" कॉलम में एक विशेष नाम खोज पाएंगे, फिर उस जानकारी का उपयोग उसी पंक्ति के साथ मिलने वाले संबंधित नंबर या पते को प्रदर्शित करने के लिए कर सकते हैं। ध्यान रखें, हालांकि, कि VLOOKUP केस सेंसिटिव नहीं है।

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

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

चीजों को सरलता से रखने के लिए, एक VLOOKUP स्ट्रिंग इस प्रकार है:

=VLOOKUP( वह मान जिसे आप देखना चाहते हैं, सेल्स की रेंज जिसमें आप इसे देखना चाहते हैं, उस मान का कॉलम नंबर जिसे आप प्रदर्शित करना चाहते हैं, क्या आप सटीक या अनुमानित मिलान चाहते हैं )

एक्सेल और गूगल शीट्स में VLOOKUP का उपयोग कैसे करें

पहली नज़र में, VLOOKUP स्ट्रिंग डराने वाली लग सकती है, लेकिन ये गहन निर्देश दिखाएंगे कि यह वास्तव में दिखने से कहीं सरल है।

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

2. हम एक निश्चित फ़ील्ड में जो भी नाम है उसका संबंधित फोन नंबर खींचना चाहते हैं। इस मामले में, हम "आइरिस वॉटसन" के लिए फोन नंबर खींचना चाहते हैं।

3. एक खाली सेल पर डबल-क्लिक करके और =VLOOKUP( टाइप करके स्ट्रिंग शुरू करें। आप देखेंगे कि पहला आवश्यक मान "lookup_value" है। जो वह जानकारी है जिसका उपयोग आप फोन नंबर खोजने के लिए करेंगे।

4. चूंकि हमने पहले से ही अपने VLOOKUP से पहले वाले सेल में "आइरिस वॉटसन" नाम जोड़ा है, वह सेल वही है जिसे आप lookup_value के रूप में उपयोग करेंगे। आपका स्ट्रिंग वर्तमान में इस तरह दिखना चाहिए: =VLOOKUP(e12,

5. अगला है table_array। यह जानकारी की पूरी टेबल है जिससे आप डेटा ड्रॉ कर रहे हैं। बस पूरी टेबल को हाइलाइट करें जहां आपका डेटा स्थित है। इस चरण में, आपका स्ट्रिंग इसके समान दिखना चाहिए: =VLOOKUP(e12,A1:C5,

6. आपके स्ट्रिंग में तीसरा मान "col_index_number" या कॉलम इंडेक्स नंबर है। यह उस कॉलम की संख्या है जिससे आप जानकारी निकालने की कोशिश कर रहे हैं। हमारे द्वारा चुनी गई टेबल सरणी में, "नाम" 1 माना जाएगा, "पता" 2 है, और "फोन नंबर" 3 है। चूंकि हम एक फोन नंबर VLOOKUP करने की कोशिश कर रहे हैं, हमारा स्ट्रिंग इस तरह दिखता है: =VLOOKUP(e12,A1:C5,3,

7. अंत में, आपके स्ट्रिंग का अंतिम भाग यह है कि आप जिस मान के साथ खोज रहे हैं उसके लिए सटीक मिलान चाहते हैं या अनुमानित मिलान। आपको सटीक के लिए FALSE और अनुमानित के लिए TRUE टाइप करना होगा। हमारे मामले में, हम बाद वाले के साथ गए। हमारा अंतिम स्ट्रिंग है =VLOOKUP(E12,A1:C5,3,FALSE)

8. जैसा कि आप देख सकते हैं, हमारा VLOOKUP "आइरिस वॉटसन" के लिए संबंधित फोन नंबर को सफलतापूर्वक खींचने में सक्षम था।

नोट: यह गाइड VLOOKUP करने के लिए माइक्रोसॉफ्ट एक्सेल का उपयोग करता है, लेकिन आप गूगल शीट्स के साथ उसी विधि का उपयोग कर सकते हैं।

एकाधिक मानदंडों का उपयोग करके डेटा कैसे फ़िल्टर करें

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

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

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

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

1. पहली चीज जो हमने की वह एक हेल्पर कॉलम बनाना था जो एक अद्वितीय पहचानकर्ता बनाने के लिए "नाम" और "फोन नंबर" सेल्स को जोड़ता है। हमने CONCATENATE फ़ंक्शन का उपयोग किया, जो केवल अलग-अलग सेल्स से स्ट्रिंग्स को जोड़ता है। आपका स्ट्रिंग कुछ इस तरह दिखना चाहिए: =CONCATENATE(B2," | ",D2) हमने नाम और फोन नंबर के बीच समझने में आसान बनाने के लिए पाइप "|" प्रतीक और कुछ स्पेस जोड़े। यदि आप इस फ़ंक्शन के बारे में अधिक जानना चाहते हैं, तो CONCATENATE का उपयोग कैसे करें पर हमारे लेख को देखें।

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

3. आपको VLOOKUP के लिए दो फ़ील्ड की आवश्यकता होगी। हमने "नाम" और "नंबर" लुकअप फ़ील्ड के साथ-साथ एक "पता" फ़ील्ड भी जोड़ा जो VLOOKUP परिणाम प्रदर्शित करेगा।

4. हम जो करना चाहते हैं वह है VLOOKUP को "नाम" और "नंबर" खोज फ़ील्ड में प्रदान की गई जानकारी को इस तरह से संयोजित करने के लिए कि वे उसी प्रारूप में फिट हों जो हम अपने हेल्पर कॉलम में उपयोग कर रहे हैं। इस तरह, VLOOKUP हेल्पर कॉलम में अद्वितीय पहचानकर्ता को पहचानने और संबंधित पता खींचने में सक्षम होगा।

यह वही है जो हमारा स्ट्रिंग शुरू में दिखता था: =VLOOKUP(F9&" | "&F10,

F9 और F10 हमारे "नाम" और "पता" खोज फ़ील्ड हैं, और & प्रतीक कनकैटनेट की तरह काम करता है जिसमें यह दोनों फ़ील्ड को एक साथ जोड़ता है। स्ट्रिंग का " | " भाग वही डिवाइडर है जिसका उपयोग हमने अपने हेल्पर कॉलम में कनकैटनेशन में किया था।

5. अब आपको केवल VLOOKUP सूत्र के बाकी हिस्से का पालन करने की आवश्यकता है। आपको टेबल सरणी का चयन करना होगा, कॉलम इंडेक्स नंबर इनपुट करना होगा, और इसे सटीक मिलान पर सेट करना होगा। यह हमारा अंतिम स्ट्रिंग था: =VLOOKUP(F9&" | "&F10,A1:D6,3,FALSE)

6. आप देखेंगे कि खोज फ़ील्ड में केवल एक नाम या पता दर्ज करना पर्याप्त नहीं है। सफल लुकअप होने के लिए दोनों को भरा जाना चाहिए। नीचे आप दो अलग-अलग VLOOKUP परिणाम देख सकते हैं क्योंकि हमारे पास दो अलग-अलग "आइरिस वॉटसन" हैं। आपको एक अलग परिणाम मिलेगा यह इस बात पर निर्भर करता है कि आप दूसरे खोज फ़ील्ड में कौन सा फोन नंबर दर्ज करते हैं।

क्या VLOOKUP INDEX-MATCH से बेहतर है?

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

MATCH फ़ंक्शन का उपयोग तब किया जाता है जब आप सेल्स की एक श्रृंखला के भीतर एक निश्चित मान की स्थिति संख्या ढूंढना चाहते हैं। दूसरी ओर, INDEX टेबल सरणी या सेल्स की एक श्रृंखला से एक मान प्रदर्शित करने के लिए दो संभावित प्रारूपों का उपयोग करता है। दोनों को संयोजित करके, कोई MATCH फ़ंक्शन का उपयोग प्रदान की गई जानकारी की स्थिति संख्या निर्धारित करने के लिए कर सकता है, फिर उन स्थिति संख्याओं का उपयोग करके मान लौटाने के लिए INDEX का उपयोग कर सकता है।

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

दिन के अंत में, VLOOKUP का उपयोग करना जानने वाले कई अधिक लोग INDEX-MATCH की तुलना में हैं, इसलिए पूर्व शायद बेहतर विकल्प है यदि शीट कई गैर-उन्नत उपयोगकर्ताओं द्वारा एक्सेस की जाएगी। लेकिन यदि शीट विशेषज्ञों के लिए है, तो INDEX-MATCH शायद जाने का रास्ता है।

VLOOKUP का उपयोग करते समय सामान्य क्या करें और क्या न करें

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

1. करें सुनिश्चित करें कि Lookup_Value आपकी टेबल में पहला कॉलम है।

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

2. न करें सटीक मिलान के लिए FALSE का उपयोग करना भूल जाएं।

आपके VLOOKUP स्ट्रिंग का अंतिम भाग आपको यह निर्धारित करने देता है कि मान सटीक मिलान के लिए FALSE है या आंशिक मिलान के लिए TRUE। कई उपयोगकर्ता गलती से TRUE का उपयोग करते हैं, जिससे गलत परिणाम हो सकते हैं, या वे बिल्कुल भी मान सेट करना भूल जाते हैं।

3. करें कॉलम इंडेक्स नंबर को दोबारा जांचें।

VLOOKUP क्या प्रदर्शित करता है यह इस बात पर बहुत निर्भर करता है कि आप स्ट्रिंग टाइप करते समय सही "col_index_num" सेट करते हैं। "col_index_num" या कॉलम इंडेक्स नंबर आपकी टेबल सरणी में प्रत्येक कॉलम के लिए सेट संख्यात्मक मान है। पहले कॉलम का मान 1 माना जाता है, दूसरा 2, और इसी तरह। अपने स्ट्रिंग में गलत "col_index_num" मान इनपुट करें, और फ़ंक्शन पूरी तरह से अलग परिणाम दिखाएगा।

4. करें फॉर्मूला को अन्य सेल्स में कॉपी करते समय F4 का उपयोग करें।

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

सामान्य VLOOKUP त्रुटियां और उन्हें कैसे ठीक करें

VLOOKUP का उपयोग करते समय आपके सामने आने वाली सबसे आम त्रुटि "#NA" त्रुटि है, हालांकि यह ध्यान देने योग्य है कि इस त्रुटि के प्रकट होने के कई कारण हैं।

1. आपका Lookup_value आपकी टेबल सरणी के पहले कॉलम में नहीं है।

VLOOKUP की सबसे बड़ी सीमाओं में से एक यह है कि यह केवल आपकी टेबल सरणी के पहले कॉलम में मानों को खोज सकता है। यदि आपका lookup_value वहां नहीं है, तो इसका परिणाम #NA त्रुटि होगी। इसे ठीक करने के लिए, आप या तो अपने फॉर्मूला को एक अलग कॉलम को संदर्भित करने के लिए 조정 सकते हैं या अपने कॉलम को स्थानांतरित कर सकते हैं ताकि lookup_value सही जगह पर हो।

2. VLOOKUP सटीक मिलान नहीं ढूंढ रहा है।

आपके VLOOKUP सूत्र में अंतिम मान आपका range_lookup तर्क है जिसे आप सटीक के लिए FALSE या अनुमानित के लिए TRUE पर सेट करते हैं। यदि आपके पास यह तर्क FALSE पर सेट है, और VLOOKUP सटीक मिलान ढूंढने में असमर्थ है, तो आप #NA त्रुटि के साथ समाप्त होंगे।

यदि आप पूरी तरह से निश्चित हैं कि आपके lookup_value में एक मिलान होना चाहिए, तो आप अपनी टेबल सरणी में जानकारी को दोबारा जांचना चाहेंगे ताकि यह सुनिश्चित हो सके कि सब कुछ सही ढंग से स्वरूपित है और इसमें अवांछित स्पेस नहीं हैं। नॉन-प्रिंटिंग वर्ण भी VLOOKUP को आइटम्स को ठीक से ढूंढने से रोक सकते हैं।

3. अत्यधिक बड़े फ्लोटिंग पॉइंट नंबर

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

अक्सर पूछे जाने वाले प्रश्न

1. जब आप range_lookup तर्क को खाली छोड़ देते हैं तो क्या होता है?

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

2. VLOOKUP का उपयोग करने के विकल्प क्या हैं?

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

3. क्या आप कॉलम के बजाय पंक्तियों द्वारा मान खोज सकते हैं?

हां। आप एक्सेल और गूगल शीट्स में HLOOKUP या "हॉरिजॉन्टल लुकअप" के साथ ऐसा कर सकते हैं। यह फ़ंक्शन आपको एक विशिष्ट पंक्ति से एक मान देखने देता है, फिर उसी कॉलम पर लेकिन एक अलग पंक्ति से एक मान प्रदर्शित करता है।

यदि आप अपनी खोजों को एकल पंक्ति या कॉलम तक सीमित करना चाहते हैं, तो आप LOOKUP फ़ंक्शन का उपयोग कर सकते हैं।