एक्सेल सूत्र: यदि सेल में कई चीजें शामिल हैं -

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

(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))

सारांश

कई स्ट्रिंग्स में से एक के लिए एक सेल का परीक्षण करने के लिए, और पहले मैच के लिए एक कस्टम परिणाम प्राप्त करें, आप SEARCH फ़ंक्शन के आधार पर एक INDEX / MATCH फॉर्मूला का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, C5 में सूत्र है:

(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))

जहां चीजें (E5: E8) और परिणाम (F5: F8) का नाम रेंज है।

यह एक सरणी सूत्र है और इसे Control + Shift + Enter के साथ दर्ज किया जाना चाहिए।

स्पष्टीकरण

यह सूत्र दो नामित श्रेणियों का उपयोग करता है: चीजें , और परिणाम । यदि आप इस सूत्र को सीधे पोर्ट कर रहे हैं, तो समान नाम (आपके डेटा के आधार पर परिभाषित) के साथ नामित श्रेणियों का उपयोग करना सुनिश्चित करें। यदि आप नामित श्रेणियों का उपयोग नहीं करना चाहते हैं, तो इसके बजाय पूर्ण संदर्भों का उपयोग करें।

इस सूत्र का मूल यह स्निपेट है:

ISNUMBER(SEARCH(things,B5)

यह एक अन्य सूत्र पर आधारित है (यहां विस्तार से बताया गया है) जो किसी एकल सबस्ट्रिंग के लिए एक सेल की जांच करता है। यदि कक्ष में सबस्ट्रिंग है, तो सूत्र TRUE लौटाता है। यदि नहीं, तो सूत्र FALSE लौटाता है।

क्योंकि हम SEARCH फ़ंक्शन को एक से अधिक चीज़ों को देखने के लिए दे रहे हैं , नामांकित श्रेणी की चीज़ों में , यह हमें एक परिणाम देगा, एक सरणी में जो इस तरह दिखता है:

(#VALUE!;9;#VALUE!;#VALUE!)

नंबर चीजों में मैचों का प्रतिनिधित्व करते हैं , त्रुटियां उन वस्तुओं का प्रतिनिधित्व करती हैं जो नहीं मिली थीं।

सरणी को सरल बनाने के लिए, हम सरणी के सभी आइटम को TRUE या FALSE में बदलने के लिए ISNUMBER फ़ंक्शन का उपयोग करते हैं। कोई भी वैध संख्या TRUE हो जाती है, और कोई भी त्रुटि (यानी कोई चीज़ नहीं मिली) FALSE हो जाती है। परिणाम इस तरह एक सरणी है:

(FALSE;TRUE;FALSE;FALSE)

जो MATCH फ़ंक्शन में लुक_अरे तर्क के रूप में जाता है, TRUE के लुकअप_वल्यू के साथ:

MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2

MATCH ने इस मामले में पहले TRUE पाया, 2 का स्थान दिया।

अंत में, हम उसी स्थान पर नामांकित श्रेणी परिणामों से परिणाम प्राप्त करने के लिए INDEX फ़ंक्शन का उपयोग करते हैं :

=INDEX(results,2) // returns "found red"

आप अपने उपयोग के मामले में जो भी मूल्य समझ में आते हैं उसके साथ आप परिणाम सीमा को अनुकूलित कर सकते हैं ।

झूठे मेल को रोकना

ISNUMBER + SEARCH दृष्टिकोण के साथ इस दृष्टिकोण के साथ एक समस्या यह है कि आप आंशिक मैच से लंबे शब्दों के अंदर झूठे मैच प्राप्त कर सकते हैं। उदाहरण के लिए, यदि आप "ड्र" से मेल खाने की कोशिश करते हैं, तो आप "एंड्रिया", "ड्रिंक", "ड्रिप" आदि भी ढूंढ सकते हैं, क्योंकि "ड्र" इन शब्दों के अंदर दिखाई देता है। ऐसा इसलिए होता है क्योंकि SEARCH स्वचालित रूप से "समाहित-प्रकार" मिलान करता है।

एक त्वरित फ़िक्स के लिए, आप दूसरे शब्दों में "ड्र" खोजने से बचने के लिए खोज शब्दों को अंतरिक्ष वर्णों (जैसे "ड्र", या "ड्र") में लपेट सकते हैं। लेकिन यह विफल हो जाएगा यदि "ड्र" किसी सेल में पहले या आखिरी में दिखाई देता है।

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