0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Wouter Groeneweg
  • 43
  • 1
  • 8

2 Answers2

3

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))
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 1
    INDEX and MAX are named the same in Dutch? Learned something new today. – BigBen Nov 26 '19 at 15:57
  • 1
    Cool approach, I like the combination of MAX and INDEX, something new for me to learn. – Justyna MK Nov 26 '19 at 16:05
  • 1
    @JustynaMK, your approach isn't much different (hence the upvote) – JvdV Nov 26 '19 at 16:06
  • 2
    @JustynaMK and JvdV thank you for your help. It will really help me out – Wouter Groeneweg Nov 26 '19 at 17:04
  • @woutergroeneweg, glad it helped. If this has answered than pls upvote/accept the answer but clicking the checkmark on the left to return the favour (upvoting can be done to any post you found helpful) – JvdV Nov 26 '19 at 17:26
  • @JvdV It doesn't work with A:A for example, is there a simple solution for this? I use it now as A2:A6000 for instance – Wouter Groeneweg Nov 27 '19 at 08:41
  • You really don't want to use whole column references @WouterGroeneweg. Instead you can try: `A2:INDEX(A:A,COUNTA(A:A))` and do the same for the other ranges. – JvdV Nov 27 '19 at 08:44
  • @JvdV That is a new combination it did not know before. The code works only if the highest number has not already been 'seen' in the row . I set your formule in row E and drag it down but it's is not working. Do you know why it's not working? `=INDEX(D2:INDEX(D:D;AANTALARG(D:D));VERGELIJKEN(1;INDEX((A2:INDEX(A:A;AANTALARG(A:A))=A2)*(B2:INDEX(B:B;AANTALARG(B:B))=B2)*(C2:INDEX(C:C;AANTALARG(C:C))=MAX(INDEX((A2:INDEX(A:A;AANTALARG(A:A))=A2)*(B2:INDEX(B:B;AANTALARG(B:B))=B2)*(C2:INDEX(C:C;AANTALARG(C:C)));))););0))` – Wouter Groeneweg Nov 27 '19 at 09:48
  • @WouterGroeneweg, in the sample data above: the formula would be: `=INDEX(D:D;VERGELIJKEN(1;INDEX((A2:INDEX(A:A;AANTALARG(A:A))=G1)*(B2:INDEX(B:B;AANTALARG(B:B))=G2)*(C2:INDEX(C:C;AANTALARG(C:C))=MAX(INDEX((A2:INDEX(A:A;AANTALARG(A:A))=G1)*(B2:INDEX(B:B;AANTALARG(B:B))=G2)*(C2:INDEX(C:C;AANTALARG(C:C)));))););0)+1)`. We assume there are no empty cells in your matrix (or the formula will return false info) – JvdV Nov 27 '19 at 10:22
1

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

enter image description here

Justyna MK
  • 3,523
  • 3
  • 11
  • 25