CODEWITHSUNDEEP
google-sheetsindexingmatch
Neral
Neral

Reputation: 3

Get index of second match in Google Sheets

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:

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

Upvotes: 0

Views: 2790

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34230

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

enter image description here

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

Upvotes: 1

Related Questions