सामान्य सूत्र
(=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth)))
सारांश
सूत्र के साथ डेटा के एक सेट से कई मिलान मूल्यों को प्राप्त करने के लिए, आप IF और SMALL फ़ंक्शन का उपयोग प्रत्येक मैच की पंक्ति संख्या का पता लगाने के लिए कर सकते हैं और उस मान को INDEX पर वापस फीड कर सकते हैं। दिखाए गए उदाहरण में, I7 में सूत्र है:
(=INDEX(amts,SMALL(IF(ids=id,ROW(ids)-ROW(INDEX(ids,1,1))+1),H6)))
जहां नामित सीमाएं amts (D4: D11), id (I3), और id (C4: C11) हैं।
ध्यान दें कि यह एक सरणी सूत्र है और इसे Control + Shift + Enter के साथ दर्ज किया जाना चाहिए।
स्पष्टीकरण
मूल में, यह सूत्र केवल एक इंडेक्स सूत्र है जो किसी दिए गए स्थान पर एक सरणी में मान को पुनः प्राप्त करता है। N के लिए मान स्तंभ H में प्रदान किया गया है, और सभी "भारी" कार्य जो सूत्र करता है, वह उस पंक्ति का पता लगाने के लिए है जिसमें से मान प्राप्त करने के लिए, जहां पंक्ति "nth" मेल से मेल खाती है।
आईएफ फ़ंक्शन यह पता लगाने का काम करता है कि किन पंक्तियों में एक मैच होता है, और एसएमएएलएल फ़ंक्शन उस सूची से एनएचटी मान लौटाता है। IF के अंदर, तार्किक परीक्षण है:
ids=id
जो इस सरणी का उत्पादन करता है:
(TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
1 और 4 वें स्थान पर ग्राहक आईडी मिलान पर ध्यान दें, जो TRUE के रूप में दिखाई देते हैं। IF में "मान यदि सही है" तर्क इस अभिव्यक्ति के सापेक्ष पंक्ति संख्याओं की एक सूची तैयार करता है:
ROW(ids)-ROW(INDEX(ids,1,1))+1
जो इस सरणी का उत्पादन करता है:
(1;2;3;4;5;6;7)
यह सरणी तब तार्किक परीक्षण परिणामों द्वारा "फ़िल्टर्ड" की जाती है, और IF फ़ंक्शन निम्न सरणी परिणाम देता है:
(1;FALSE;FALSE;4;FALSE;FALSE;FALSE)
ध्यान दें कि हमारे पास पंक्ति 1 और पंक्ति 2 के लिए मान्य पंक्ति संख्याएँ हैं।
इस सरणी को तब SMALL द्वारा संसाधित किया जाता है, जिसे "nth" मानों को वापस करने के लिए स्तंभ H में मानों का उपयोग करने के लिए कॉन्फ़िगर किया गया है। लघु फ़ंक्शन स्वचालित रूप से सरणी में तार्किक मान TRUE और FALSE को अनदेखा करता है। अंत में, सूत्र निम्न करते हैं:
=INDEX(amts,1) // I6, returns $150 =INDEX(amts,4) // I7, returns $125
त्रुटियों को संभालना
एक बार दिए गए आईडी के लिए अधिक मैच नहीं होने पर, SMALL फ़ंक्शन एक #NUM त्रुटि लौटाएगा। आप इस त्रुटि को IFERROR फ़ंक्शन के साथ संभाल सकते हैं, या कॉलम H में संख्या मिलान संख्या से अधिक होने पर मिलानों और गर्भपात प्रसंस्करण में तर्क जोड़कर कर सकते हैं। यहाँ उदाहरण एक दृष्टिकोण को दर्शाता है।
कई मापदंड
कई मानदंडों को जोड़ने के लिए, आप बूलियन तर्क का उपयोग करते हैं, जैसा कि इस उदाहरण में बताया गया है।
