Fórmula genérica
=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")
Resumen
Una forma de extraer múltiples coincidencias en Excel es usar INDEX y MATCH con una columna auxiliar que marca los datos coincidentes. Esto evita la complejidad de una fórmula de matriz más avanzada. En el ejemplo que se muestra, la fórmula en H6 es:
=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")
donde ct (G3), datos (B3: E52) y helper (E3: E52) son rangos con nombre.
Explicación
El desafío con las fórmulas de búsqueda que recuperan más de una coincidencia es administrar duplicados (es decir, múltiples coincidencias). Las fórmulas de búsqueda como BUSCARV e INDICE + COINCIDIR pueden encontrar fácilmente la primera coincidencia, pero es mucho más difícil buscar "todas las coincidencias" cuando los criterios encuentran más de una coincidencia.
Esta fórmula se ocupa de este desafío mediante el uso de una columna auxiliar que devuelve un valor numérico que se puede utilizar para extraer fácilmente múltiples coincidencias. La fórmula de la columna de ayuda se ve así:
=SUM(E2,AND(C3=$I$3,D3=$J$3))
La columna auxiliar prueba cada fila de los datos para ver si el Departamento en la columna C coincide con el valor en I3 y el Edificio en la columna D coincide con el valor en J3. Ambas pruebas lógicas deben devolver TRUE para que AND devuelva TRUE.
Para cada fila, el resultado de la función Y se agrega al "valor anterior" en la columna auxiliar para generar un recuento. El efecto práctico de esta fórmula es un contador incremental que solo cambia cuando se encuentra una (nueva) coincidencia. Entonces, el valor permanece igual hasta que se encuentra la siguiente coincidencia. Esto funciona porque los resultados VERDADERO / FALSO devueltos por Y son coaccionados a valores 1/0 como parte de la operación de suma. Los resultados FALSOS no agregan nada y los resultados VERDADEROS suman 1.
De vuelta en el área de extracción, la fórmula de búsqueda para el Nombre en la columna H se ve así:
=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")
Trabajando desde adentro hacia afuera, la parte INDICE + COINCIDIR de la fórmula busca el nombre de la primera coincidencia encontrada, usando el número de fila en la columna G como valor de coincidencia:
INDEX(data,MATCH($G6,helper,0),1)
INDICE recibe las 3 columnas de datos como la matriz (rango llamado "datos"), y MATCH está configurado para coincidir con el número de fila dentro de la columna auxiliar (el rango llamado "ayudante") en el modo de coincidencia exacta (tercer argumento establecido en cero) .
Aquí es donde se hace evidente la inteligencia de la fórmula. La columna de ayuda obviamente contiene duplicados, pero no importa, porque MATCH coincidirá solo con el primer valor. Por diseño, cada "primer valor" corresponde a la fila correcta en la tabla de datos.
Las fórmulas de las columnas I y J son las mismas que las de H, excepto el número de columna, que se incrementa en uno en cada caso.
La instrucción IF que envuelve la fórmula INDEX / MATCH realiza una función simple: verifica cada número de fila en el área de extracción para ver si el número de fila es menor o igual al valor en G3 (rango llamado "ct"), que es el recuento total de todos los registros coincidentes. Si es así, se ejecuta la lógica INDEX / MATCH. Si no, IF genera una cadena vacía ("").
La fórmula en G3 (rango llamado "ct") es simple:
=MAX(helper)
Dado que el valor máximo en la columna auxiliar es el mismo que el recuento total de coincidencias, la función MAX es todo lo que necesitamos.
Nota: el área de extracción debe configurarse manualmente para manejar tantos datos como sea necesario (es decir, 5 filas, 10 filas, 20 filas, etc.). En este ejemplo, está limitado a 5 filas solo para mantener la hoja de trabajo compacta.
Aprendí esta técnica en el libro Control + Shift + Enter de Mike Girvin.
La función FILTRO
Si tiene la versión Dynamic Array de Excel, la función FILTRO es mucho más fácil de extraer todos los datos coincidentes.
