Get index of second match in Google Sheets

Question

In a sheet made of names and scores, I am trying to build a sheet to display the name of the people with the best scores. To do so:

• I sort the scores
• I get the index of the biggest value
• I offset the names list with the given index

When I want to get the second biggest value, I only have to get the index of the second biggest value on step 2.

There is a problem if two values are tied for the biggest, as MATCH() will always give me the index of the first value found.

I thought of determining the index of the biggest value, then excluding this index from the range used to determine the second biggest value, but I could not achieve it as the range lengths may be different.

I also thought of using a function or script that returns the Nth index that meets a criteria from a range, but I did not find anything to do so.

Image

Here is an example spreadsheet https://docs.google.com/spreadsheets/d/1RrUpAjbMBze9L5OqxdyEWBnYXq98LtohdgROF8s68FI/edit?usp=sharing

Answer 1

One way is to add a column number and sort on that as well as on the score, then take the second element in the list:

=ArrayFormula(index(sort(transpose({B1:F3;column(B1:F3)}),3,false,4,true),2,1))

Note that the headers (players' names) are sorted along with their scores.

EDIT

Actually Sort in GS is a stable sort (in other words, according to the documentation 'range is sorted only by the specified columns, other columns are returned in the order they originally appear') so this is sufficient:

=ArrayFormula(index(sort(transpose(B1:F3),3,false),2,1))