एक्सेल ट्यूटोरियल: लगभग मैच लुकअप को कैसे उजागर करें

विषय - सूची

संबंधित शॉर्टकट

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

यहां हमारे पास एक सरल लुकअप टेबल है जो विभिन्न ऊंचाइयों और चौड़ाई के लिए सामग्री लागत को दर्शाता है। K8 में सूत्र K6 और K7 में दर्ज चौड़ाई और ऊंचाई के मूल्यों के आधार पर सही लागत को पुनः प्राप्त करने के लिए INDEX और MATCH फ़ंक्शन का उपयोग करता है।

ध्यान दें कि लुकअप एक अनुमानित मैच पर आधारित है। चूँकि मान बढ़ते क्रम में हैं, MATCH मानों की जाँच तब तक करता है जब तक कि एक बड़ा मूल्य नहीं हो जाता है, और फिर वापस कदम रखता है और पिछली स्थिति को लौटाता है।

आइए मिलान की गई पंक्ति और स्तंभ को हाइलाइट करने के लिए एक सशर्त स्वरूपण नियम बनाएँ।

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

मैं पहले चौड़ाई के लिए सूत्र सेट करूँगा। हमें पंक्ति 7 में प्रत्येक सेल के लिए TRUE लौटाने की आवश्यकता है, जहाँ मिलान की गई चौड़ाई 200 है।

इसका मतलब है कि हम अपना फॉर्मूला $ B5 = से शुरू करते हैं, और हमें कॉलम को लॉक करने की आवश्यकता है।

= $ B5 =

अब, हम चौड़ाई कॉलम में 275 की तलाश नहीं कर सकते, क्योंकि यह वहां नहीं है। इसके बजाय, हमें हमारे लुकअप फॉर्मूले की तरह ही एक अनुमानित मिलान की आवश्यकता है जो 200 को पाता है।

इसका सबसे आसान तरीका यह है कि LOOKUP फ़ंक्शन का उपयोग किया जाए। LOOKUP स्वचालित रूप से एक अनुमानित मिलान करता है, और, MATCH जैसी स्थिति में लौटने के बजाय, LOOKUP वास्तविक मिलान मान लौटाता है। तो, हम लिख सकते हैं:

$ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12)

लुकअप मान के लिए हमारी इनपुट चौड़ाई और परिणाम वेक्टर के लिए तालिका में सभी चौड़ाई के साथ।

यदि मैं F9 का उपयोग करता हूं, तो आप LOOKUP रिटर्न मान देख सकते हैं।

अब जब मैं तालिका में सूत्र दर्ज करता हूं, तो हमें 200-चौड़ाई की पंक्ति में प्रत्येक सेल के लिए TRUE मिलता है।

अब हमें ऊंचाई कॉलम से मिलान करने के लिए सूत्र का विस्तार करने की आवश्यकता है। ऐसा करने के लिए, मैं OR फ़ंक्शन जोड़ूंगा, और फिर ऊंचाई से मिलान करने के लिए एक दूसरा सूत्र।

हम उसी तरह सूत्र शुरू करेंगे, लेकिन इस बार हमें पंक्ति को लॉक करने की आवश्यकता है:

= बी $ ५

फिर हम लुकअप फ़ंक्शन का उपयोग फिर से लुकअप वैल्यू के लिए ऊंचाई के साथ करते हैं और परिणाम वेक्टर के रूप में तालिका में सभी ऊंचाइयों पर।

= या ($ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = LOOKUP ($ K $ 7, $ C $ 5: $ H $ 5))

जब मैं तालिका में सूत्र को कॉपी करता हूं, तो हमें मिलान पंक्ति में प्रत्येक सेल के लिए TRUE मिलता है और मिलान पंक्ति में प्रत्येक सेल - बस हमें सशर्त स्वरूपण की आवश्यकता है।

मैं बस ऊपरी बाएँ सेल में सूत्र की प्रतिलिपि बना सकता हूँ, और एक नया नियम बना सकता हूँ।

अब यदि मैं चौड़ाई या ऊँचाई को बदलता हूँ, तो हाइलाइटिंग अपेक्षित रूप से काम करती है।

अंत में, यदि आप केवल लुकअप वैल्यू को हाइलाइट करना चाहते हैं, तो यह एक साधारण बदलाव है। बस सूत्र को संपादित करें और OR फ़ंक्शन को AND फ़ंक्शन के साथ बदलें।

= और ($ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = LOOKUP ($ K $ 7, $ C $ 5: $ H $ 5))