सारांश
अलग-अलग पंक्तियों में, अलग-अलग पंक्तियों में, कई मिलान निकालने के लिए, आप INDEX और SMALL के आधार पर एक सरणी सूत्र का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, E5 में सूत्र है:
(=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$5:E5))),""))
यह एक सरणी सूत्र है और इसे Control + Shift + Enter के साथ दर्ज किया जाना चाहिए।
प्रथम कक्ष में सूत्र दर्ज करने के बाद, इसे नीचे खींचें और अन्य कक्षों में भरने के लिए।
स्पष्टीकरण
नोट: यह सूत्र दो नामित श्रेणियों का उपयोग करता है: "नाम" C4: C11 को संदर्भित करता है, और "समूह" B4 को संदर्भित करता है: B11। इन नामों को ऊपर स्क्रीन शॉट में भी परिभाषित किया गया है।
इस सूत्र का सार यह है: हम SMALL फ़ंक्शन का उपयोग एक पंक्ति संख्या को प्राप्त करने के लिए कर रहे हैं जो "nth मैच" से मेल खाती है। एक बार जब हमारे पास पंक्ति संख्या होती है, तो हम बस इसे INDEX फ़ंक्शन फ़ंक्शन में पास करते हैं, जो उस पंक्ति में मान लौटाता है।
चाल यह है कि SMALL एक सरणी के साथ काम कर रहा है जो कि इस बिट में IF द्वारा गतिशील रूप से निर्मित है:
IF(groups=E$4,ROW(names)-MIN(ROW(names))+1)
यह स्निपेट ई 4 में मूल्य के लिए नामित सीमा "समूहों" का परीक्षण करता है। यदि पाया जाता है, तो यह सूत्र के इस हिस्से के साथ बनाई गई पंक्ति संख्याओं की एक सरणी से "सामान्यीकृत" पंक्ति संख्या देता है:
ROW(names)-MIN(ROW(names))+1
परिणाम एक ऐसा सरणी है जिसमें पंक्ति संख्याएँ होती हैं जहाँ एक मेल होता है, और FALSE जहाँ नहीं होता है। सरणी कुछ इस तरह दिखती है:
(1; FALSE; FALSE; FALSE; FALSE; 6; FALSE)
यह सरणी SMALL में जाती है। SMALL (nth) का k मान एक विस्तार सीमा से आता है:
ROWS($E$5:E5)
जब परिणाम तालिका को कॉपी किया जाता है, तो सीमा का विस्तार होता है, जिससे k (nth) वृद्धि होती है। SMALL फ़ंक्शन प्रत्येक मिलान पंक्ति संख्या को लौटाता है, जिसे INDEX फ़ंक्शन को row_num के रूप में दिया जाता है, सरणी के रूप में नामित सीमा "नाम" के साथ।
त्रुटियों को संभालना
जब ROWS का मान मौजूद नहीं होता है, तो SMALL एक #NUM त्रुटि फेंकता है। ऐसा सभी मैचों के होने के बाद होता है। त्रुटि को दबाने के लिए, हम IFERROR का उपयोग त्रुटि को पकड़ने और खाली स्ट्रिंग ("") को वापस करने के लिए करते हैं।