Sorting a list in Excel 2019

[hidegandras]

I have a list of names and a score for each person. I need to sort this list in descending order, name of the highest score on the top, lowest on the bottom.

SORT function is not available in Excel 2019, so I need to find a workaround.

I managed to sort the values using the LARGE function, however, as there are two similar values, using VLOOKUP gives the same name for the two 92% scores. The name list should look: Emily, John, Mia, James, Jessica, Mark and Richard.

Function in E3: =LARGE($A$2:$A$9;ROW(A1))

Function in F3: =VLOOKUP(LARGE($A$2:$A$9;1);$A$3:$D$9;3;FALSE)

I also can't use VBA, so I need some sort of function combination that is available in Excel 2019.

[#2]

@hidegandras 

As variant

In E3

=RANK(A3,$A$3:$A$9)+COUNTIF($A$3:$A3,A3)-1

In F3

=INDEX($C$3:$C$9, E3 )

Drag both down

[#3]

@SergeiBaklan 

Yes, this works! Thank you!

[#4]

@hidegandras , you are welcome.

You may combine both in one formula if you don't need to show ranks.

[#5]

@SergeiBaklan 

I don't know what is going on... So if I give a 98% to Richard the entire order gets messed up.

[#6]

Resolved by adding a MATCH function. I am only looking for the top 3, so I just need to match 1, 2 and 3.

=INDEX($C$3:$C$9;MATCH(1;$E$3:$E$9;0))

[#7]

@hidegandras 

Oops, my bad, sorry. You are right, we need to match the rank.

In general

with

=INDEX($C$3:$C$9, MATCH( SMALL($D$3:$D$9, ROW() - ROW($F$2) ), $D$3:$D$9, 0 ) )