Fórmula genérica
=FILTER(list1,COUNTIF(list2,list1))
Resumen
Para comparar dos listas y extraer valores comunes, puede usar una fórmula basada en las funciones FILTER y COUNTIF. En el ejemplo que se muestra, la fórmula en F5 es:
=FILTER(list1,COUNTIF(list2,list1))
donde list1 (B5: B15) y list2 (D5: D13) son rangos con nombre. El resultado, valores que aparecen en ambas listas, se derrama en el rango F5: F11.
Explicación
La función FILTRO acepta una matriz de valores y un argumento "incluir" que filtra la matriz basándose en una expresión lógica o valor.
En este caso, la matriz se proporciona como el rango con nombre "lista1", que contiene todos los valores en B5: B15. El argumento de inclusión lo entrega la función CONTAR.SI, que está anidada dentro de FILTRO:
=FILTER(list1,COUNTIF(list2,list1))
CONTAR.SI se configura con lista2 como rango y lista1 como criterio . Debido a que damos a COUNTIF once valores de criterios, COUNTIF devuelve once resultados en una matriz como esta:
(1;1;0;1;0;1;0;1;0;1;1)
Observe que los 1 corresponden a los elementos de list2 que aparecen en list1.
Esta matriz se envía directamente a la función FILTRO como el argumento "incluir":
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
La función FILTRO filtra la lista 1 utilizando los valores proporcionados por CONTAR.SI. Se eliminan los valores asociados con cero; otros valores se conservan.
El resultado final es una matriz de valores que existe en ambas listas, que se derrama en el rango F5: F11.
Lógica extendida
En la fórmula anterior, usamos los resultados sin procesar de COUNTIF como filtro. Esto funciona porque Excel evalúa cualquier valor distinto de cero como VERDADERO y cero como FALSO. Si CONTAR.SI devuelve un recuento mayor que 1, el filtro seguirá funcionando correctamente.
Para forzar resultados VERDADEROS y FALSOS explícitamente, puede usar "> 0" así:
=FILTER(list1,COUNTIF(list2,list1)>0)
Eliminar duplicados u ordenar
Para eliminar duplicados, simplemente anide la fórmula dentro de la función ÚNICA:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Para ordenar los resultados, anide en la función CLASIFICAR:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Lista de valores que faltan en list2
Para generar valores en list1 que faltan en list2, puede invertir la lógica de esta manera:
=FILTER(list1,COUNTIF(list2,list1)=0)
