EXCEL: Extraer múltiples coincidencias en filas separadas

Para extraer múltiples coincidencias en celdas separadas, en filas separadas, puede usar una fórmula de matriz basada en INDICE y K.ESIMO.MENOR. En el ejemplo que se muestra, la fórmula en E5 es:

{=SI.ERROR(INDICE(names,K.ESIMO.MENOR(SI(groups=E$4,FILA(names)-MIN(FILA(names))+1),FILAS($E$5:E5))),"")}

Esta es una fórmula de matriz y debe ingresarse con Control + Shift + Enter.

Después de ingresar la fórmula en la primera celda, arrástrela hacia abajo y hacia adentro para completar las otras celdas.

Cómo funciona esta fórmula

Nota: esta fórmula utiliza dos rangos con nombre: "nombres" se refiere a C4: C11 y "grupos" se refiere a B4: B11. Estos nombres también se definen en la captura de pantalla anterior.

La esencia de esta fórmula es esta: estamos utilizando la función K.ESIMO.MENOR para obtener un número de fila que corresponde a una "enésima coincidencia". Una vez que tenemos el número de fila, simplemente lo pasamos a la función de función INDICE, que devuelve el valor en esa fila.

El truco es que K.ESIMO.MENOR está trabajando con una matriz que SI construye dinámicamente en este bit:

SI(groups=E$4,FILA(names)-MIN(FILA(names))+1)

Este fragmento prueba los "grupos" de rango con nombre para el valor en E4. Si se encuentra, devuelve un número de fila "normalizado" de una matriz de números de fila creados con esta parte de la fórmula:

FILA(names)-MIN(FILA(names))+1

El resultado es una matriz que contiene números de fila donde hay una coincidencia, y FALSO donde no. La matriz se ve así:

{1; FALSO; FALSO; FALSO; FALSO; 6; FALSO}

Esta matriz entra en K.ESIMO.MENOR. El valor k para K.ESIMO.MENOR (enésimo) proviene de un rango en expansión:

FILAS($E$5:E5)

Cuando se copia la tabla de resultados, el rango se expande, haciendo que k (enésimo) se incremente. La función K.ESIMO.MENOR devuelve cada número de fila coincidente, que se proporciona a la función INDICE como row_num, con el rango de nombres "nombres" como la matriz.

Manejo de errores

Cuando FILAS devuelve un valor para k que no existe, K.ESIMO.MENOR arroja un error #NUM. Esto sucede después de que se hayan producido todos los partidos. Para suprimir el error, usamos SI.ERROR para detectar el error y devolver una cadena vacía ("").