Fórmula genérica
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Resumo
Se quiser testar uma célula para ver se ela contém todos os itens de uma lista, você pode fazer isso com uma fórmula que usa a função SEARCH, com a ajuda das funções ISNUMBER, SUMPRODUCT e COUNTA.
Contexto
Digamos que você tenha uma lista de strings de texto no intervalo B5: B8 e queira descobrir se essas células contêm todas as palavras em outro intervalo, E5: E7.
Você poderia construir uma fórmula que usa instruções IF aninhadas para verificar cada item, mas isso não será bem escalonado se você tiver muitos itens para procurar. Cada vez que você adicionar uma palavra para procurar, será necessário adicionar outro IF aninhado e ajustar os parênteses.
Solução
A solução é criar uma fórmula para contar todas as correspondências de uma vez. Assim que tivermos isso, simplesmente comparamos essa contagem com a contagem de itens que estamos procurando. Se eles corresponderem, sabemos que uma célula contém todos os itens.
No exemplo mostrado, a fórmula que estamos usando é:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Explicação
A chave é este snippet:
ISNUMBER(SEARCH(things,B5)
Isso se baseia em outra fórmula (explicada em detalhes aqui) que simplesmente 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 FALSE.
No entanto, se dermos à mesma fórmula uma lista de coisas (neste caso, estamos usando um intervalo nomeado chamado "coisas", E5: E7), ela nos dará de volta uma lista de valores VERDADEIRO / FALSO, um para cada item em . O resultado é uma matriz semelhante a esta:
(VERDADEIRO; VERDADEIRO; VERDADEIRO)
Onde cada TRUE representa um item encontrado e cada FALSE representa um item não encontrado.
Podemos forçar os valores TRUE / FALSE para 1s e 0s com um negativo duplo (-, também chamado de unário duplo):
--ISNUMBER(SEARCH(things,B5))
que produz uma matriz como esta:
(1; 1; 1)
Em seguida, processamos esse array com SUMPRODUCT, que nos dará uma soma total. Se essa soma for igual ao número de itens no intervalo nomeado "coisas", sabemos que encontramos todas as coisas e podemos retornar VERDADEIRO. A maneira como fazemos isso é comparar os dois números diretamente. Obtemos uma contagem de células não vazias em "coisas" usando CONT.valores:
=COUNTA(things)
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:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
