Índice do N'ésimo maior valor em um intervalo

Question

Eu tenho um intervalo de valores que podem conter duplicatas. Parece algo assim:

    A B C D
  ----------
1  23 1 
2  12 2
3  23 3
4  36 4
5  19 5

Eu quero mostrar os 3 maiores valores, mas eu quero mostrar o valor correspondente de B, não o valor em A. Eu posso encontrar os 3 maiores valores de A usando

=LARGE(A1:A5,1)   # returns 36, I need it to return 4
=LARGE(A1:A5,2)   # returns 23, I need it to return 1
=LARGE(A1:A5,3)   # returns 23, I need it to return 3

Existe alguma maneira de fazer isso? Eu considerei algo baseado na função RANK, mas RANK retorna a mesma classificação para itens iguais, então não tenho certeza do que fazer.

    

Answer 1

Em D2:D4 use suas fórmulas de 3 LARGE para obter 36 , 23 , 23 e, em seguida, em E2, use essa "fórmula de matriz" para obter os valores correspondentes de B1: B5, enquanto contabiliza possíveis duplicatas:

=INDEX(B$1:B$5,SMALL(IF(A$1:A$5=D2,ROW(A$1:A$5)-ROW(A$1)+1),COUNTIF(D$2:D2,D2)))

confirmado com CTRL + SHIFT + ENTER

    

Answer 2

Que tal usar um filtro? Você pode classificar de Maior a menor (a correspondência entre os elementos A e B é mantida) e depois ler facilmente os vaules das três primeiras células da coluna B