Fórmula genérica
=FILTER(list1,COUNTIF(list2,list1))
Resumo
Para comparar duas listas e extrair valores comuns, você pode usar uma fórmula baseada nas funções FILTER e CONT.SE. No exemplo mostrado, a fórmula em F5 é:
=FILTER(list1,COUNTIF(list2,list1))
onde lista1 (B5: B15) e lista2 (D5: D13) são intervalos nomeados. O resultado, valores que aparecem em ambas as listas, atinge o intervalo F5: F11.
Explicação
A função FILTER aceita uma matriz de valores e um argumento "incluir" que filtra a matriz com base em uma expressão ou valor lógico.
Nesse caso, a matriz é fornecida como o intervalo nomeado "lista1", que contém todos os valores em B5: B15. O argumento include é entregue pela função CONT.SE, que está aninhada em FILTER:
=FILTER(list1,COUNTIF(list2,list1))
COUNTIF é configurado com lista2 como intervalo e lista1 como critérios . Como fornecemos CONT.SE onze valores de critérios, CONT.SE retorna onze resultados em uma matriz como esta:
(1;1;0;1;0;1;0;1;0;1;1)
Observe que os 1s correspondem aos itens na lista2 que aparecem na lista1.
Esta matriz é entregue diretamente à função FILTER como o argumento "incluir":
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
A função FILTER filtra a lista1 usando os valores fornecidos por CONT.SE. Os valores associados a zero são removidos; outros valores são preservados.
O resultado final é uma matriz de valores que existem em ambas as listas, que se estende até o intervalo F5: F11.
Lógica estendida
Na fórmula acima, usamos os resultados brutos de CONT.SE como filtro. Isso funciona porque o Excel avalia qualquer valor diferente de zero como TRUE e zero como FALSE. Se CONT.SE retornar uma contagem maior que 1, o filtro ainda funcionará corretamente.
Para forçar resultados VERDADEIRO e FALSO explicitamente, você pode usar "> 0" assim:
=FILTER(list1,COUNTIF(list2,list1)>0)
Remover duplicatas ou classificar
Para remover duplicatas, basta aninhar a fórmula dentro da função UNIQUE:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Para classificar os resultados, aninhe na função SORT:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Valores de lista ausentes na lista2
Para gerar valores na lista1 ausentes na lista2, você pode inverter a lógica desta forma:
=FILTER(list1,COUNTIF(list2,list1)=0)
