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
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
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.
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
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))
Last edited by Fotis1991; 06-06-2013 at 04:19 AM. Reason: Edit
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
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?
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.....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?
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.
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
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))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks