एक्सेल सूत्र: निकटतम मैच का पता लगाएं -

सामान्य सूत्र

(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))

सारांश

संख्यात्मक डेटा में निकटतम मैच खोजने के लिए, आप ABS और MIN फ़ंक्शंस की मदद से INDEX और MATCH का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, F5 का फॉर्मूला, नीचे कॉपी किया गया है:

=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))

जहां यात्रा (B5: B14) और लागत (C5: C14) को नाम दिया गया है।

F5, F6, और F7 में, फॉर्मूला क्रमशः 500, 1000 और 1500 की लागत से यात्रा को निकटतम देता है।

नोट: यह एक सरणी सूत्र है और इसे Excel 365 को छोड़कर नियंत्रण + शिफ्ट + दर्ज के साथ दर्ज किया जाना चाहिए।

स्पष्टीकरण

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

MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)

चीजों को चरण-दर-चरण लेते हुए, लुकअप मान की गणना MIN और ABS के साथ की जाती है:

MIN(ABS(cost-E5)

सबसे पहले, E5 में मूल्य नामित सीमा लागत (C5: C14) से घटाया जाता है । यह एक सरणी ऑपरेशन है, और चूंकि रेंज में 10 मान हैं, परिणाम इस तरह के 10 मानों के साथ एक सरणी है:

(899;199;250;-201;495;1000;450;-101;500;795)

ये संख्या C5: C15 में प्रत्येक लागत और सेल E5, 700 में लागत के बीच के अंतर को दर्शाती हैं। कुछ मूल्य नकारात्मक हैं क्योंकि एक लागत E5 में संख्या से कम है। नकारात्मक मूल्यों को सकारात्मक मूल्यों में बदलने के लिए, हम ABS फ़ंक्शन का उपयोग करते हैं:

ABS((899;199;250;-201;495;1000;450;-101;500;795))

कौन सा रिटर्न:

(899;199;250;201;495;1000;450;101;500;795)

हम निकटतम मैच की तलाश कर रहे हैं, इसलिए हम सबसे छोटे अंतर को खोजने के लिए MIN फ़ंक्शन का उपयोग करते हैं, जो कि 101 है:

MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101

यह MATCH के अंदर लुकअप वैल्यू बन जाता है। लुकअप सरणी पहले की तरह उत्पन्न होती है:

ABS(cost-E5) // generate lookup array

वही सरणी जो हमने पहले देखी थी:

(899;199;250;201;495;1000;450;101;500;795)

अब हमारे पास वह है जो हमें निकटतम मैच की स्थिति (सबसे छोटा अंतर) खोजने की आवश्यकता है, और हम इस तरह से सूत्र के MATCH हिस्से को फिर से लिख सकते हैं:

MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8

101 लुकअप वैल्यू के रूप में, MATCH 8 रिटर्न करता है, क्योंकि 101 एरे में 8 वें स्थान पर है। अंत में, यह स्थिति पंक्ति तर्क के रूप में INDEX में दी गई है, नाम श्रेणी की यात्रा के साथ सरणी के रूप में:

=INDEX(trip,8)

और INDEX ने "स्पेन" श्रेणी में 8 वीं यात्रा लौटा दी। जब सूत्र को F6 और F7 कोशिकाओं में कॉपी किया जाता है, तो यह 1000 और 1500 के निकटतम मैच का पता लगाता है, "फ्रांस" और "थाईलैंड" जैसा कि दिखाया गया है।

नोट: यदि कोई टाई है, तो यह सूत्र पहला मैच लौटाएगा।

XLOOKUP के साथ

XLOOKUP फ़ंक्शन इस समस्या को हल करने का एक दिलचस्प तरीका प्रदान करता है, क्योंकि 1 प्रकार का मैच (सटीक मैच या अगला सबसे बड़ा) या -1 (सटीक मैच या अगला सबसे छोटा) डेटा को सॉर्ट करने की आवश्यकता नहीं है। इसका मतलब है कि हम इस तरह एक सूत्र लिख सकते हैं:

=XLOOKUP(0,ABS(cost-E5),trip,,1)

जैसा कि ऊपर, हम लुकअप ऐरे बनाने के लिए (कॉस्ट-ई 5) के निरपेक्ष मान का उपयोग करते हैं:

(899;199;250;201;495;1000;450;101;500;795)

फिर हम मिलान के लिए सेट 1 के साथ, सटीक मिलान या अगले सबसे बड़े के लिए शून्य की तलाश के लिए XLOOKUP कॉन्फ़िगर करते हैं। हम रिटर्न रेंज के रूप में नामित रेंज ट्रिप की आपूर्ति करते हैं , इसलिए परिणाम पहले की तरह "स्पेन" है।