Search code examples
excelexcel-formulamaxifsmaxifs

Excel - Use of MAXIFS (in excel 2010)

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

enter image description here

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?

Solution

  • 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:

    enter image description here

    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))