Obter o primeiro valor correspondente numa célula comparando com uma lista

Suponha que tem uma lista de palavras-chave e pretende obter a primeira palavra-chave que aparece numa célula específica, mesmo que esta contenha vários outros valores. Para o conseguir, terá de utilizar uma fórmula com as funções ÍNDICE e CORRESP, com o auxílio das funções AGREGAR e PESQUISAR.

Como obter o primeiro valor correspondente numa célula ao compará-lo com uma lista?

Para obter a primeira palavra-chave correspondente numa célula comparando com a lista de palavras-chave, conforme mostrado na tabela acima, é necessário realizar uma correspondência por conteúdo e não uma correspondência exata. Para isso, pode utilizar a função PESQUISAR para fornecer à função AGREGAR as posições numéricas das palavras-chave que aparecem na célula. Em seguida, a função AGREGAR obtém o menor número ao definir o seu número_da_função como 15 e o argumento ref2 como 1. Depois, utilize a função CORRESP para localizar a primeira ocorrência desse valor mais pequeno e forneça essa posição à função ÍNDICE para obter o valor correspondente.

Sintaxe genérica

=INDEX(keyword_rng,MATCH(AGGREGATE(15,6,SEARCH(keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))

√ Nota: Trata-se de uma fórmula de vetor que requer que a introduza com Ctrl+Shift+Enter.

Para obter a primeira palavra-chave correspondente que aparece na célula B5 comparando com a coluna Palavras-chave, copie ou introduza a fórmula abaixo na célula C5 e prima Ctrl+Shift+Enterpara obter o resultado:

=ÍNDICE()$E$5:$E$7,CORRESP(AGREGAR(15,6,PESQUISAR()))$E$5:$E$7,B5),1),PESQUISAR($E$5:$E$7,B5),0))

√ Nota: Os símbolos de cifrão ($) acima indicam referências absolutas, o que significa que o intervalo_palavras_chavena fórmula não será alterado ao mover ou copiar a fórmula para outras células. Contudo, não foram adicionados símbolos de cifrão à célula_procurada, pois pretende que esta seja dinâmica. Após introduzir a fórmula, arraste a alça de preenchimento para baixo para aplicar a fórmula às células seguintes.

Explicação da fórmula

=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))

• SEARCH($E$5:$E$7,B5): A função PESQUISAR devolve a posição de cada palavra-chave do intervalo $E$5:$E$7 quando encontrada, sob a forma de números, e o erro #VALOR! caso contrário. O resultado é apresentado num vetor com este formato: {15;11;#VALOR!}.
• AGREGAR(15,6,)SEARCH($E$5:$E$7,B5),1)=AGREGAR(15,6,){15;11;#VALOR!},1):A função AGREGAR com um número_da_função de 15 e uma opção de 6 devolve o menor valor no vetor, de acordo com o argumento ref2 1, ignorando simultaneamente os valores de erro. Assim, este excerto devolve 11.
• CORRESP()AGREGAR(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)=CORRESP()11,{15;11;#VALOR!},0):A tipo_corresp 0 obriga a função CORRESP a efetuar uma correspondência exata e a devolver a posição de 11 no vetor {15;11;#VALOR!}. Assim, a função devolve 2.
• ÍNDICE($E$5:$E$7,)CORRESP()AGREGAR(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)) = ÍNDICE($E$5:$E$7,2):A função ÍNDICE devolve então o 2.º valor no intervalo $E$5:$E$7, que é bbb.

Observação

• Se não houver palavras-chave numa célula, será devolvido um erro #NÚM!.
• A fórmula não diferencia maiúsculas de minúsculas. Para efetuar uma correspondência sensível a maiúsculas e minúsculas, basta substituir a função PESQUISAR pela função ENCONTRAR.

Funções relacionadas

Fórmulas relacionadas