VLOOKUP एक शक्तिशाली कार्य है। लेकिन मुझे अक्सर अपने पावर एक्सेल सेमिनारों में से किसी एक में एक प्रश्न मिलता है जो यह जानना चाहता है कि क्या VLOOKUP सभी मिलान मूल्यों को वापस कर सकता है। जैसा कि आप जानते हैं, चौथे तर्क के रूप में VLOOKUP फाल्स के साथ हमेशा पहला मैच जो वह पाता है, वापस करेगा। निम्नलिखित स्क्रीनशॉट में, सेल F2 3623 देता है क्योंकि यह नौकरी J1199 के लिए मिला पहला मैच है।
फिर, क्या VLOOKUP सभी मैचों को वापस कर सकता है?
VLOOKUP नहीं करेगा। लेकिन अन्य कार्य कर सकते हैं।
यदि आप नौकरी J1199 से सभी लागतों को पूरा करना चाहते हैं, तो आप उपयोग करेंगे =SUMIFS($B$2:$B$53,$A$2:$A$53,G2),
यदि आपके पास पाठ मूल्य हैं और सभी परिणामों को एक ही मूल्य में शामिल करना चाहते हैं, तो आप उपयोग कर सकते हैं =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,""))। यह सूत्र केवल Office 365 और Excel 2019 में काम करता है।
या, आपको किसी एकल कार्य के लिए कार्यपत्रक की नई श्रेणी में सभी परिणाम वापस करने की आवश्यकता हो सकती है। =FILTER(B2:C53,A2:A53=K1,"None Found")2019 में ऑफिस 365 में आने वाला एक नया समारोह समस्या का समाधान करेगा:
कभी-कभी, लोग सभी VLOOKUP का प्रदर्शन करना चाहते हैं और उन्हें योग करते हैं। यदि आपका लुकअप टेबल सॉर्ट है, तो आप उपयोग कर सकते हैं =SUM(LOOKUP(B2:B53,M3:N5))।
यदि आपको VLOOKUP के सटीक मिलान संस्करण के साथ सभी VLOOKUP को योग करने की आवश्यकता है, तो उपयोग करने के लिए आपको गतिशील Arrays तक पहुंच की आवश्यकता होगी =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE))।
डायनेमिक एरे के बारे में अधिक जानने के लिए, एक्सेल डायनामिक एरे को सीधे प्वाइंट पर देखें।
वीडियो देखेंा
वीडियो ट्रांसक्रिप्ट
एक्सेल से जानें, पॉडकास्ट एपिसोड 2247: क्या आप सभी वीएलक्यूयूपी वैल्यू वापस कर सकते हैं?
अरे। नेटकास्ट में आपका स्वागत है। मैं बिल जेलन हूं। पिछले हफ्ते विस्कॉन्सिन के एपलटन में मेरे सेमिनार में दो सवाल सामने आए - दोनों संबंधित। उन्होंने कहा, हे, हम सभी VLOOKUPs कैसे लौटाते हैं, ठीक है? इस मामले में, जैसे J1199 में मैचों का एक समूह है और वे, आप जानते हैं, उन सभी को वापस करना चाहते हैं, और मेरा पहला सवाल जब भी कोई मुझसे यह पूछता है, ठीक है, तो आप मैचों के साथ क्या करना चाहते हैं? क्या वे संख्याएँ हैं जिन्हें आप जोड़ना चाहते हैं या यह पाठ है जिसे आप संक्षिप्त करना चाहते हैं? और यह हास्यास्पद है। एक ही संगोष्ठी में दो प्रश्न, एक व्यक्ति उन्हें जोड़ना चाहता था और दूसरा व्यक्ति परिणामों को संक्षिप्त करना चाहता था।
तो आइए एक नजर डालते हैं इन दोनों पर। सामग्री की तालिका के लिए YouTube विवरण में देखें जहां आप पाठ के परिणाम को देखना चाहते हैं, जहां आप दूसरे पर जा सकते हैं।
ठीक है, इसलिए, पहली बात, अगर हम उन सभी को जोड़ना चाहते हैं, तो हम VLOOKUP का उपयोग बिल्कुल नहीं करेंगे। हम SUMIF या SUMIFS नामक एक फ़ंक्शन का उपयोग करने जा रहे हैं जो इस आइटम से मेल खाने वाली सभी चीज़ों का योग करने वाला है। तो, SUMIFS। यहां संख्यात्मक मान दिए गए हैं, जिन्हें हम योग करना चाहते हैं और मैं F4 दबाकर उसे बंद कर दूंगा। इस तरह, जैसा कि मैंने इसे नीचे कॉपी किया है, यह उसी सीमा की ओर इशारा करते रहने वाला है, और फिर हम जांच करके यह देखना चाहते हैं कि कॉलम A में JOB नंबर, फिर से F4 है, या = हमारे बाईं ओर के मान पर = - इस मामले में E2 - और जैसा कि हम नीचे कॉपी करते हैं, हम प्रत्येक आइटम के लिए कुल देखेंगे। (SUMIFS ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2)
चलिए, यहाँ थोड़ा जाँच करते हैं। J1199। कुल 25365 है। ठीक है। तो, यह काम कर रहा है। यदि यह संख्या है और आप सभी संख्याएँ प्राप्त करना चाहते हैं और उन्हें जोड़ना चाहते हैं, तो SUMIF या SUMIFS पर स्विच करें, लेकिन यदि यह पाठ, ठीक है, तो, यह फ़ंक्शन फरवरी 2017 में Office 365 में नया है। इसलिए, यदि आपके पास Excel 2016 है या एक्सेल 2013 या एक्सेल 2010 या उन पुराने किसी भी, आप इस समारोह के लिए नहीं जा रहे हैं। यह एक फ़ंक्शन है जिसका नाम TEXTJOIN है। TEXTJOIN। यह (जो मैकडैड - 01:50) का एक अन्य कार्य है, जिसने 2018 में इग्नाइट में उन सभी महान गतिशील सरणी फॉर्मूलों को लाया, और जो ने यह सुनिश्चित किया कि TEXTJOIN सरणियों के साथ काम करेगा, जो वास्तव में बहुत अच्छा है।
तो, यहाँ परिसीमन होने जा रहा है, स्थान, निश्चित रूप से EMPTY को अनदेखा करें। हम यहाँ EMPTY को अनदेखा करना चाहते हैं क्योंकि हम इस अगले भाग में बहुत सारे खालीपन उत्पन्न करने वाले हैं, IF स्टेटमेंट। यदि वह आइटम A2, F4 से अधिक है, तो = इस JOB नंबर पर यहां है, तो मैं कॉलम C, F4 से संबंधित आइटम चाहता हूं, अन्यथा, मुझे वह पसंद है। उस IF स्टेटमेंट को बंद करें। TEXTJOIN को बंद करें। क्या मुझे नियंत्रण + SHIFT + ENTER दबाना होगा? नहीं, मैं नहीं। यह मुझे उस तरह से मेल खाने वाले सभी उत्पादों को लाता है, ठीक है? इसलिए, यदि हम उन्हें समेटना चाहते हैं, तो सभी VLOOKUPs लौटाते हुए, हाँ, यदि हम उन्हें समेटना चाहते हैं, तो। (= TEXTJOIN (",", सच, IF ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, "")))
ठीक है, अब, यहां एक और संभावना है जब लोग मुझसे पूछते हैं कि क्या वे सभी वीएलबुक खरीद सकते हैं। यह एक ऐसा मुद्दा हो सकता है जहां हम इनमें से प्रत्येक लागत को देखना चाहते हैं और HANDLING COST का पता लगा सकते हैं और फिर उन्हें पूरा कर सकते हैं। जैसे, मैं यहाँ एक VLOOKUP और यहाँ एक VLOOKUP और यहाँ एक VLOOKUP और यहाँ एक VLOOKUP नहीं रखना चाहता। मैं उन्हें पूरी तरह से करना चाहता हूं और उस स्थिति में, हम SUM फ़ंक्शन और फिर पुराने, पुराने LOOKUP फ़ंक्शन का उपयोग करने जा रहे हैं। LOOKUP कहता है कि हम कॉलम बी में इन सभी मूल्यों को देखने जा रहे हैं। मुझे यहां F4 की आवश्यकता नहीं है क्योंकि मैं इसे कहीं भी कॉपी नहीं कर रहा हूं। , यहां हमारी लुकिंग टेबल है। ), एसयूएम को बंद करें, और यह बाहर चला जाता है और प्रत्येक व्यक्ति को वीएलबुकअप करता है और फिर उन सभी को इस तरह से रकम देता है। (= SUM (LOOKUP (B2: B53, K3: L5)))
अच्छा हे। ये सभी विषय मेरी पुस्तक LIV: द 54 ग्रेटेस्ट टिप्स ऑफ ऑल टाइम हैं। अधिक जानने के लिए ऊपरी दाएँ हाथ के कोने में i क्लिक करें।
तो, सवाल यह है कि क्या आप सभी VLOOKUPs वापस कर सकते हैं? ठीक है, की तरह, लेकिन वास्तव में VLOOKUP का उपयोग नहीं। हम या तो इसे हल करने के लिए SUMIF, TEXTJOIN, या SUM या LOOKUP का उपयोग करने जा रहे हैं।
अच्छा हे। मैं आपको रोकने के लिए धन्यवाद देना चाहता हूं। हम आपको अगली बार एक और नेटकास्ट से देखेंगे।
तुम्हें पता है, ठीक है, मैं एक सप्ताह के लिए इन गतिशील सरणियों के बारे में बात कर रहा हूं। मैं एक वीडियो करना चाहता था, जहां मैं डायनेमिक सरणियों पर नहीं छूता था क्योंकि मुझे पता है कि बहुत सारे लोग अभी तक उनके पास नहीं हैं, लेकिन यहां हम हैं। यह बाहर है। तुम्हें पता है, ये वर्णमाला नहीं हैं। यह बहुत बेहतर होगा यदि हम उन्हें सॉर्ट कर सकते हैं, और यदि आपके पास नए डायनेमिक सरणियाँ हैं, तो आप इसे SORT फ़ंक्शन में भेज सकते हैं, इस तरह टाइप करें, और ENTER दबाएँ, और अब परिणाम उसी तरह सॉर्ट किए जाएंगे।
तुम्हें पता है, यहां तक कि यह सूत्र गतिशील सरणियों के साथ बेहतर हो सकता है। देखने के लिए आपको TRUE का उपयोग करना होगा। यदि आप एक, FALSE का उपयोग करना चाहते हैं तो क्या होगा? हम इसे एक VLOOKUP में बदल सकते हैं, इस पाठ के सभी को उस तालिका में देख सकते हैं, 2,। इस मामले में, मैं TRUE का उपयोग करने जा रहा हूँ, लेकिन एक अन्य मामले में, आप FALSE का उपयोग कर सकते हैं। नियंत्रण + SHIFT + ENTER। नहीं, यह सिर्फ काम करने जा रहा है, ठीक है? (= SUM (VLOOKUP (B2: B53, K3: L5,2, True)))
2019 की शुरुआत में आने वाले डायनेमिक एरे, कई समस्याओं का समाधान करेंगे।
यहां से आगे निकलने के लिए बाहर घूमने के लिए धन्यवाद। हम आपको अगली बार एक और नेटकास्ट से देखेंगे।
एक्सेल फ़ाइल डाउनलोड करें
एक्सेल फ़ाइल डाउनलोड करने के लिए: can-you-return-all-vlookup-values.xlsx
जब कोई पूछता है कि "क्या VLOOKUP सभी मैचों को वापस कर सकता है, तो इसका जवाब नहीं है। लेकिन, कई अन्य कार्य हैं जो अनिवार्य रूप से एक ही काम कर सकते हैं।
एक्सेल थॉट्स ऑफ द डे
मैंने अपने एक्सेल के बारे में सलाह के लिए अपने एक्सेल मास्टर दोस्तों से पूछा है। विचार करने के लिए आज का विचार:
"अपने डेटा को सामान्य करें क्योंकि आप दूसरों को आपके लिए अपना डेटा सामान्य कर देंगे"
केविन लेहरबस
