INDEX MATCH + LARGE

[alejoalexo]

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!

[#2 · Accepted solution]

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

[#3]

Thank you so much! funny how simple it was, once again thanks!

[#4]

@alejoalexo 

=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.