एक्सेल 2020: XLOOKUP के बारह फायदे - एक्सेल टिप्स

नया XLOOKUP फ़ंक्शन नवंबर 2019 में शुरू होने वाले Office 365 के लिए चल रहा है। एक्सेल टीम के जो मैकडैड ने XLOOKUP को डिज़ाइन किया है जो VLOOKUP और INDEX / MATCH का उपयोग करने वाले लोगों को एकजुट करने के लिए बनाया गया है। यह अनुभाग XLOOKUP के 12 लाभों पर चर्चा करेगा:

  1. सटीक मिलान डिफ़ॉल्ट है।
  2. VLOOKUP का पूर्णांक आधारित तीसरा तर्क अब एक उचित संदर्भ है।
  3. IFNA में अंतर्निहित मानों को संभालने के लिए अंतर्निहित है।
  4. XLOOKUP को बाईं ओर जाने में कोई समस्या नहीं है।
  5. तालिका को छांटे बिना अगला-छोटा या अगला-बड़ा मिलान ढूंढें।
  6. XLOOKUP HLOOKUP कर सकते हैं।
  7. नीचे से खोजकर आखिरी मैच खोजें।
  8. वाइल्डकार्ड डिफ़ॉल्ट रूप से "बंद" होते हैं, लेकिन आप उन्हें वापस चालू कर सकते हैं।
  9. एक सूत्र में सभी 12 महीने लौटें।
  10. एक सेल संदर्भ वापस कर सकते हैं अगर XLOOKUP एक ​​कॉलन के बगल में है जैसे XLOOKUP (); XLOOKUP ();
  11. INDEX (, MATCH, MATCH) जैसे टू-वे मैच कर सकते हैं।
  12. LOOKUP कर सकता है जैसे एक ही सूत्र में सभी लुकअप राशि जोड़ सकते हैं।

यहाँ सिंटैक्स है: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)।

XLOOKUP लाभ 1: डिफ़ॉल्ट रूप से सटीक मिलान

मेरे VLOOKUP फॉर्मूले का 99%, सटीक मिलान को इंगित करने के लिए FALSE या, 0 में समाप्त होता है। यदि आप हमेशा VLOOKUP के सटीक मिलान संस्करण का उपयोग करते हैं, तो आप अपने XLOOKUP फ़ंक्शन से match_mode को छोड़ना शुरू कर सकते हैं।

निम्नलिखित आकृति में, आप सेल A4 से W25-6 देख रहे हैं। आप उस आइटम को L8: L35 में देखना चाहते हैं। जब यह पाया जाता है, तो आप कॉलम एन से संबंधित मूल्य चाहते हैं। फाल्स को मिलान_मॉड के रूप में निर्दिष्ट करने की कोई आवश्यकता नहीं है क्योंकि एक्सएलबुकअप एक सटीक मैच में चूक करता है।

X 4 में XLOOKUP मान। L8 में देखें: L35 N8: N35 से संबंधित मूल्य लौटाएं।

XLOOKUP लाभ 2: Results_Array एक पूर्णांक के बजाय एक संदर्भ है

VLOOKUP फॉर्मूला के बारे में सोचें जो आप XLOOKUP से पहले उपयोग करेंगे। तीसरा तर्क यह इंगित करने के लिए 3 होगा कि आप तीसरा कॉलम वापस करना चाहते हैं। इस बात का खतरा हमेशा बना रहता था कि आपकी मेज पर एक स्तंभकार सहकर्मी एक कॉलम (या हटाए) गया होगा। तालिका में एक अतिरिक्त कॉलम के साथ, VLOOKUP जो एक मूल्य लौटा रहा था, एक विवरण लौटाना शुरू कर देगा। क्योंकि XLOOKUP एक ​​सेल संदर्भ की ओर इशारा कर रहा था, सूत्र उस कॉलम ओ में अब उस मूल्य की ओर संकेत करने के लिए खुद को फिर से लिखता है।

यदि कोई लुकअप तालिका में कोई नया स्तंभ सम्मिलित करता है तो पुराना VLOOKUP विफल हो जाएगा। XLOOKUP काम करता रहता है।

XLOOKUP लाभ 3: IFNA एक वैकल्पिक तर्क के रूप में बनाया गया है

जब आपके लुकअप मान को तालिका में नहीं मिला है तो घबराया हुआ # N / A त्रुटि लौटा दी जाती है। अतीत में, # N / A को किसी अन्य चीज़ से बदलने के लिए, आपको IFERROR या IFNA का उपयोग VLOOKUP के चारों ओर लिपटा हुआ करना होगा।

जब कोई आइटम नहीं मिलता है, तो यह VLOOKUP या XLOOKUP से # N / A देता है …

मेरे YouTube चैनल पर रीको के सुझाव के लिए धन्यवाद, Excel टीम ने if_not_found के लिए एक वैकल्पिक चौथे तर्क को शामिल किया। यदि आप उन # एन / ए त्रुटियों को शून्य से बदलना चाहते हैं, तो बस चौथे तर्क के रूप में जोड़ दें। या, आप कुछ पाठ का उपयोग कर सकते हैं, जैसे "मूल्य नहीं मिला"।

XLOOKUP में वैकल्पिक चौथा तर्क "यदि नहीं मिला है" है। वहाँ एक 0 या "नहीं मिला" रखो।

XLOOKUP लाभ 4: प्रमुख क्षेत्र के बाईं ओर देखने में कोई समस्या नहीं है

VLOOKUP VLOOKUP (A4, CHOOSE ((1,2), G7: G34: F7: F34: F34), 2, False) का सहारा लिए बिना प्रमुख क्षेत्र के बाईं ओर नहीं देख सकते हैं। XLOOKUP के साथ, Lookup_array के बाईं ओर Results_array होने में कोई समस्या नहीं है।

XLOOKUP के साथ, स्तंभ G से भाग संख्या को देखते हुए कोई समस्या नहीं है, जबकि स्तंभ G में भाग संख्या देख रहे हैं। यह हमेशा VLOOKUP की कमजोरी थी: यह बाईं ओर नहीं देख सकता था।

XLOOKUP लाभ 5: छँटाई के बिना अगला-छोटा या अगला-बड़ा मैच

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

VLOOKUP के अनुमानित मैच संस्करण का एक उदाहरण। 10 हजार से 20 हजार तक की किसी भी बिक्री पर 12 डॉलर का बोनस मिलता है।

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

XLOOKUP का वैकल्पिक पाँचवाँ तर्क match_mode केवल सटीक मैच के लिए देख सकता है, या उसके बराबर छोटा या केवल बड़ा हो सकता है। ध्यान दें कि XLOOKUP में मूल्य MATCH की तुलना में अधिक अर्थ रखते हैं:

  • -1 मान को बराबर या उससे छोटा पाता है
  • 0 एक सटीक मैच खोजें
  • 1 के बराबर या उससे बड़ा मान पाता है।

लेकिन, सबसे आश्चर्यजनक हिस्सा: लुकअप टेबल को क्रमबद्ध नहीं करना है और कोई भी मिलान_मोड काम करेगा।

नीचे, -1 का एक match_mode अगला-छोटा आइटम ढूंढें।

XLOOKUP का पांचवा तर्क Match_Mode है। 0 सटीक मैच के लिए है। नकारात्मक एक का उपयोग सटीक मिलान या अगले छोटे आइटम के लिए किया जाता है। सकारात्मक 1 सटीक मिलान या अगले बड़े आइटम के लिए है। 2 वाइल्डकार्ड मैच के लिए है। चौथे तर्क में वीएलबुक को सही के साथ प्रदर्शित करने के लिए, एक नकारात्मक एक को XLOOKUP में मैच_मॉड तर्क के रूप में रखें।

यहां, 1 का एक match_mode, पाता है कि पार्टी में लोगों की संख्या के आधार पर किस वाहन की आवश्यकता है। ध्यान दें कि लुकअप टेबल यात्रियों द्वारा सॉर्ट नहीं किया गया है और वाहन का नाम कुंजी के बाईं ओर है।

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

तालिका कहती है:

  • बस में 64 लोग सवार हैं
  • कार में 4 लोग सवार हैं
  • मोटरसाइकल में 1 व्यक्ति रहता है
  • टूर वान में 12 लोग रहते हैं
  • वैन में 6 लोग सवार थे।

एक बोनस के रूप में, डेटा वाहन द्वारा सॉर्ट किया जाता है (पुराने समाधान में, MATCH का उपयोग करके, तालिका को क्षमता द्वारा अवरोही क्रमबद्ध करना होगा। यह भी: वाहन क्षमता के बाईं ओर है।

XLOOKUP लाभ 6: Sideways XLOOKUP HLOOKUP की जगह लेता है

लुकअप_अरे और results_array XLOOKUP के साथ क्षैतिज हो सकते हैं, जिससे HLOOKUP को बदलना आसान हो जाता है।

यहां लुकअप टेबल क्षैतिज है। अतीत में, इसके लिए HLOOKUP की आवश्यकता होती है, लेकिन XLOOKUP एक ​​तालिका से निपट सकता है जो बग़ल में जाती है।

XLOOKUP लाभ 7: नवीनतम मैच के लिए नीचे से खोजें

मेरे पास YouTube पर एक पुराना वीडियो है जो एक ब्रिटिश घोड़े के खेत के एक सवाल का जवाब दे रहा है। उनके पास वाहनों का बेड़ा था। हर बार जब कोई वाहन ईंधन या सेवा के लिए आता है, तो वे एक स्प्रेडशीट में वाहन, तिथि और माइलेज लॉग करते हैं। वे प्रत्येक वाहन के लिए नवीनतम ज्ञात लाभ प्राप्त करना चाहते थे। जबकि एक्सेल-2017 युग MAXIFS आज इसे हल कर सकता है, कई साल पहले समाधान LOOKUP का उपयोग करते हुए एक आर्कन फार्मूला था और इसमें शून्य के साथ विभाजन शामिल था।

आज, XLOOKUP का वैकल्पिक छठा तर्क आपको यह निर्दिष्ट करने देता है कि खोज डेटा सेट के नीचे से शुरू होनी चाहिए।

सूची में अंतिम मैच का पता लगाएं।

ध्यान दें

जबकि यह एक महान सुधार है, यह आपको पहला या अंतिम मैच खोजने देता है। कुछ लोगों को उम्मीद थी कि इससे आपको दूसरा या तीसरा मैच मिल जाएगा, लेकिन यह search_mode तर्क का उद्देश्य नहीं है।

सावधान

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

XLOOKUP लाभ 8: वाइल्डकार्ड डिफ़ॉल्ट रूप से "बंद" होते हैं

अधिकांश लोगों को पता ही नहीं चला कि VLOOKUP पृष्ठ १४३ पर "# ५१ वाइल्डकार्ड इन वील्डबुक" में वर्णित वाइल्डकार्ड वर्णों के रूप में तारांकन चिह्न, प्रश्न चिह्न और टिल्ड का इलाज कर रहा है। XLOOKUP के साथ, वाइल्डकार्ड डिफ़ॉल्ट रूप से चालू हो जाते हैं। यदि आप चाहते हैं कि XLOOKUP इन वर्णों को वाइल्डकार्ड के रूप में मानें, तो 2 का उपयोग Match_Mode के रूप में करें।

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

XLOOKUP लाभ 9: एकल फॉर्मूला में सभी 12 महीने लौटाएं!

यह वास्तव में डायनामिक एरे का एक लाभ है, लेकिन यह एक्सएलबुकअप से प्यार करने का मेरा पसंदीदा कारण है। जब आपको लुकअप में सभी 12 महीने वापस करने पड़ते हैं, तो आयताकार रिटर्न_अरे के साथ B6 में दर्ज किया गया एक एकल फॉर्मूला कई परिणाम देगा। वे परिणाम आसन्न कोशिकाओं में फैल जाएंगे।

नीचे दिए गए आंकड़े में, B7: M7 में दिखाए गए सभी 12 उत्तरों को B7 में दर्ज किया गया एक एकल सूत्र है।

जनवरी कॉलम में एक एकल XLOOKUP जनवरी के लिए दिसंबर के माध्यम से संख्या देता है। यह 12 कॉलम के साथ एक results_array निर्दिष्ट करके किया जाता है।

XLOOKUP बेनिफिट 10: यदि कर्नल के निकट हो तो सेल संदर्भ लौटा सकता है

यह एक जटिल लेकिन सुंदर है। अतीत में, सात कार्य थे जो एक सेल संदर्भ को वापस करने से सेल संदर्भ को वापस करने के लिए बदल देंगे यदि फ़ंक्शन एक कॉलोन को छू रहा था। एक उदाहरण के लिए, गैर-वाष्पशील OFFSET के रूप में A2: INDEX () का उपयोग करें देखें। XLOOKUP CHOOSE, IF, IFS, INDEX, INDIRECT, OFFSET और SWITCH को मिलाकर इस व्यवहार की पेशकश करने वाला आठ कार्य है।

निम्नलिखित आकृति पर विचार करें। कोई व्यक्ति E4 में चेरी और E5 में छवि का चयन करता है। आप एक ऐसा फॉर्मूला चाहते हैं जो B6 से B9 तक सब कुछ समेटे।

आंकड़ा दो कोशिकाओं में दो XLOOKUP सूत्र दिखाता है। सेल बी 6 से पहला रिटर्न 15। दूसरे ने बी 9 से 30 की वापसी की। लेकिन फिर एक तीसरे सेल में, एक सूत्र है जो एक कॉलोन के साथ दो XLOOKUP फॉर्मूले को जोड़ता है और फिर एक SUM फ़ंक्शन में लपेटता है। इसका परिणाम B6: B9 का SUM है, क्योंकि XLOOKUP एक ​​सेल संदर्भ लौटा सकता है यदि फ़ंक्शन एक ऑपरेटर जैसे कि एक कोलन के बगल में दिखाई देता है। यह साबित करने के लिए कि यह काम कर रहा है, अगले कई आंकड़े इस सूत्र को मूल्यांकन फॉर्मूला संवाद में दिखाएंगे।

ऊपर दिए गए आंकड़े में, आप देख सकते हैं कि E4 का एक XLOOKUP 15 सेल B6 से वापस आ जाएगा। E5 का एक XLOOKUP 30 B9 से वापस आएगा। हालाँकि, यदि आप D9 और D10 कक्षों से दो XLOOKUP फ़ंक्शंस लेते हैं और उन्हें कॉलन-इन के साथ जोड़ते हैं, तो XLOOKUP का व्यवहार बदल जाता है। 15 लौटने के बजाय, पहला XLOOKUP सेल एड्रेस B6 लौटाता है!

यह साबित करने के लिए, मैंने D7 का चयन किया है और सूत्र का उपयोग करें, फॉर्मूला का मूल्यांकन करें। दो बार मूल्यांकन करने के बाद, गणना किया जाने वाला अगला भाग XLOOKUP ("चेरी", A4: A29, B4: B29) है, जैसा कि यहां दिखाया गया है।

यह पहले XLOOKUP का मूल्यांकन करने से ठीक पहले मूल्यांकन फॉर्मूला संवाद दिखाता है। यह XLOOKUP एक ​​कोलन से ठीक पहले दिखाई देता है।

फिर से मूल्यांकन करें और आश्चर्यजनक रूप से, XLOOKUP सूत्र B6 में संग्रहीत 15 के बजाय $ B $ 6 लौटाता है। ऐसा इसलिए होता है क्योंकि इस XLOOKUP सूत्र के तुरंत बाद एक बृहदान्त्र होता है।

मूल्यांकन पर क्लिक करें और पहला XLOOKUP 15 के बजाय $ B $ 6 लौटाता है।

प्रेस दो बार मूल्यांकन करें, और अंतरिम सूत्र = SUM (B6: B9) होगा।

दूसरे XLOOKUP का मूल्यांकन करने के बाद, अंतरिम सूत्र = SUM (B6: B9) है।

यह अद्भुत व्यवहार है जिसके बारे में ज्यादातर लोग नहीं जानते हैं। एक्सेल एमवीपी चार्ल्स विलियम्स मुझे बताता है कि यह एक्सएलबुक के बगल में इन तीन ऑपरेटरों में से किसी के साथ शुरू हो सकता है:

  • कोलोन
  • अंतरिक्ष (अंतरवाही ऑपरेटर)
  • कोमा (संघ संचालक)

XLOOKUP लाभ 11: INDEX (, MATCH, MATCH) जैसे दो-तरफ़ा मैच

मेरे सभी VLOOKUP दोस्तों के लिए, INDEX / MATCH लोग यह देखने के लिए प्रतीक्षा कर रहे हैं कि क्या XLOOKUP दो-तरफ़ा मैच को संभाल सकता है या नहीं। बड़ी खबर: यह कर सकता है। बुरी खबर: कार्यप्रणाली INDEX / MATCH प्रशंसकों की अपेक्षा थोड़ी अलग है। यह उनके सिर पर थोड़ा हो सकता है। लेकिन मुझे यकीन है कि वे इस पद्धति के आसपास आ सकते हैं।

दो-तरफ़ा मैच के लिए, आप यह जानना चाहते हैं कि J3 में दर्शाई गई खाता संख्या A621 में कौन सी पंक्ति है। तो, XLOOKUP काफी आसान शुरू होता है: = XLOOKUP (J3, A5: A15। लेकिन फिर आपको एक result_array प्रदान करना होगा। आप उसी चाल का उपयोग कर सकते हैं जैसा कि XLOOKUP लाभ 9: उपरोक्त सभी 12 महीने एक ही फॉर्मूला में करें, लेकिन वर्टिकल वेक्टर को वापस करने के लिए इसका उपयोग करें। एक आंतरिक XLOOKUP महीने में J4 के लिए B4: G4 में शीर्षासन करता है। Return_array को B5: G15 के रूप में निर्दिष्ट किया जाता है। इसका परिणाम यह है कि आंतरिक XLOOKUP I10 में दिखाए गए अक्षर की तरह एक सरणी देता है। I20 नीचे। चूँकि A621 लुकअप_एयर के पांचवें सेल में पाया जाता है और 104 रिजल्ट_अरे के पांचवें सेल में पाया जाता है, आपको फॉर्मूला से सही उत्तर मिलता है। नीचे, J6 पुराने तरीके को दिखाता है। J7 नया तरीका देता है।

A5: A15 में खातों की सूची में XLookup J3। परिणाम सरणी के लिए, XLOOKUP (J4, B4: G4, B5: G15) का उपयोग करें। इस सूत्र में, B4: G4 महीनों की सूची है। बी 5: जी 15 सभी महीनों के लिए सभी खातों के लिए आयताकार सरणी है। एक अन्य सेल में, बस आंतरिक XLOOKUP दिखाता है कि यह मई के लिए मूल्यों के पूरे कॉलम को कैसे लौटाता है।

XLOOKUP लाभ 12: एकल रूप में सभी लुकअप मानों को जोड़ दें

प्राचीन LOOKUP फ़ंक्शन ने दो अजीब चाल की पेशकश की। सबसे पहले, यदि आप जमा करने के लिए बोनस व्यय की कुल राशि का पता लगाने की कोशिश कर रहे हैं, तो आप LOOKUP को एक सूत्र में सभी मान देखने के लिए कह सकते हैं। नीचे की छवि में, LOOKUP (C4: C14 11 लुकअप कर रहा है। लेकिन LOOKUP फ़ंक्शन ने सटीक मिलान नहीं किया है और लुकअप तालिका को क्रमबद्ध करने की आवश्यकता है।

लुकअप 13 मान और उन्हें योग। यह LOOKUP के साथ काम करता था, लेकिन यह XLOOKUP के साथ भी काम करता है। लुकअप मान C4: C14 को पहले तर्क के रूप में निर्दिष्ट करें। एक SUM फ़ंक्शन में XLOOKUP लपेटें।

XLOOKUP के साथ, आप एक सीमा निर्दिष्ट कर सकते हैं क्योंकि लुकअप_वेल्यू और XLOOKUP सभी उत्तरों को लौटा देगा। लाभ यह है कि XLOOKUP सटीक मिलान कर सकता है।

सभी लुकअप परिणामों का योग करने के लिए LOOKUP का उपयोग करने की चाल ने केवल लुकअप के अनुमानित मिलान संस्करण के साथ काम किया। यहां, XLOOKUP L4: L14 में सभी नामों पर सटीक मिलान कर रहा है और सभी परिणामों को प्राप्त कर रहा है।

बोनस टिप: एक मुड़ पुस्तक के बारे में क्या?

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

यहाँ लुकअप सरणी लंबवत है और परिणाम सरणी क्षैतिज है। पुराना LOOKUP फ़ंक्शन इसे संभाल सकता है, लेकिन इसे XLOOKUP के साथ करने के लिए, आपको ट्रांसपोज़ में या तो सरणी लपेटनी होगी।

दिलचस्प लेख...