Fórmula genérica
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Resumo
Para categorizar o texto usando palavras-chave com uma correspondência "contém", você pode usar a função SEARCH, com a ajuda de INDEX e MATCH. No exemplo mostrado, a fórmula em C5 é:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
onde palavras - chave é o intervalo nomeado E5: E14 e categorias é o intervalo nomeado F5: F14.
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
Explicação
Basicamente, esta é uma função INDEX e MATCH.
Dentro da função JOGO, usamos a função de pesquisa para células na coluna B procurar cada palavra-chave listada no intervalo nomeado palavras-chave (E5: E14):
SEARCH(keywords,B5)
Como estamos procurando por vários itens (nas palavras-chave do intervalo nomeado ), obteremos vários resultados como este:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
O valor que! ocorre um erro quando SEARCH não consegue encontrar o texto. Quando SEARCH encontra uma correspondência, ele retorna um número que corresponde à posição do texto dentro da célula.
Para alterar esses resultados para um formato mais utilizável, usamos a função ISNUMBER, que converte todos os valores para TRUE / FALSE assim:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Esta matriz vai para a função MATCH como lookup_array, com lookup_value definido como TRUE. MATCH retorna a posição do primeiro TRUE que encontrar no array (7 neste caso), que é fornecido para a função INDEX como o row_num:
=INDEX(categories,7)
INDEX retorna o sétimo item nas categorias , "Auto", como resultado final.
Com XLOOKUP
Com a função XLOOKUP, esta fórmula pode ser um pouco simplificada. XLOOKUP pode utilizar a mesma lógica usada dentro da função MATCH acima, então a fórmula equivalente é:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP localiza o primeiro TRUE na matriz e retorna o valor correspondente das categorias .
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, etc.
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. Então você pode pesquisar por palavras inteiras cercadas por espaços.
