Fórmula genérica
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Resumo
Para testar uma célula para ver se ela contém uma das muitas strings, você pode usar uma fórmula baseada nas funções SEARCH, ISNUMBER e SUMPRODUCT. A fórmula em C5, copiada, é:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
onde coisas é o intervalo nomeado E5: E9.
Explicação
Queremos testar cada célula B5: B11 para ver se ele contém qualquer uma das strings no intervalo nomeado coisas (E5: E9). A fórmula que estamos usando em C5, copiada, é:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Esta fórmula é baseada em uma fórmula (explicada aqui) que verifica uma célula para uma única substring. Se a célula contiver a substring, a fórmula retornará TRUE. Caso contrário, a fórmula retorna FALSO:
ISNUMBER(SEARCH(things,B5))
No entanto, neste caso, fornecemos a SEARCH uma lista de strings. Como existem 5 strings nas coisas , SEARCH retorna 5 resultados em uma matriz como esta:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Quando SEARCH encontra uma string, ele retorna a posição dessa string. Se SEARCH não encontrar uma string, ele retornará um #VALUE! erro. Como "amarelo" aparece como a primeira palavra em B5, vemos um 1. Como as outras strings não foram encontradas, os outros 4 itens são erros.
Esta matriz é retornada diretamente para a função ISNUMBER. ISNUMBER então retorna uma matriz de valores TRUE / FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Se tivermos pelo menos um TRUE no array, sabemos que uma célula contém pelo menos uma das strings que estamos procurando. A maneira mais fácil de verificar TRUE é somar todos os valores. Podemos fazer isso com SUMPRODUCT, mas primeiro precisamos forçar os valores TRUE / FALSE para 1s e 0s com um duplo negativo (-) como este:
--ISNUMBER(SEARCH(things,B5))
Isso produz uma nova matriz contendo apenas 1s e 0s:
(1;0;0;0;0)
entregue diretamente para SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
Com apenas uma matriz para processar, SUMPRODUCT adiciona os itens na matriz e retorna um resultado. Qualquer resultado diferente de zero significa que temos um "acerto", então adicionamos> 0 para forçar um resultado final VERDADEIRO ou FALSO:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Com uma lista codificada
Não é necessário usar um intervalo para a lista de strings a procurar. Você também pode usar uma constante de matriz. Por exemplo, para verificar se há "vermelho", "azul" e "verde", você pode usar uma fórmula como esta:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Prevenindo falsas correspondências
Um problema com essa abordagem é que você pode obter correspondências falsas de substrings que aparecem dentro de palavras mais longas. Por exemplo, se tentar corresponder "dr", também poderá encontrar "Andrea", "drink", "dry", etc., uma vez que "dr" aparece dentro destas palavras. Isso acontece porque SEARCH faz uma correspondência "contém" automaticamente.
Para um hack rápido, você pode adicionar um espaço ao redor das palavras de pesquisa (por exemplo, "dr" ou "dr") para evitar capturar "dr" em outra palavra. Mas isso falhará se "dr" aparecer primeiro ou por último em uma célula, ou aparecer com pontuação.
Se você precisar de uma solução mais precisa, uma opção é normalizar o texto primeiro em uma coluna auxiliar, tomando cuidado para também adicionar um espaço à esquerda e à direita. Em seguida, você usa a fórmula desta página no texto resultante.
