Sorting a list in Excel 2019

Question

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.

sergeibaklan · Answer

hidegandras&nbsp;
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
&nbsp;

hidegandras · Answer

SergeiBaklan&nbsp;&nbsp;Yes, this works! Thank you!

sergeibaklan · Answer

hidegandras&nbsp;, you are welcome.
You may combine both in one formula if you don't need to show ranks.

hidegandras · Answer

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

hidegandras · Answer

Resolved by adding a MATCH function. I am only looking for the top 3, so I just need to match 1, 2 and 3.&nbsp;=INDEX($C$3:$C$9;MATCH(1;$E$3:$E$9;0))&nbsp;

Forum Discussion

Sorting a list in Excel 2019

Share

Resources