Forum Discussion
Solved
INDEX MATCH + LARGE
Hello!
I been looking for a couple of hours for this solution and I came across with lots of people having the same issue as me but their formulas was way complicate for me to understand.
I have my list with names and then each of them has a score A2:A13 for the names B2:B13 for the scores.
then I have another list with everyone organized by their Score.
The problem I have is that I don't want to see duplicate names, I understand that match don't work for this. but still cant figure it out how.
i used this to get the scores in order
=LARGE(B2:B13,1)
=LARGE(B2:B13,2)
etc...
to get the names in order i used this on the first one
=INDEX($A$2:$A$13,MATCH(MAX(B2:B13),B2:B13,0))
and for the second to the 12th name
=INDEX(A2:A13,MATCH(E3,B2:B13,0))
=INDEX(A2:A13,MATCH(E4,B2:B13,0))
etc...
any idea how to make it to no have the duplicate names?
Thank you!
alejoalexo Since you tag your question with Excel for Web, why not look into more modern functions at your disposal?
=SORT(A2:B13,2,-1)
A2:B13 contains the Names and Scores
SORT( -----, 2, -1) sorts the array by the 2nd column (Score) in descending order (i.e -1)
=LET(range,A1:B14,SORT(HSTACK(UNIQUE(TAKE(range,,1)),BYROW(UNIQUE(TAKE(range,,1)),LAMBDA(row,MAX(IF(TAKE(range,,1)=row,TAKE(range,,2)))))),2,-1))An alternative could be this formula.
alejoalexo Since you tag your question with Excel for Web, why not look into more modern functions at your disposal?
=SORT(A2:B13,2,-1)
A2:B13 contains the Names and Scores
SORT( -----, 2, -1) sorts the array by the 2nd column (Score) in descending order (i.e -1)
- Thank you so much! funny how simple it was, once again thanks!
