will autopopulate the group depending on the top 3 results

**Elainefish** · 06-06-2013, 03:44 AM

fx:

A 12
B 150
C 865
D 213
E 956
F 127
G 453
H 422
I 4523
J 456
K 451
TOP1 ? -> I 4523
TOP2 ? -> E 956
TOP3 ? -> C 865

QUESTION_CATEGORY FOR TOP3.xlsx

**benishiryo** · 06-06-2013, 03:48 AM

hi Elainefish, try this array formula:
=INDEX($B$1:$B$11,SMALL(IF($C$1:$C$11=C12,ROW($B$1:$B$11)-ROW($B$1)+1),COUNTIF(C$12:C12,C12)))
...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. 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.

**Fotis1991** · 06-06-2013, 03:55 AM

And a regular formula as another option.

=INDEX($B$1:$B$11,MATCH(LARGE($C$1:$C$11,ROW(A1)),$C$1:$C$11,0))

Edit: Of course this formula can not handle the situation if we have same numbers as largre1, large2 etc....as Ben's ARRAY does, but this also regular formula does it.

=INDEX($B$1:$B$11;MATCH(LARGE(INDEX($C$1:$C$11+(ROWS($C$1:$C$11)-ROW($C$1:$C$11))/10^5;0);ROWS($2:2));INDEX($C$1:$C$11+(ROWS($C$1:$C$11)-ROW($C$1:$C$11))/10^5;0);0))

**Elainefish** · 06-06-2013, 04:31 AM

Thanks for the help... this formula works
I will be using:
=INDEX($B$1:$B$11;MATCH(LARGE(INDEX($C$1:$C$11+(ROWS($C$1:$C$11)-ROW($C$1:$C$11))/10^5;0);ROWS($2:2));INDEX($C$1:$C$11+(ROWS($C$1:$C$11)-ROW($C$1:$C$11))/10^5;0);0))

but my question is
why ROWS($2:2)? because when i drag down the formula it changes to ROWS($2:3)) - ROWS($2:4))
should i lock the formula?

Originally Posted by Fotis1991

And a regular formula as another option.

=INDEX($B$1:$B$11,MATCH(LARGE($C$1:$C$11,ROW(A1)),$C$1:$C$11,0))

Edit: Of course this formula can not handle the situation if we have same numbers as largre1, large2 etc....as Ben's ARRAY does, but this also regular formula does it.

=INDEX($B$1:$B$11;MATCH(LARGE(INDEX($C$1:$C$11+(ROWS($C$1:$C$11)-ROW($C$1:$C$11))/10^5;0);ROWS($2:2));INDEX($C$1:$C$11+(ROWS($C$1:$C$11)-ROW($C$1:$C$11))/10^5;0);0))

**Fotis1991** · 06-06-2013, 04:43 AM

..but my question is
why ROWS($2:2)? because when i drag down the formula it changes to ROWS($2:3)) - ROWS($2:4))
should i lock the formula?

As you see the first part of the formula is locked. ROWS($2:2) .In this case you get the Name that have the bigest value. As you drag down ROWS($2:3) you get the second Name..ROWS($2:4)) the third and so on...

This part of the formula "says" to the other parts of the formula which result we need(1st,2nd,3d.....)

You will get the same result if you use ROWS($1:1) OR ROWS($3:3)....or even ROW($B1)...LIKE THIS...

=INDEX($B$1:$B$11;MATCH(LARGE(INDEX($C$1:$C$11+(ROWS($C$1:$C$11)-ROW($C$1:$C$11))/10^5;0);ROW($B1));INDEX($C$1:$C$11+(ROWS($C$1:$C$11)-ROW($C$1:$C$11))/10^5;0);0))

Hope that helps.

**Elainefish** · 06-09-2013, 07:16 AM

Thanks for answering my questions but I do have follow up question on this...
I added another column to check the top 3... I hope you can take a look at this followup question_top3.xlsx

Originally Posted by Fotis1991

As you see the first part of the formula is locked. ROWS($2:2) .In this case you get the Name that have the bigest value. As you drag down ROWS($2:3) you get the second Name..ROWS($2:4)) the third and so on...

This part of the formula "says" to the other parts of the formula which result we need(1st,2nd,3d.....)

You will get the same result if you use ROWS($1:1) OR ROWS($3:3)....or even ROW($B1)...LIKE THIS...

=INDEX($B$1:$B$11;MATCH(LARGE(INDEX($C$1:$C$11+(ROWS($C$1:$C$11)-ROW($C$1:$C$11))/10^5;0);ROW($B1));INDEX($C$1:$C$11+(ROWS($C$1:$C$11)-ROW($C$1:$C$11))/10^5;0);0))

Hope that helps.

**Fotis1991** · 06-10-2013, 02:31 AM

So your new request is to get the top 3 products. Correct?

If so...then.

=INDEX($B$1:$B$22;MATCH(LARGE(INDEX($D$1:$D$22+(ROWS($D$1:$D$22)-ROW($D$1:$D$22))/10^5;0);ROWS($2:2));INDEX($D$1:$D$22+(ROWS($D$1:$D$22)-ROW($D$1:$D$22))/10^5;0);0))