Index Match - multiple criteria & multiple matches

**suwandy** · 12-03-2013, 12:43 AM

Hi, I would like to extract Bill cost from "rule" sheet and insert it to "data" sheet using index-match functions. My problem are multiple criteria and multiple matches.

The criteria are Column: Cost type, Power, Penalty Category (The logic goes like AND function).
And i want it to extract "Bills" value from "rule" sheet.

There are multiple matches in all columns (Cost type, Power, Penalty Category) which cause wrong extraction.

How do i solve this problem with index-match function? or, any other function to solve this problem?

**benishiryo** · 12-03-2013, 12:51 AM

welcome to the forum, suwandy. if the combination of the 3 columns are unique, try:
=SUMIFS(Rule!D:D,Rule!A:A,A5,Rule!B:B,B5,Rule!C:C,C5)

that will be faster. if you wish to use INDEX & MATCH, you have to use this array formula. calculates slower:
=IFERROR(INDEX(Rule!$D$2:$D$21,MATCH(A5&B5&C5,Rule!$A$2:$A$21&Rule!$B$2:$B$21&Rule!$C$2:$C$21,0)),0)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

**suwandy** · 12-03-2013, 01:06 AM

Thank you benishiryo for your quick reply!

That sumifs worked nicely! i couldn't figure out that sumifs function could be use this way.
is it because of the criteria combination only direct to single output?