Fórmula genérica
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Resumo
Para obter o nome do enésimo maior valor com critérios, você pode usar INDEX e MATCH, a função LARGE e um filtro criado com a função IF. No exemplo mostrado, a fórmula na célula G5, copiada, é:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
onde nome (B5: B16), grupo (C5: C16) e pontuação (D5: D16) são intervalos nomeados. A fórmula retorna o nome associado ao primeiro, segundo e terceiro valores mais altos do Grupo A.
Observação: esta é uma fórmula de matriz que deve ser inserida com control + shift + enter, exceto no Excel 365.
Explicação
A função LARGE é uma maneira fácil de obter o enésimo maior valor em um intervalo:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Neste exemplo, podemos usar a função LARGE para obter a pontuação mais alta e, em seguida, usar a pontuação como uma "chave" para recuperar o nome associado a INDEX e MATCH. Observe que estamos pegando os valores para n no intervalo F5: F7, a fim de obter a 1ª, 2ª e 3ª maiores pontuações.
A diferença, entretanto, neste caso, é que precisamos distinguir entre as pontuações no grupo A e no grupo B. Em outras palavras, precisamos aplicar critérios. Fazemos isso com a função IF, que é usada para "filtrar" valores antes de serem avaliados com LARGE. Como um exemplo genérico, para obter o maior valor (ou seja, primeiro valor) no intervalo 2, onde intervalo 1 = "A", você pode usar uma fórmula como esta:
LARGE(IF(range="A",range2),1)
Nota: usar IF desta forma torna esta uma fórmula de matriz.
Trabalhando de dentro para fora, a primeira etapa é obter o "1º" maior valor nos dados associados ao Grupo A com a função LARGE:
LARGE(IF(group="A",score),F5)
Nesse caso, o valor em F5 é 1, portanto, estamos pedindo a pontuação máxima do Grupo A. Quando a função IF é avaliada, ela testa cada valor no grupo de intervalo nomeado . A pontuação do intervalo nomeado é fornecida para value_if_true. Isso gera uma nova matriz, que é retornada diretamente para a função LARGE:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Observe que as únicas pontuações que sobrevivem ao filtro são do Grupo A. LARGE, em seguida, retorna a pontuação restante mais alta, 93, diretamente para a função MATCH como um valor de pesquisa. Agora podemos simplificar a fórmula para:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Agora podemos ver que a função MATCH está configurada usando o mesmo array filtrado que vimos acima. A função IF filtra novamente os valores indesejados, e a parte CORRESPONDÊNCIA da fórmula resolve para:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Como 93 aparece na 3ª posição, MATCH retorna 3 diretamente para a função INDEX:
=INDEX(name,3) // Hannah
Por fim, a função INDEX retorna o nome na terceira linha, "Hannah".
Com XLOOKUP
A função XLOOKUP também pode ser usada para resolver este problema, usando a mesma abordagem explicada acima:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Como acima, LARGE está configurado para funcionar com uma matriz filtrada por IF e retorna um resultado de 93 para XLOOKUP como o valor de pesquisa:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
A matriz de pesquisa também é criada usando IF como um filtro nas pontuações do Grupo A. Com a matriz de retorno fornecida como nome (B5: B16). XLOOKUP retorna "Hannah" como resultado final.
Notas
- Para obter o nome do enésimo valor com critérios, (ou seja, limitar os resultados ao grupo A ou B), você precisará estender a fórmula para usar lógica adicional.
- No Excel 365, a função FILTRO é a melhor maneira de listar os resultados superiores ou inferiores dinamicamente. Esta abordagem tratará automaticamente os empates.
