Fórmula genérica
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Resumo
Para testar uma célula para uma das muitas strings, enquanto exclui outras, você pode usar uma fórmula baseada nas funções SEARCH, ISNUMBER e SUMPRODUCT. No exemplo mostrado, a fórmula em C5 é:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
onde "incluir" é o intervalo nomeado E5: E9 e "excluir" é o intervalo nomeado G5: G6.
Explicação
Basicamente, essa fórmula usa a função SEARCH para procurar várias strings dentro de uma célula. Dentro do SUMPRODUCT esquerdo, SEARCH procura todas as strings no intervalo nomeado "include".
No SUMPRODUCT correto, SEARCH procura por todas as strings no intervalo nomeado "excluir".
Em ambas as partes da fórmula, SEARCH retorna posições numéricas quando as strings são encontradas e erros quando não são encontradas. A função ISNUMBER converte os números em TRUE e os erros em FALSE, e o negativo duplo converte os valores TRUE FALSE em 1 e 0.
O resultado neste ponto se parece com este:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Então:
=(1>0)*(0=0) =TRUE*TRUE =1
Nota: esta fórmula retorna 1 ou zero, que são tratados como TRUE e FALSE em fórmulas, formatação condicional ou validação de dados.
