Recuperar la enésima coincidencia con ÍNDICE

Para encontrar la enésima coincidencia de un valor en un rango y recuperar sus datos correspondientes, puedes utilizar una fórmula basada en las funciones ÍNDICE, FILA, PEQUEÑO y SI.

¿Cómo recuperar la información asociada a la enésima coincidencia de un valor mediante ÍNDICE?

Para obtener la información sobre la 3.ª coincidencia de Usain Bolt en la tabla mostrada anteriormente, combina las funciones FILA y SI para generar una matriz con los números de fila de todas las apariciones de Usain Bolt. A continuación, utiliza la función PEQUEÑO para extraer el número de fila correspondiente a la enésima coincidencia que necesitas. Por último, introduce ese resultado en la función ÍNDICE para recuperar la información asociada en dicha fila.

Sintaxis genérica

=INDEX()return_range,SMALL(IF())lookup_array=lookup_value,ROW()lookup_array-ROW(INDEX())lookup_array,1,1))+1),n))

√ Nota: Esta es una fórmula matricial que requiere pulsar Ctrl+Mayús+Intro.

Para obtener la información sobre la 3.ª coincidencia de Usain Bolt, copie o introduzca las fórmulas siguientes en las celdas G8 y G9 y pulse Ctrl+Mayús+Intropara obtener los resultados:

Año (celda G8)
=ÍNDICE()B6:B12,PEQUEÑO(SI())C6:C12=G5,FILA()C6:C12-FILA(ÍNDICE())C6:C12,1,1))+1),G6))
Tiempo (celda G9)
=ÍNDICE()D6:D12,PEQUEÑO(SI())C6:C12=G5,FILA()C6:C12-FILA(ÍNDICE())C6:C12,1,1))+1),G6))

Explicación de la fórmula

A continuación utilizamos la siguiente fórmula como ejemplo:

=INDEX(B6:B12,)SMALL()IF(C6:C12=G5,)ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6))

• FILA(C6:C12):La función FILA devuelve el número de fila de cada celda del rango C6:C12 en una matriz como esta: {6;7;8;9;10;11;12}.
• FILA(ÍNDICE(C6:C12,1,1)): Las funciones ÍNDICE y FILA devuelven el número de la primera fila del rango de búsqueda C6:C12 en esta hoja de cálculo de Excel, que es 6.
• SI(C6:C12=G5,)FILA(C6:C12)-FILA(ÍNDICE(C6:C12,1,1))+1)=SI(C6:C12=G5,){6;7;8;9;10;11;12}-6+1):La función SI comprueba si cada valor del rango C6:C12 coincide con el valor de la celda G5, Usain Bolt. Si es así, devuelve «su número de fila correspondiente − 6 + 1»; de lo contrario, devuelve FALSO. Por tanto, el resultado es una matriz como esta: {FALSO;2;3;4;FALSO;FALSO;FALSO}.
• PEQUEÑO()SI(C6:C12=G5,)FILA(C6:C12)-FILA(ÍNDICE(C6:C12,1,1))+1),G6)=PEQUEÑO(){FALSO;2;3;4;FALSO;FALSO;FALSO},G6):Este fragmento devuelve el 3.ºvalor más pequeño (indicado en la celda)G6) de la matriz, que es 4.
• ÍNDICE(B6:B12,)PEQUEÑO()SI(C6:C12=G5,)FILA(C6:C12)-FILA(ÍNDICE(C6:C12,1,1))+1),G6)) = ÍNDICE(B6:B12,4)La función ÍNDICE devuelve el 4.º valor del rango de años B6:B12, que es Pekín 2008.

Funciones relacionadas

Fórmulas relacionadas