Fórmula genérica
=FILTER(list1,COUNTIF(list2,list1))
Resumen
Para filtrar datos para extraer valores coincidentes en dos listas, puede usar la función FILTRO y la función CONTAR.SI o CONTAR.SI. En el ejemplo que se muestra, la fórmula en F5 es:
=FILTER(list1,COUNTIF(list2,list1))
donde lista1 (B5: B16) y lista2 (D5: D14) son rangos con nombre. El resultado devuelto por FILTER incluye solo los valores en list1 que aparecen en list2 .
Nota: FILTRO es una nueva función de matriz dinámica en Excel 365.
Explicación
Esta fórmula se basa en la función FILTRO para recuperar datos en función de una prueba lógica construida con la función CONTAR.SI:
=FILTER(list1,COUNTIF(list2,list1))
Trabajando de adentro hacia afuera, la función CONTAR.SI se usa para crear el filtro real:
COUNTIF(list2,list1)
Observe que estamos usando list2 como argumento de rango y list1 como argumento de criterios. En otras palabras, le estamos pidiendo a COUNTIF que cuente todos los valores en list1 que aparecen en list2. Debido a que estamos dando COUNTIF múltiples valores para los criterios, obtenemos una matriz con múltiples resultados:
(1;1;0;1;0;1;0;0;1;0;1;1)
Tenga en cuenta que la matriz contiene 12 recuentos, uno para cada valor en list1 . Un valor cero indica un valor en list1 que no se encuentra en list2 . Cualquier otro número positivo indica un valor en list1 que se encuentra en list2 . Esta matriz se devuelve directamente a la función FILTRO como argumento de inclusión:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
La función de filtro usa la matriz como filtro. Se elimina cualquier valor en list1 asociado con un cero, mientras que cualquier valor asociado con un número positivo sobrevive.
El resultado es una matriz de 7 valores coincidentes que se extienden al rango F5: F11. Si los datos cambian, FILTER volverá a calcular y devolverá una nueva lista de valores coincidentes basada en los nuevos datos.
Valores no coincidentes
Para extraer valores que no coinciden de list1 (es decir, valores en list1 que no aparecen en list2 ), puede agregar la función NO a la fórmula de esta manera:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
La función NO invierte efectivamente el resultado de CONTAR.SI: cualquier número distinto de cero se vuelve FALSO y cualquier valor cero se convierte en VERDADERO. El resultado es una lista de los valores en list1 que no están presentes en list2 .
Con INDICE
Es posible crear una fórmula para extraer valores coincidentes sin la función FILTRO, pero la fórmula es más compleja. Una opción es usar la función INDICE en una fórmula como esta:
La fórmula en G5, copiada es:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter, excepto en Excel 365.
El núcleo de esta fórmula es la función INDICE, que recibe list1 como argumento de matriz. La mayor parte de la fórmula restante simplemente calcula el número de fila que se utilizará para los valores coincidentes. Esta expresión genera una lista de números de fila relativos:
ROW(list1)-ROW(INDEX(list1,1,1))+1
que devuelve una matriz de 12 números que representan las filas en list1 :
(1;2;3;4;5;6;7;8;9;10;11;12)
Estos se filtran con la función SI y la misma lógica utilizada anteriormente en FILTRO, basada en la función CONTAR.SI:
COUNTIF(list2,list1) // find matching values
La matriz resultante se ve así:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Esta matriz se envía directamente a la función PEQUEÑA, que se utiliza para buscar el siguiente número de fila coincidente a medida que la fórmula se copia en la columna. El valor k para PEQUEÑO (piense en nth) se calcula con un rango de expansión:
ROWS($G$5:G5) // incrementing value for k
La función IFERROR se utiliza para atrapar errores que ocurren cuando la fórmula se copia y se queda sin valores coincidentes. Para otro ejemplo de esta idea, vea esta fórmula.
