The formula I have in my cells on the second sheet goes like this:
=INDEX($Scores.$B$3:$B$40, MATCH(LARGE($Scores.D$3:D$30,$A4), $Scores.D$3:D$30) )
The range $Scores.$B$3:$B$40 contains the 3-letter country codes. The range $Scores.D$3:D$30 contains some scores given to the country in the same row by the country written at the top of the column in an international competition. I want the formula to output the country who was ranked the nth-highest by the country written at the top of the column, with the ranking number being specified in $A4.
The problem is, it only outputs correctly if I sort rows 3-30 in the $Scores sheet ascending according to the score in column D.
How do I make it so that it doesn’t require any sorting?
Eurovision-2023-rankings.ods (30.7 KB)
Find this function all over the sheet called Rankings in this file.