Fórmula genérica
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Resumo
Para testar uma célula para uma das várias strings e retornar um resultado personalizado para a primeira correspondência encontrada, você pode usar uma fórmula INDEX / MATCH com base na função SEARCH. No exemplo mostrado, a fórmula em C5 é:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
onde coisas (E5: E8) e resultados (F5: F8) são intervalos nomeados.
Esta é uma fórmula de matriz e deve ser inserida com Control + Shift + Enter.
Explicação
Esta fórmula usa dois intervalos nomeados: coisas e resultados . Se você estiver transferindo esta fórmula diretamente, certifique-se de usar intervalos nomeados com os mesmos nomes (definidos com base em seus dados). Se você não quiser usar intervalos nomeados, use referências absolutas.
O núcleo desta fórmula é este snippet:
ISNUMBER(SEARCH(things,B5)
Isso se baseia em outra fórmula (explicada em detalhes aqui) que verifica uma célula em busca de uma única substring. Se a célula contiver a substring, a fórmula retornará TRUE. Caso contrário, a fórmula retorna FALSE.
Porque nós estamos dando a função de pesquisa mais de uma coisa que procurar, no intervalo nomeado coisas , ele vai nos dar mais um resultado, em uma matriz que se parece com isso:
(#VALUE!;9;#VALUE!;#VALUE!)
Números representam correspondências em coisas , erros representam itens que não foram encontrados.
Para simplificar o array, usamos a função ISNUMBER para converter todos os itens do array em TRUE ou FALSE. Qualquer número válido se torna TRUE e qualquer erro (ou seja, algo não encontrado) torna-se FALSE. O resultado é uma matriz como esta:
(FALSE;TRUE;FALSE;FALSE)
que vai para a função MATCH como o argumento lookup_array, com um lookup_value de TRUE:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
MATCH então retorna a posição do primeiro TRUE encontrado, 2 neste caso.
Finalmente, usamos a função ÍNDICE para recuperar um resultado do intervalo nomeado resultados na mesma posição:
=INDEX(results,2) // returns "found red"
Você pode personalizar o intervalo de resultados com quaisquer valores que façam sentido em seu caso de uso.
Prevenindo falsas correspondências
Um problema com essa abordagem com a abordagem ISNUMBER + SEARCH é que você pode obter correspondências falsas de correspondências parciais dentro de palavras mais longas. Por exemplo, se tentar corresponder "dr", também poderá encontrar "Andrea", "drank", "drip", etc., uma vez que "dr" aparece dentro destas palavras. Isso acontece porque SEARCH faz automaticamente uma correspondência "tipo contém".
Para uma solução rápida, você pode envolver as palavras de pesquisa em caracteres de espaço (por exemplo, "dr" ou "dr") para evitar encontrar "dr" em outra palavra. Mas isso falhará se "dr" aparecer primeiro ou por último em uma célula.
Se precisar de uma solução mais robusta, uma opção é normalizar o texto primeiro em uma coluna auxiliar e adicionar um espaço inicial e final. Em seguida, use a fórmula desta página no texto da coluna auxiliar, em vez do texto original.
