Excel VLOOKUP शक्तिशाली है, लेकिन यह तब काम नहीं करेगा जब आपके पास विशिष्ट परिस्थितियाँ हों। आज, VLOOKUP का निवारण कैसे करें, इस पर एक नज़र।
VLOOKUP Excel में मेरा पसंदीदा कार्य है। यदि आप VLOOKUP कर सकते हैं, तो आप एक्सेल में कई समस्याओं को हल करने में सक्षम हैं। लेकिन ऐसी चीजें हैं जो एक VLOOKUP यात्रा कर सकती हैं। यह विषय उनमें से कुछ के बारे में बात करता है।
लेकिन पहले, सादे अंग्रेजी में VLOOKUP की मूल बातें।
ए: सी में डेटा आईटी विभाग से आया था। आपने आइटम और दिनांक द्वारा बिक्री के लिए कहा। उन्होंने आपको आइटम नंबर दिया। आपको आइटम का विवरण चाहिए। डेटा को फिर से चलाने के लिए आईटी विभाग की प्रतीक्षा करने के बजाय, आपको स्तंभ F: G में दिखाई गई तालिका मिल जाती है।
आप चाहते हैं कि VLOOKUP A2 में आइटम ढूंढे, जबकि यह $ F $ 3: $ G $ 30 में तालिका के पहले कॉलम के माध्यम से खोज करता है। जब VLOOKUP F7 में मैच पाता है, तो आप चाहते हैं कि VLOOKUP तालिका के दूसरे कॉलम में मिले विवरण को वापस लौटाए। हर VLOOKUP जो एक सटीक मैच की तलाश कर रहा है उसे False (या शून्य, जो False के बराबर है) में समाप्त होना है। नीचे सूत्र ठीक से सेट किया गया है।
ध्यान दें कि आप लुकअप टेबल के पते पर चार डॉलर के संकेत जोड़ने के लिए F4 का उपयोग करते हैं। जैसे ही आप फॉर्मूला को कॉलम डी कॉपी करते हैं, आपको निरंतर बने रहने के लिए लुकअप टेबल के पते की आवश्यकता होती है। दो सामान्य विकल्प हैं: आप संपूर्ण स्तंभ F: G को लुकअप टेबल के रूप में निर्दिष्ट कर सकते हैं। या, आप F3: G30 को एक नाम के साथ नाम दे सकते हैं जैसे कि आइटमटेबल। यदि आप उपयोग करते हैं =VLOOKUP(A2,ItemTable,2,False), तो नामित रेंज एक पूर्ण संदर्भ की तरह काम करती है।
जब भी आप VLOOKUPs का एक समूह बनाते हैं, तो आपको VLOOKUPs के कॉलम को क्रमबद्ध करना होगा। ZA को क्रमबद्ध करें, और कोई # N / A त्रुटियाँ शीर्ष पर आएंगी। इस मामले में, एक है। आइटम BG33-9 लुकअप टेबल से गायब है। शायद यह एक टाइपो है। शायद यह एक नया आइटम है। यदि यह नया है, तो अपने लुकअप टेबल के बीच में कहीं भी एक नई पंक्ति डालें और नई आइटम जोड़ें।
कुछ # N / A त्रुटियां होना काफी सामान्य है। लेकिन नीचे दिए गए आंकड़े में, ठीक यही फॉर्मूला # एन / ए के अलावा कुछ नहीं दे रहा है। जब ऐसा होता है, तो मैं देखता हूं कि क्या मैं पहले VLOOKUP को हल कर सकता हूं। आप A2 में पाए जाने वाले BG33-8 को देख रहे हैं। लुकअप तालिका के पहले कॉलम के माध्यम से नीचे मंडराना शुरू करें। जैसा कि आप देख सकते हैं, मिलान मूल्य स्पष्ट रूप से F10 में है। आप इसे क्यों देख सकते हैं, लेकिन एक्सेल इसे नहीं देख सकता है?
प्रत्येक सेल पर जाएं और F2 कुंजी दबाएं। यहाँ F10 है। ध्यान दें कि प्रविष्टि कर्सर 8 के ठीक बाद दिखाई देता है।
यहाँ सेल A2 एडिट मोड में है। सम्मिलन कर्सर 8. से दूर रिक्त स्थान का एक जोड़ा है। यह एक संकेत है कि किसी बिंदु पर, यह डेटा एक पुराने COBOL डेटा सेट में संग्रहीत किया गया था। वापस COBOL में, यदि आइटम फ़ील्ड को 10 वर्णों के रूप में परिभाषित किया गया था और आपने केवल 6 वर्णों को टाइप किया, तो COBOL इसे अतिरिक्त रिक्त स्थान के साथ पैड करेगा।
समाधान? A2 देखने के बजाय, ऊपर देखें TRIM(A2)।
TRIM () फ़ंक्शन अग्रणी और अनुगामी रिक्त स्थान को निकालता है। यदि आपके पास शब्दों के बीच कई स्थान हैं, तो TRIM उन्हें एकल स्थान में बदल देगा। नीचे दिए गए आंकड़े में A1 में दोनों नामों के पहले और बाद के रिक्त स्थान हैं। =TRIM(A1)A3 में सभी लेकिन एक स्थान को हटा देता है।
वैसे, क्या होगा अगर समस्या कॉलम ए के बजाय कॉलम एफ में रिक्त स्थान में आ गई थी? TRIM () फ़ंक्शंस का एक कॉलम E में जोड़ें, जो स्तंभ F की ओर इशारा करता है। उन लोगों को कॉपी करें और लुकअप फिर से काम करना शुरू करने के लिए F के मान के रूप में पेस्ट करें।
VLOOKUP काम नहीं करेगा कि अन्य बहुत ही सामान्य कारण यहाँ दिखाया गया है। कॉलम एफ में वास्तविक संख्याएं हैं। कॉलम ए में टेक्स्ट है जो संख्याओं की तरह दिखता है।
सभी कॉलम A. प्रेस alt = "+ D, E, F का चयन करें। यह कॉलम को डिफ़ॉल्ट टेक्स्ट करता है और सभी टेक्स्ट नंबर को वास्तविक संख्या में बदल देगा। लुकअप फिर से काम करना शुरू कर देता है।
वीडियो देखेंा
- VLOOKUP कई समस्याओं को हल करता है
- सामान्य VLOOKUP समस्याएं:
- यदि VLOOKUP काम करना शुरू कर देता है, लेकिन # N / A अधिक प्रमुख हो जाता है: लुकअप टेबल में $ भूल गया
- कुछ # एन / ए: टेबल से गायब आइटम
- VLOOKUP कार्य में से कोई भी: रिक्त स्थान के लिए जाँच करें
- TRIM के साथ अनुगामी रिक्त स्थान निकालें
- संख्या और संख्या पाठ के रूप में संग्रहीत
- दोनों स्तंभों का चयन करें और alt = "" + DEF का उपयोग करें
- एपिसोड में एक चुटकुला शामिल है जो कि एकाउंटेंट और आईटी दोनों लोग मजाकिया हैं, लेकिन विभिन्न कारणों से
वीडियो ट्रांसक्रिप्ट
पॉडकास्ट से एक्सेल सीखें, एपिसोड 2027 - समस्या निवारण VLOOKUP!
ठीक है, इस पूरी पुस्तक को पॉडकास्टिंग करते हुए, प्लेलिस्ट में लाने के लिए शीर्ष-दाएं हाथ के कोने पर "i" पर क्लिक करें!
VLOOKUP एक्सेल के सभी में मेरा पसंदीदा कार्य है, और मैं हमेशा अपने एक सेमिनार में इस मजाक को बताता हूं, और यह हंसी आती है कि क्या आप आईटी व्यक्ति हैं या आईटी व्यक्ति नहीं हैं। मैं हमेशा कहता हूं "ठीक है, हमारे पास यह डेटा यहां बाईं ओर है, और मैं उस डेटा को देख सकता हूं और यह बता सकता हूं कि डेटा आईटी विभाग से आया था क्योंकि यह वास्तव में मैंने क्या मांगा था, लेकिन वास्तव में मुझे इसकी आवश्यकता नहीं थी। मैंने आइटम की तारीख और मात्रा के लिए पूछा, और उन्होंने मुझे आइटम नंबर की तारीख और मात्रा दी, लेकिन उन्होंने मुझे विवरण देने के लिए परेशान नहीं किया, है ना? " और लेखाकार हमेशा इस पर हंसते हैं, क्योंकि यह हर समय उनके साथ होता है, और आईटी लोग "जैसे हैं, वैसे मैंने आपको जो मांगा है, वह आपको दिया है, यह मेरी गलती नहीं है!" इसलिए वे दोनों सोचते हैं कि यह अलग-अलग कारणों से मज़ेदार है, आप जानते हैं, और आईटी आदमी व्यस्त है, वह क्वेरी को फिर से लिखने के लिए वापस नहीं मिल सकता है:इसलिए हमें खुद को बचाने के लिए कुछ करना होगा।
और इसलिए इस छोटी सी तालिका को मैंने अपने कंप्यूटर में कहीं और से सादे अंग्रेजी में कॉपी किया, जो VLOOKUP करती है, वह कहती है "अरे, हमारे पास एक आइटम नंबर W25-6 है।" हमारे पास यहां एक तालिका है, मैं तालिका के पहले कॉलम के माध्यम से नीचे तक क्रूज करना चाहता हूं जब तक कि मुझे वह आइटम नंबर नहीं मिल जाता है, और फिर उस पंक्ति से कुछ वापस करें। ठीक है, इस मामले में, मुझे जो चाहिए वह उस पंक्ति से दूसरा स्तंभ है। और फिर हर VLOOKUP के अंत में हमें या तो FALSE लगाना होगा या 0. अब यहीं, रेंज चुनने के बाद, मैं F4 कुंजी को प्रेस करने जा रहा हूं, और फिर मैं दूसरा कॉलम चाहता हूं और फिर एक सटीक के लिए FALSE मैच। कभी भी अनुमानित मैच न चुनें, कभी नहीं, कभी नहीं! यह एक अनुमानित मैच नहीं है, यह एक बहुत ही खास बात है, यह हर समय काम नहीं करता है। यदि आप प्रतिभूति और विनिमय आयोग को अपनी संख्याएँ देना पसंद करते हैं, तो हर तरह से बेझिझक,सही या बस छोड़ दें, FALSE बंद। लेकिन आपके द्वारा बनाया गया हर VLOOKUP FALSE से कम होना चाहिए, FALSE या, 0, FALSE से छोटा है, आपको वहां FALSE लगाना चाहिए, लेकिन 0 FALSE की तरह ही है।
अब ठीक है, समस्या निवारण, पहली बात, जब आप VLOOKUPs का एक पूरा गुच्छा बनाते हैं, तो # N / As, का एक जोड़ा होना बहुत सामान्य है, ठीक है? और मैं हमेशा डेटा, ZA, जो # N / As को शीर्ष पर लाता है, के साथ # N / A ढूंढता है, वहीं, BG33-9, या तो यह एक टाइपो है या यह बिल्कुल नया आइटम है, ठीक है, और हमें मिल गया पता लगाने के लिए। इसलिए यहां दाईं ओर मेरे पास नया डेटा है, मैं उसे काट दूंगा, और फिर Alt + IED, उन कोशिकाओं को बीच में डालें, इसमें अल्फाबेटिक नहीं होना चाहिए, इस तालिका को सॉर्ट करने की आवश्यकता नहीं है। जब आप FALSE संस्करण का उपयोग कर रहे होते हैं, तो तालिका नहीं होती है - आप इसे केवल कहीं भी रख सकते हैं, आप अंत में नहीं डाल सकते हैं, क्योंकि मुझे सूत्र को फिर से लिखना नहीं है, मैं सिर्फ सूत्र चाहता हूं काम क। ठीक है, इसलिए # N / A का एक जोड़ा, बेहद, बेहद सामान्य, लेकिन इसे देखें।
जब आप उस काम के उत्तरों के साथ शुरू करते हैं, लेकिन तब # N / A की शुरुआत थोड़ी बार दिखाई देती है, और फिर अंततः वे सभी तरह से नीचे दिखाई देते हैं, यह एक निश्चित संकेत है कि आपने तालिका संदर्भ को लॉक नहीं किया है। ठीक है, देखें, तो यहां तालिका चल रही है क्योंकि मैं सूत्र को नीचे की ओर कॉपी करता हूं, ठीक है, और इसलिए मैं कुछ हिट करने में भाग्यशाली रहा हूं जो सूची के अंत में थे। लेकिन अंततः मैं उस बिंदु पर उतर जाता हूं, जहां यह पूरी तरह से खाली कोशिकाओं में यहां दिख रहा है, और निश्चित रूप से कुछ भी नहीं मिल रहा है। ठीक है, इसलिए यह पहली बात है, आपको एक जोड़ी मिलती है जो काम करती है, कुछ # N / A की, कुछ और जो कि काम करती है, और फिर सभी # N / A बाकी की तरह - निश्चित संकेत है कि आपने नहीं डाला है $ में।
बस वापस जाएं, संपादन मोड के लिए F2, कोलन का चयन करें, F4 दबाएं, जो कि सभी $ में डालता है, उस नीचे शूट करने के लिए डबल-क्लिक करें, और चीजें फिर से काम करना शुरू कर देती हैं, ठीक है। अगला एक, सटीक एक ही सूत्र, सूत्र एकदम सही है, BG33-8 देखें, हमें उस अधिकार में से कोई भी नहीं मिल रहा है। ठीक है, इसलिए मैं देखता हूं, BG33-8, अरे, यह वहां है, यह वहीं है, यह लाल रंग में है, मुझे इसे देखना चाहिए! इसलिए मैं दाईं ओर एक पर आता हूं, मैं F2 दबाता हूं, और मैं चमकता हुआ सम्मिलन बिंदु देखता हूं, और देखता हूं, यह 8 के बाद सही है, जैसे कि, और फिर मैं यहां आ गया और मैं F2 दबाता हूं, वहां हैं कुछ अनुगामी रिक्त स्थान। COBOL के दिनों में यह बहुत आम बात है, वे कहेंगे “आप जानते हैं, देखो, हम आपको आइटम नंबर के लिए 10 स्थान देने जा रहे हैं, और यदि आप सभी 10 स्थानों को टाइप नहीं करते हैं तो वे रिक्त स्थान को भर देंगे। । ” और इसलिए यह बहुत आम है,और यहाँ महान समाधान TRIM फ़ंक्शन के साथ उन अग्रणी और अनुगामी स्थानों से छुटकारा पाने के लिए है। A2 के बजाय TRIM (A2) का उपयोग करें, इसे नीचे शूट करने के लिए डबल-क्लिक करें, ठीक है, फिर भी BG33-9 दूसरी तालिका में वापस है।
ठीक है, तो बस आप समझते हैं कि टीआरआईएम कैसे काम करता है, इसलिए मैंने रिक्त स्थान, जॉन, रिक्त स्थान का एक गुच्छा, दुर्रान और रिक्त स्थान का एक गुच्छा टाइप किया, और फिर मैंने एक * पहले और बाद में आप देख सकते हैं कि यह कैसा दिखता है । जब मैं टीआरआईएम (पी 4) के लिए कहता हूं, तो हम सभी प्रमुख स्थानों, सभी अनुगामी स्थानों से छुटकारा पा लेते हैं, और फिर कई आंतरिक रिक्त स्थान एक स्थान पर नीचे हो जाते हैं। ठीक है, इसलिए आप देख सकते हैं कि वहां किस तरह का दृश्य है, जिससे वे अग्रणी और पीछे चलने वाले स्थानों से छुटकारा पा रहे हैं, बीच का कोई भी डबल-अप स्थान एक एकल स्थान बन जाता है। तो TRIM, महान, अपने शस्त्रागार में महान उपकरण, ठीक है, यहाँ एक और वास्तव में आम है।
यदि यह अनुगामी स्थान नहीं है, तो मैंने जो सबसे आम चीज़ देखी है, वह यहाँ है जहाँ हमारे पास सही संख्याएँ हैं, और यहाँ हमारे पास संख्याएँ पाठ के रूप में संग्रहीत हैं। और VLOOKUP एक मैच के रूप में नहीं देखेंगे, भले ही 4399, यह एक संख्या है, यह पाठ है, काम नहीं करता है। टेक्स्ट के एक कॉलम को संख्याओं में बदलने का सबसे तेज़ तरीका, कॉलम का चयन करें, अनुक्रम में 3 अक्षर, alt = "" DEF, और अचानक, हमारे VLOOKUPs फिर से काम करना शुरू करते हैं, लगभग 1500 पॉडकास्ट से महान, महान टिप। ठीक है, इसलिए वे सबसे आम VLOOKUP समस्याएं हैं, या तो आप $ भूल गए, या आपके पास अनुगामी स्थान हैं, या आपके पास संख्याएं और संख्याएं पाठ के रूप में संग्रहीत हैं। ये सभी युक्तियां इस पुस्तक "MrExcel XL" में हैं, शीर्ष-दाएँ हाथ के कोने पर ihe "i" पर क्लिक करें, आप पुस्तक खरीद सकते हैं।
ठीक है, त्वरित पुनर्कथन: VLOOKUP कई समस्याओं को हल करता है, अगर एक VLOOKUP काम करना शुरू कर देता है लेकिन # N / A! अधिक प्रमुख हो जाता है, आप $ को लुकअप टेबल में रखना भूल गए। यदि कुछ # N / A है, तो यह तालिका से केवल आइटम गायब है। यदि VLOOKUP में से कोई भी काम नहीं करता है और यह पाठ है, तो रिक्त स्थान की जाँच करें, आप TRIM फ़ंक्शन का उपयोग कर सकते हैं। यदि आपके पास संख्याएँ और संख्याएँ पाठ के रूप में संग्रहीत हैं, तो या तो कॉलम चुनें, जिसके पास पाठ है, तो alt = "" DEF को उन सभी संख्याओं पर वापस जाना है।
ठीक है अरे, मैं आपको रोकने के लिए धन्यवाद देना चाहता हूं, हम अगली बार आपको एक और नेटकास्ट से देखेंगे!
फ़ाइल डाउनलोड करें
यहाँ नमूना फ़ाइल डाउनलोड करें: Podcast2027.xlsx
