EXCEL: Extraer valores comunes de dos listas

Para comparar dos listas y extraer valores comunes, puede usar una fórmula basada en las funciones FILTRAR y CONTAR.SI. En el ejemplo que se muestra, la fórmula en F5 es:

=FILTRAR(list1,CONTAR.SI(list2,list1))

donde list1 (B5: B15) y list2 (D5: D13) son rangos con nombre. El resultado, los valores que aparecen en ambas listas, se derrama en el rango F5: F11.

Cómo funciona esta fórmula

La función FILTRAR acepta una matriz de valores y un argumento "incluir" que filtra la matriz en función de una expresión o valor lógico.
En este caso, la matriz se proporciona como el rango con nombre "list1", que contiene todos los valores en B5: B15. El argumento de inclusión es entregado por la función CONTAR.SI, que está anidada dentro de FILTRAR:

=FILTRAR(list1,CONTAR.SI(list2,list1))

CONTAR.SI se configura con list2 como rango y list1 como criterio. Debido a que le damos a CONTAR.SI once valores de criterios, CONTAR.SI 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 la lista2 que aparecen en la lista1.
Esta matriz se entrega directamente a la función FILTRAR como el argumento "incluir":

=FILTRAR(list1,{1;1;0;1;0;1;0;1;0;1;1})

La función FILTRAR filtra list1 utilizando los valores proporcionados por CONTAR.SI. Se eliminan los valores asociados con cero; Se conservan otros valores.
El resultado final es una matriz de valores que existen en ambas listas, que se extiende al rango F5: F11.

Lógica extendida

En la fórmula anterior, utilizamos los resultados sin procesar de CONTAR.SI 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 explícitamente los resultados VERDADERO y FALSO, puede usar "> 0" de esta manera:

=FILTRAR(list1,CONTAR.SI(list2,list1)>0)

Eliminar duplicados u ordenar

Para eliminar duplicados, simplemente anide la fórmula dentro de la función ÚNICA:

=UNICOS(FILTRAR(list1,CONTAR.SI(list2,list1)))

Para ordenar los resultados, anide en la función ORDENAR:

=ORDENAR(UNICOS(FILTRAR(list1,CONTAR.SI(list2,list1))))

Faltan valores de lista de list2

Para generar valores en la lista1 que faltan en la lista2, puede invertir la lógica de esta manera:

=FILTRAR(list1,CONTAR.SI(list2,list1)=0)