Index Match multiple criteria

[Horst_See]

Hello
I want to match a value with 2 citeria, can you please help me on the following issue. attached excel file shows me an error on the formula
Thanks for the help in advanceConsumption trend_v2.xls

[Glenn Kennedy]

Your formula was an array formula. They're a little different from ordinary formulas in that they MUST be 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 the outside of your formula. If you do not CTRL+SHIFT+ENTER you will get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

There was also an error with thre placing of the ,0 exact match. Fixed...

[jayajaya_4]

what is the expectect result(ans)??

Regards,
Jaya

[Glenn Kennedy]

However, here's a better (faster) non-array version of the same. Jus set with ENTER.

=INDEX($E$9:$E$24,MATCH(1,INDEX(($A$9:$A$24=I9)*($C$9:$C$24=J9),0),0))

[Glenn Kennedy]

However, here's a better (faster) non-array version of the same. Jus set with ENTER.

=INDEX($E$9:$E$24,MATCH(1,INDEX(($A$9:$A$24=I9)*($C$9:$C$24=J9),0),0))

[Ali Kırksekiz]

Change your formula as the following

For N9 cell

Please Login or Register  to view this content.

This in an array formula and so must be confirmed with CTRL+SHIFT+ENTER and not just ENTER than pull down

[jayajaya_4]

u can also use =SUMPRODUCT((A9=$I$9:$I$21)*(C9=$J$9:$J$21)*($E$9:$E$21))