Fórmula genérica
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Resumen
Para obtener el nombre del enésimo valor más grande con criterios, puede usar INDICE y COINCIDIR, la función GRANDE y un filtro creado con la función SI. En el ejemplo que se muestra, la fórmula en la celda G5, copiada, es:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
donde nombre (B5: B16), grupo (C5: C16) y puntuación (D5: D16) son rangos de nombres. La fórmula devuelve el nombre asociado con el primer, segundo y tercer valor más alto en el Grupo A.
Nota: Esta es una fórmula de matriz que debe ingresarse con control + shift + enter, excepto en Excel 365.
Explicación
La función LARGE es una manera fácil de obtener el enésimo valor más grande en un rango:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
En este ejemplo, podemos usar la función LARGE para obtener una puntuación más alta, luego usar la puntuación como una "clave" para recuperar el nombre asociado con INDEX y MATCH. Observe que estamos recogiendo los valores de n del rango F5: F7, para obtener el 1º, 2º y 3º puntaje más alto.
Sin embargo, el giro en este caso es que debemos distinguir entre las puntuaciones del grupo A y del grupo B. En otras palabras, debemos aplicar criterios. Hacemos esto con la función SI, que se utiliza para "filtrar" valores antes de que se evalúen con LARGE. Como ejemplo genérico, para obtener el valor más grande (es decir, el primer valor) en rango2 donde rango 1 = "A", puede usar una fórmula como esta:
LARGE(IF(range="A",range2),1)
Nota: usar IF de esta manera hace que esta sea una fórmula de matriz.
Trabajando de adentro hacia afuera, el primer paso es obtener el "primer" valor más grande en los datos asociados con el Grupo A con la función GRANDE:
LARGE(IF(group="A",score),F5)
En este caso, el valor en F5 es 1, por lo que estamos solicitando la máxima puntuación en el Grupo A. Cuando se evalúa la función SI, prueba cada valor en el grupo de rango nombrado . La puntuación del rango con nombre se proporciona para value_if_true. Esto genera una nueva matriz, que se devuelve directamente a la función LARGE:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Observe que los únicos puntajes que sobreviven al filtro son del Grupo A. LARGE luego devuelve el puntaje restante más alto, 93, directamente a la función COINCIDIR como un valor de búsqueda. Ahora podemos simplificar la fórmula para:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Ahora podemos ver que la función COINCIDIR está configurada usando la misma matriz filtrada que vimos arriba. La función SI nuevamente filtra los valores no deseados, y la parte COINCIDIR de la fórmula se resuelve en:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Dado que 93 aparece en la tercera posición, COINCIDIR devuelve 3 directamente a la función ÍNDICE:
=INDEX(name,3) // Hannah
Finalmente, la función INDICE devuelve el nombre en la tercera fila, "Hannah".
Con XLOOKUP
La función XLOOKUP también se puede utilizar para resolver este problema, utilizando el mismo enfoque explicado anteriormente:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Como se indicó anteriormente, LARGE está configurado para funcionar con una matriz filtrada por IF y devuelve un resultado de 93 a XLOOKUP como valor de búsqueda:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
La matriz de búsqueda también se crea utilizando IF como un filtro en las puntuaciones del Grupo A. Con la matriz de retorno proporcionada como nombre (B5: B16). XLOOKUP devuelve "Hannah" como resultado final.
Notas
- Para obtener el nombre del enésimo valor con criterios (es decir, limitar los resultados al grupo A o B), deberá ampliar la fórmula para utilizar una lógica adicional.
- En Excel 365, la función FILTRO es una mejor manera de enumerar los resultados superiores o inferiores de forma dinámica. Este enfoque manejará automáticamente las ataduras.
