एक्सेल सूत्र: अलग-अलग कॉलम में कई मिलान निकालें -

सारांश

अलग-अलग कॉलम में, अलग-अलग सेल में कई मिलान निकालने के लिए, आप INDEX और SMALL के आधार पर एक सरणी सूत्र का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, F5 में सूत्र है:

(=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($E$5:E5))),""))

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

प्रथम कक्ष में सूत्र दर्ज करने के बाद, इसे नीचे खींचें और अन्य कक्षों में भरने के लिए।

स्पष्टीकरण

नोट: यह सूत्र दो नामित श्रेणियों का उपयोग करता है: "नाम" C5: C11 को संदर्भित करता है, और "समूह" B5 को संदर्भित करता है: B11। इन नामों को ऊपर स्क्रीन शॉट में भी परिभाषित किया गया है।

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

चाल यह है कि SMALL एक सरणी के साथ काम कर रहा है जो कि इस बिट में IF द्वारा गतिशील रूप से निर्मित है:

IF(groups=$E5,ROW(names)-MIN(ROW(names))+1)

यह स्निपेट ई 5 में मूल्य के लिए नामित सीमा "समूहों" का परीक्षण करता है। यदि पाया जाता है, तो यह संबंधित पंक्ति संख्या के सरणी से एक पंक्ति संख्या देता है:

ROW(names)-MIN(ROW(names))+1

अंतिम परिणाम एक सरणी है जिसमें संख्याएँ होती हैं जहाँ एक मैच होता है, और FALSE जहाँ नहीं होता है:

(1; FALSE; FALSE; FALSE; FALSE; 6; FALSE)

यह सरणी SMALL में जाती है। SMALL (nth) का k मान एक विस्तार सीमा से आता है:

COLUMNS($E$5:E5)

जब परिणाम तालिका में कॉपी की जाती है, तो सीमा का विस्तार होता है, जिससे k (nth) वृद्धि होती है। SMALL फ़ंक्शन प्रत्येक मिलान पंक्ति संख्या को लौटाता है, जिसे INDEX फ़ंक्शन को row_num के रूप में दिया जाता है, सरणी के रूप में नामित सीमा "नाम" के साथ।

त्रुटियों को संभालना

जब COLUMNS k के लिए एक मान देता है जो मौजूद नहीं है, SMALL एक #NUM त्रुटि फेंकता है। ऐसा सभी मैचों के होने के बाद होता है। त्रुटि को दबाने के लिए, हम IFERROR फ़ंक्शन में सूत्र को त्रुटियों को पकड़ने और एक रिक्त स्ट्रिंग ("") को वापस करने के लिए लपेटते हैं।