Forum Discussion
Large Function
In column "A" is a list of athletes' names. In column "B" is the number of goals they've scored.
Step 1: I used the "LARGE" function [=LARGE(D2:D174,1)] at the bottom of column "B" to determine the 1st, 2nd, 3rd and 4th larges values in columb "B".
Step 2: I now need a function that associates the athlete's name in column "A" corresponding to the 1st, 2nd, 3rd and 4th largest values from column "B".
I don't know if this is combining two functions or if there is a single function to do the second step.
2 Replies
Hi David,
assuming you have a list of names in A2:A101, the score in B2:B101 and you have no instances of the same score shared between the athletes, you can use the following formula:
=INDEX($A$2:$A$101,MATCH(LARGE($B$2:$B$101,ROW(1:1)),$B$2:$B$101,0))
Please enter the formula, then drag down to increment ROW(1:1) to ROW(2:2), Row(3:3) and Row(4:4) for the 2nd, 3rd and 4th lines respectively.
If you have instances of score shared between athletes, the formula above will return only first name match. You can solve this by finding an nth match of the score. Assuming you have your score in F2:F5, and you wish to place your ranked list of names into E2:E5, your formula in E2 would be
{=INDEX($A$2:$A$101,SMALL(IF($B$2:$B$101=F2,ROW($B$2:$B$101)-ROW(INDEX($B$2:$B$101,1,1))+1),COUNTIFS(F$2:F2,F2)))}
This formula needs to be array entered in cell E2 (copy and paste the formula above, excluding {}, press F2, then Ctrl+Shift+Enter). After this, copy the array entered formula into the cells E3, E4 and E5.
Please see attached the example file for your reference
Yury
Thank you very much Yury!! That worked perfectly.
