Fórmula genérica
=SUMPRODUCT(--(A1=things))>0
Resumo
Se quiser testar uma célula para ver se ela é igual a uma de várias coisas, você pode fazer isso com uma fórmula que usa a função SUMPRODUCT.
Contexto
Digamos que você tenha uma lista de strings de texto no intervalo B5: B11 e queira testar cada célula em relação a outra lista de itens no intervalo E5: E9. Em outras palavras, para cada célula em B5: B11, você quer saber: essa célula é igual a alguma das coisas em E5: E9?
Você poderia começar a construir uma grande fórmula baseada em instruções IF aninhadas, mas uma fórmula de matriz baseada em SUMPRODUCT é uma abordagem mais simples e limpa.
Solução
A solução é criar uma fórmula que irá testar vários valores e retornar uma lista de valores TRUE / FALSE. Assim que tivermos isso, podemos processar essa lista (uma matriz, na verdade) com SUMPRODUCT.
A fórmula que estamos usando é parecida com esta:
=SUMPRODUCT(--(B5=things))>0
Explicação
A chave é este snippet:
--(B5=things)
que simplesmente compara o valor em B5 com cada valor no intervalo nomeado "coisas". Como estamos comparando B5 a uma matriz (ou seja, o intervalo nomeado "coisas", E5: E11), o resultado será uma matriz de valores VERDADEIRO / FALSO como este:
(VERDADEIRO; FALSO; FALSO; FALSO; FALSO)
Se tivermos apenas um TRUE na matriz, sabemos que B5 é igual a pelo menos uma coisa na lista, então, para forçar os valores TRUE / FALSE para 1s e 0s, usamos um duplo negativo (-, também chamado de duplo unário). Após esta coerção, temos o seguinte:
(1; 0; 0; 0; 0)
Agora processamos o resultado com SUMPRODUCT, que irá somar os elementos do array. Se obtivermos qualquer resultado diferente de zero, temos pelo menos uma correspondência, então usamos> 1 para forçar um resultado final VERDADEIRO ou FALSO.
Com uma lista codificada
Não há exigência de que você use um intervalo para sua lista de itens. Se você está procurando apenas um pequeno número de coisas, pode usar uma lista no formato de matriz, que é chamada de constante de matriz. Por exemplo, se você está procurando apenas as cores vermelho, azul e verde, pode usar ("vermelho", "azul", "verde") assim:
--(B5=("red","blue","green"))
Lidando com espaços extras
Se as células que você está testando contiverem algum espaço extra, elas não corresponderão corretamente. Para retirar todo o espaço extra, você pode modificar a fórmula para usar a função TRIM da seguinte maneira:
=SUMPRODUCT(--(TRIM(A1)=things))>0
