Recuperar o primeiro valor correspondente em uma célula em relação a uma lista

Suponha que você tenha uma lista de palavras-chave e deseja recuperar a primeira palavra-chave que apareceu em uma célula específica, enquanto a célula contém vários outros valores. Para isso, será necessário usar uma fórmula com as funções ÍNDICE e CORRESP combinadas com as funções AGREGAR e PROCURAR.

Como recuperar o primeiro valor correspondente em uma célula em relação a uma lista?

Para obter a primeira palavra-chave correspondente em uma célula em relação à lista de Palavras-chave mostrada na tabela acima, isso significa que precisamos realizar uma correspondência parcial, em vez de uma correspondência exata. Para fazer isso, você pode usar a função PROCURAR para fornecer as posições das palavras-chave que aparecem na célula em números numéricos para a função AGREGAR. Em seguida, AGREGAR obterá o menor número definindo seu function_num como 15 e o argumento ref2 como 1. Depois, use CORRESP para localizar o menor valor e forneça o número da posição para ÍNDICE para recuperar o valor nessa posição.

Sintaxe genérica

=ÍNDICE(intervalo_palavras_chave;CORRESP(AGREGAR(15;6;PROCURAR(intervalo_palavras_chave;célula_consulta);1);PROCURAR(intervalo_palavras_chave;célula_consulta);0))

√ Nota: Esta é uma fórmula de matriz que requer que você a insira usando Ctrl + Shift + Enter.

Para recuperar a primeira palavra-chave correspondente que apareceu na célula B5 em relação à coluna Palavras-chave, copie ou insira a fórmula abaixo na célula C5 e pressione Ctrl + Shift + Enter para obter o resultado:

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

√ Nota: Os sinais de cifrão ($) acima indicam referências absolutas, o que significa que o intervalo_palavras_chave na fórmula não mudará quando você mover ou copiar a fórmula para outras células. No entanto, nenhum sinal de cifrão foi adicionado à célula_consulta, pois você quer que ela seja dinâmica. Após inserir a fórmula, arraste a alça de preenchimento para baixo para aplicar a fórmula às células abaixo.

Explicação da fórmula

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

• PROCURAR($E$5:$E$7;B5): A função PROCURAR retorna a posição de cada palavra-chave do intervalo $E$5:$E$7 quando encontrada em números numéricos, e o erro #VALOR! se não for encontrada. O resultado seria em um array como este: {15;11;#VALOR!}.
• AGREGAR(15;6;PROCURAR($E$5:$E$7;B5);1) = AGREGAR(15;6;{15;11;#VALOR!};1): A função AGREGAR com um function_num de 15 e opção de 6 retornará o menor valor no array de acordo com o argumento ref2 igual a 1, ignorando os valores de erro. Assim, o trecho retornaria 11.
• CORRESP(AGREGAR(15;6;PROCURAR($E$5:$E$7;B5);1);PROCURAR($E$5:$E$7;B5);0) = CORRESP(11;{15;11;#VALOR!};0): O tipo_de_correspondência 0 força a função CORRESP a realizar uma correspondência exata e retornar a posição de 11 no array {15;11;#VALOR!}. Assim, a função retornará 2.
• ÍNDICE($E$5:$E$7;CORRESP(AGREGAR(15;6;PROCURAR($E$5:$E$7;B5);1);PROCURAR($E$5:$E$7;B5);0)) = ÍNDICE($E$5:$E$7;2): A função ÍNDICE então retorna o segundo valor no intervalo $E$5:$E$7, que é bbb.

Observação

• Se não houver palavras-chave em uma célula, um erro #NÚM! será retornado.
• A fórmula não diferencia maiúsculas de minúsculas. Para realizar uma correspondência sensível a maiúsculas e minúsculas, você pode simplesmente substituir a função PROCURAR pela função LOCALIZAR.

Funções relacionadas

Fórmulas relacionadas