Fórmula genérica
=COUNTIF(list,A1)=0
Resumo
Para comparar listas e realçar valores que existem em uma, mas não na outra, você pode aplicar a formatação condicional com uma fórmula baseada na função CONT.SE. Por exemplo, para destacar valores A1: A10 que não existem C1: C10, selecione A1: A10 e crie uma regra de formatação condicional com base nesta fórmula:
=COUNTIF($C$1:$C$10,A1)=0
Nota: com a formatação condicional, é importante inserir a fórmula relativa à "célula ativa" na seleção, que é considerada A1 neste caso.
Explicação
Esta fórmula é avaliada para cada uma das 10 células em A1: D10. A1 mudará para o endereço da célula que está sendo avaliada, enquanto C1: C10 é inserido como um endereço absoluto, portanto, não mudará nada.
A chave para esta fórmula é o = 0 no final, que "inverte" a lógica da fórmula. Para cada valor em A1: A10, CONT.SE retorna o número de vezes que o valor aparece em C1: C10. Contanto que o valor apareça pelo menos uma vez em C1: C10, CONT.SE retornará um número diferente de zero e a fórmula retornará FALSO.
Mas quando um valor não é encontrado em C1: C10, CONT.SE retorna zero e, como 0 = 0, a fórmula retornará VERDADEIRO e a formatação condicional será aplicada.
Intervalos nomeados para sintaxe simples
Se você nomear a lista que está pesquisando (C1: C10 neste caso) com um intervalo nomeado, a fórmula será mais simples de ler e entender:
=COUNTIF(list,A1)=0
Isso funciona porque os intervalos nomeados são automaticamente absolutos.
Versão que diferencia maiúsculas de minúsculas
Se você precisar de uma contagem com distinção entre maiúsculas e minúsculas, pode usar uma fórmula como esta:
=SUMPRODUCT((--EXACT(A1,list)))=0
A função EXACT executa uma avaliação com distinção entre maiúsculas e minúsculas e SUMPRODUCT registra o resultado. Tal como acontece com CONT.SE, esta fórmula retornará quando o resultado for zero. Como o teste diferencia maiúsculas de minúsculas, "apple" aparecerá como ausente, mesmo se "Apple" ou "APPLE" aparecer na segunda lista. Veja esta página para uma explicação mais detalhada.
