Excel - Use of MAXIFS (in excel 2010)

Question

I want to solve a stock problem. I have stock from some articles in a specific store and I want to find out which store in the same country has the highest stock of this specific item.

I use the table below for information input

Column A: Article number

Column B: Country

Column C: Stock

Column D: Store number

For instance:

I would like to know for article 884 in Netherlands, which store has the highest stock. The outcome would be store 1.

I'm not able to use the formulas MAXIFS :(.

Is there a possibility to work around and get the same answer?

Answer 1

Whilst you could do as per my comment using MAX and IF in an array (also see this page for some additional information), you can also try the following:

Formula in G3:

=INDEX(D2:D7,MATCH(1,INDEX((A2:A7=G1)*(B2:B7=G2)*(C2:C7=MAX(INDEX((A2:A7=G1)*(B2:B7=G2)*(C2:C7),))),),0))

Or in Dutch (I assume you are)

=INDEX(D2:D7;VERGELIJKEN(1;INDEX((A2:A7=G1)*(B2:B7=G2)*(C2:C7=MAX(INDEX((A2:A7=G1)*(B2:B7=G2)*(C2:C7);))););0))

Answer 2

I have a feeling there's a much simpler solution to that, but... here it goes...

=INDEX($D$2:$D$7,SUMPRODUCT(MATCH(MAX(--($A$2:$A$7=A10)*--($B$2:$B$7=B10)*$C$2:$C$7),--($A$2:$A$7=A10)*--($B$2:$B$7=B10)*$C$2:$C$7,0)))