सामान्य सूत्र
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
सारांश
"समाहित" मिलान वाले कीवर्ड का उपयोग करके पाठ को वर्गीकृत करने के लिए, आप INDEX और MATCH की मदद से SEARCH फ़ंक्शन का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, C5 में सूत्र है:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
जहाँ कीवर्ड्स का नाम E5: E14 है, और श्रेणियों का नाम F5: F14 है।
नोट: यह एक सरणी सूत्र है और इसे नियंत्रण + शिफ्ट + दर्ज के साथ दर्ज किया जाना चाहिए।
स्पष्टीकरण
मूल में, यह एक INDEX और MATCH फ़ंक्शन है।
MATCH फ़ंक्शन के अंदर, हम खोज श्रेणी कीवर्ड (E5: E14) में प्रत्येक सूचीबद्ध कीवर्ड के लिए कॉलम B में सेल खोजने के लिए SEARCH फ़ंक्शन का उपयोग करते हैं :
SEARCH(keywords,B5)
क्योंकि हम कई मदों (नामांकित श्रेणी के कीवर्ड में ) की तलाश कर रहे हैं , हम इस तरह से कई परिणाम प्राप्त करेंगे:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
मूल्य! त्रुटि तब होती है जब खोज पाठ नहीं खोज सकता। जब SEARCH एक मैच पाता है, तो यह एक संख्या देता है जो सेल के अंदर पाठ की स्थिति से मेल खाती है।
इन परिणामों को और अधिक उपयोगी प्रारूप में बदलने के लिए, हम ISNUMBER फ़ंक्शन का उपयोग करते हैं, जो सभी मानों को TRUE / FALSE में परिवर्तित करता है:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
यह सरणी MATCH फ़ंक्शन के रूप में लुकअप_अरे के रूप में जाती है, जिसमें लुकअप_वेल्यू TRUE के रूप में सेट होता है। MATCH ने सरणी में पाए जाने वाले पहले TRUE की स्थिति लौटा दी (इस मामले में 7) जो INDEX फ़ंक्शन को row_num के रूप में प्रदान की गई है:
=INDEX(categories,7)
INDEX अंतिम परिणाम के रूप में "ऑटो" श्रेणियों में 7 वाँ आइटम लौटाता है ।
XLOOKUP के साथ
XLOOKUP फ़ंक्शन के साथ, इस सूत्र को कुछ हद तक सरल बनाया जा सकता है। XLOOKUP ऊपर दिए गए MATCH फ़ंक्शन के अंदर उपयोग किए गए एक ही तर्क का उपयोग कर सकता है, इसलिए समतुल्य सूत्र है:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP सरणी में पहले TRUE का पता लगाता है, और श्रेणियों से संबंधित मान लौटाता है ।
झूठे मेल को रोकना
इस दृष्टिकोण के साथ एक समस्या यह है कि आप लंबे समय तक शब्दों के अंदर दिखाई देने वाले सबस्ट्रिंग से झूठे मेल प्राप्त कर सकते हैं। उदाहरण के लिए, यदि आप "ड्र" से मेल खाने की कोशिश करते हैं, तो आप "एंड्रिया", "ड्रिंक", "ड्राई" आदि को भी ढूंढ सकते हैं, क्योंकि "ड्र" इन शब्दों के अंदर दिखाई देता है। ऐसा इसलिए होता है क्योंकि SEARCH स्वचालित रूप से "समाहित" मैच करता है।
एक त्वरित हैक के लिए, आप दूसरे शब्दों में "ड्र" को पकड़ने से बचने के लिए खोज शब्द (यानी "डॉ", या "डॉ") के आसपास स्थान जोड़ सकते हैं। लेकिन यह विफल हो जाएगा यदि "ड्र" किसी सेल में पहले या अंतिम रूप से प्रकट होता है, या विराम चिह्न आदि के साथ दिखाई देता है।
यदि आपको अधिक सटीक समाधान की आवश्यकता है, तो एक विकल्प यह है कि पाठ को पहले एक सहायक कॉलम में सामान्य किया जाए, जिससे एक अग्रणी और अनुगामी स्थान को भी जोड़ा जा सके। फिर आप रिक्त स्थान से घिरे पूरे शब्दों को खोज सकते हैं।
