SOLVED

INDEX MATCH + LARGE

Copper Contributor

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?scores.jpg

Thank you!

3 Replies
best response confirmed by alejoalexo (Copper Contributor)
Solution

@alejoalexo Since you tag your question with Excel for Web, why not look into more modern functions at your disposal?

Riny_van_Eekelen_0-1678700090760.png

 

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

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

sort.JPG 

1 best response

Accepted Solutions
best response confirmed by alejoalexo (Copper Contributor)
Solution

@alejoalexo Since you tag your question with Excel for Web, why not look into more modern functions at your disposal?

Riny_van_Eekelen_0-1678700090760.png

 

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

 

View solution in original post