Fórmula genérica
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Resumo
Para verificar uma das várias coisas em uma célula e retornar a primeira correspondência encontrada na lista, você pode usar uma fórmula INDEX / MATCH que usa SEARCH ou FIND para localizar uma correspondência. No exemplo mostrado, a fórmula em C5 é:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
onde "coisas" é o intervalo nomeado E5: E9.
Observação: esta é uma fórmula de matriz e deve ser inserida com Control + Shift + Enter.
Explicação
Neste exemplo, temos uma lista de cores em um intervalo nomeado chamado "coisas" (E5: E9). Queremos verificar o texto na coluna B para ver se ele contém alguma dessas cores. Nesse caso, queremos retornar o nome da primeira cor encontrada.
Trabalhando de dentro para fora, esta fórmula usa a função ISNUMBER e a função SEARCH para pesquisar o texto em B5 para cada cor listada em "coisas" como esta:
ISNUMBER(SEARCH(things,B5)
Esta expressão é baseada em uma fórmula (explicada em detalhes aqui) que verifica uma célula para uma única substring. Se a célula contiver a substring, a expressão retornará TRUE. Caso contrário, a expressão retorna FALSE.
Quando damos a este SEARCH uma lista de coisas (em vez de uma coisa) nos devolverá um conjunto de resultados. Cada cor encontrada irá gerar uma posição numérica e as cores não encontradas irão gerar um erro:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
A função ISNUMBER então converte os resultados em valores TRUE / FALSE. Qualquer número se torna TRUE e qualquer erro (não encontrado) se torna FALSE. O resultado é uma matriz como esta:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Esta matriz é retornada à função MATCH como o argumento da matriz. O valor de pesquisa é TRUE e o tipo de correspondência é definido como zero para forçar uma correspondência exata. Quando há uma cor correspondente, MATCH retorna a primeira posição TRUE encontrada. Este valor é alimentado na função INDEX como o número da linha, com o intervalo nomeado "coisas" fornecido como a matriz. Quando houver pelo menos uma correspondência, INDEX retorna a cor naquela posição. Quando nenhuma correspondência é encontrada, esta fórmula retorna o erro # N / A.
Com valores embutidos em código
Se você não deseja configurar um intervalo nomeado externo como "coisas" neste exemplo, você pode codificar valores na fórmula como "constantes de matriz" como este:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Obtenha a primeira correspondência na célula
A linguagem aqui é muito confusa, mas a fórmula acima retornará a primeira correspondência encontrada na lista de itens a serem procurados. Se, em vez disso, você quiser retornar a primeira correspondência encontrada na célula que está sendo testada, pode tentar uma fórmula como esta:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
Nesta versão da fórmula, a função MATCH é configurada para procurar o resultado deste snippet:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
que usa a função AGGREGATE para obter o valor mínimo nos resultados retornados por SEARCH. Precisamos AGGREGATE aqui, porque a matriz de entrada provavelmente conterá erros (retornados por SEARCH quando as coisas não são encontradas) e precisamos de uma função que ignore esses erros e ainda nos forneça o valor numérico mínimo.
O resultado de AGGREGATE é retornado diretamente para MATCH como o valor de pesquisa, junto com a mesma matriz retornada por SEARCH. O resultado final é a primeira correspondência encontrada na célula, não a primeira correspondência encontrada na lista de coisas.
