Fórmula genérica
=LOOKUP(2,1/SEARCH(things,A1),things)
Resumo
Para verificar uma das várias coisas em uma célula e retornar a última correspondência encontrada na lista, você pode usar uma fórmula baseada nas funções LOOKUP e SEARCH. No caso de várias correspondências encontradas, a fórmula retornará a última correspondência da lista de "coisas".
No exemplo mostrado, a fórmula em C5 é:
=LOOKUP(2,1/SEARCH(things,B5),things)
Explicação
Contexto: você tem uma lista de coisas no intervalo nomeado "coisas" (E5: E8) e deseja verificar as células na coluna B para ver se elas contêm essas coisas. Em caso afirmativo, você deseja devolver o último item de "coisas" que foi encontrado.
Nesta fórmula, a função SEARCH é usada para pesquisar células na coluna B assim:
SEARCH(things,B5)
Quando SEARCH encontra uma correspondência, ele retorna a posição da correspondência na célula que está sendo pesquisada. Quando a pesquisa não consegue encontrar uma correspondência, ele retorna o erro #VALUE. Como estamos dando ao SEARH mais de um item para procurar, ele retornará mais de um resultado. No exemplo mostrado, SEARCH retorna uma matriz de resultados como este:
(8;24;#VALUE!;#VALUE!)
Essa matriz é então usada como um divisor para o número 1. O resultado é uma matriz composta de erros e valores decimais. Os erros representam coisas não encontradas e os valores decimais representam coisas encontradas. No exemplo mostrado, a matriz se parece com isto:
(0.125;0.0416666666666667;#VALUE!;#VALUE!)
Esta matriz serve como "lookup_vector" para a função LOOKUP. O valor de pesquisa é fornecido como o número 2 e o vetor de resultado é o intervalo nomeado "coisas". Esta é a parte inteligente.
A fórmula é construída de forma que o vetor de pesquisa nunca contenha um valor maior que 1, enquanto o valor de pesquisa é 2. Isso significa que o valor de pesquisa nunca será encontrado. Nesse caso, LOOKUP corresponderá ao último valor numérico encontrado na matriz, que corresponde à última "coisa" encontrada por SEARCH.
Finalmente, usando o intervalo nomeado "coisas" fornecido como o vetor de resultado, LOOKUP retorna a última coisa encontrada.
Com valores embutidos em código
Usar um intervalo como "coisas" torna mais fácil modificar a lista de termos de pesquisa (e adicionar mais termos de pesquisa), mas não é um requisito. Você também pode codificar valores diretamente na fórmula, como esta:
=LOOKUP(2,1/SEARCH(("red","blue","green"),B5),("red","blue","green"))
