Recupere o primeiro valor correspondente na célula em relação a uma lista
Suponha que você tenha uma lista de palavras-chave, para recuperar a primeira palavra-chave que apareceu em uma célula específica, enquanto a célula contém vários outros valores, você terá que usar um ÍNDICE e JOGO fórmula com a ajuda de AGREGAR e Pesquisar funções.
Como recuperar o primeiro valor correspondente na célula em relação a uma lista?
Para obter o primeira palavra-chave correspondida em uma célula com a lista de palavras-chave conforme a tabela mostrada acima, isso significa que precisamos executar uma correspondência de conteúdo em vez de uma correspondência exata. Para fazer isso, você pode usar a função PESQUISAR para alimentar as posições das palavras-chave que aparecem na célula em números numéricos na função AGREGAR. Então AGGREGATE obterá o menor número configurando seu function_num para 15 e o argumento ref2 para 1. Em seguida, use MATCH para localizar o primeiro menor valor e alimentar o número da posição para INDEX para recuperar o valor naquela posição.
Sintaxe genérica
=INDEX(keyword_rng,MATCH(AGGREGATE(15,6,SEARCH(keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))
√ Nota: Esta é uma fórmula de matriz que requer que você insira com Ctrl + Shift + Entrar.
- palavra-chave_rng: O intervalo de células com palavras-chave.
- pesquisa_célula: A célula a ser pesquisada se contém as palavras-chave.
Para recuperar o a primeira palavra-chave correspondente apareceu na célula B5 na coluna Palavras-chave, copie ou insira a fórmula abaixo na célula C5 e pressione Ctrl + Shift + Entrar para obter o resultado:
= INDEX ($ E $ 5: $ E $ 7, CORRESPONDÊNCIA (AGREGADO (15,6, PESQUISA ($ E $ 5: $ E $ 7,B5), 1), PESQUISA ($ E $ 5: $ E $ 7,B5), 0))
√ Nota: Os cifrões ($) acima indicam referências absolutas, o que significa que o palavra-chave_rng na fórmula não mudará quando você mover ou copiar a fórmula para outras células. No entanto, não há cifrões adicionados ao célula_procurada já que você quer que seja dinâmico. Depois de 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
=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))
- PESQUISA ($ E $ 5: $ E $ 7, B5): A função SEARCH retorna a posição de cada palavra-chave do intervalo $ E $ 5: $ E $ 7 quando encontrado em números numéricos, e o #VALOR! erro se não for encontrado. O resultado seria uma matriz como esta: {15; 11; #VALOR!}.
- AGREGADO (15,6,PESQUISA ($ E $ 5: $ E $ 7, B5), 1) = AGREGADO (15,6,{15; 11; #VALOR!}, 1): A função AGGREGATE com um function_num de 15 e opção de 6 retornará o menor valor na matriz de acordo com o ref2 argumento 1, enquanto ignora os valores de erro. Então, o snippet retornaria 11.
- PARTIDA(AGREGADO (15,6,PESQUISA ($ E $ 5: $ E $ 7, B5), 1),PESQUISA ($ E $ 5: $ E $ 7, B5), 0) = PARTIDA(11,{15; 11; #VALOR!}, 0): A tipo_correspondência 0 força a função MATCH a realizar uma correspondência exata e retornar a posição de 11 na matriz {15; 11; #VALOR!}. Então, a função irá retornar 2.
- ÍNDICE ($ E $ 5: $ E $ 7,PARTIDA(AGREGADO (15,6,PESQUISA ($ E $ 5: $ E $ 7, B5), 1),PESQUISA ($ E $ 5: $ E $ 7, B5), 0)) = ÍNDICE ($ E $ 5: $ E $ 7,2): A função INDEX então retorna o 2nd valor na faixa $ E $ 5: $ E $ 7, o que é bbb.
Observação
- Se não houver palavras-chave em uma célula, um #NUM! o erro será retornado.
- A fórmula não faz distinção entre maiúsculas e minúsculas. Para fazer uma correspondência com distinção entre maiúsculas e minúsculas, você pode simplesmente substituir a função SEARCH por ENCONTRAR.
Funções relacionadas
A função INDEX do Excel retorna o valor exibido com base em uma determinada posição de um intervalo ou matriz.
A função MATCH do Excel procura um valor específico em um intervalo de células e retorna a posição relativa do valor.
No Excel, a função SEARCH pode ajudá-lo a encontrar a posição de um caractere específico ou substring da string de texto fornecida, como mostrado a seguir. Neste tutorial, vou apresentar como usar esta função PESQUISA no Excel.
A função AGGREGATE do Excel retorna uma agregação de cálculos, como SUM, COUNT, SMALLL e assim por diante, com a opção de ignorar erros e linhas ocultas.
Fórmulas Relacionadas
Recupere o primeiro valor da lista de uma célula
Para recuperar a primeira palavra-chave listada em um intervalo de uma célula específica, enquanto a célula contém um de vários valores, você terá que usar uma fórmula de matriz bastante complexa com as funções INDEX, MATCH, ISNUMBER e SEARCH.
Correspondência exata com INDEX e MATCH
Se você precisa descobrir as informações listadas no Excel sobre um produto específico, filme ou pessoa, etc., você deve fazer um bom uso da combinação das funções INDEX e MATCH.
Verifique se uma célula contém um texto específico
Aqui, o tutorial fornece algumas fórmulas para verificar se uma célula contém um texto específico e retornar VERDADEIRO e FALSO conforme a imagem abaixo mostrada, e explica os argumentos e como as fórmulas funcionam.
Verifique se a célula contém muitas coisas
Supondo que no Excel, haja uma lista de valores na coluna E, você deseja verificar se as células da coluna B contêm todos os valores da coluna E e retornar VERDADEIRO ou FALSO conforme a imagem mostrada abaixo. Aqui neste tutorial, ele fornece uma fórmula para resolver essa tarefa.
Verifique se a célula contém uma de muitas coisas
Este tutorial fornece uma fórmula para verificar se uma célula contém um dos vários valores do Excel e explica os argumentos da fórmula e como ela funciona.
As melhores ferramentas de produtividade para escritório
Kutools para Excel - ajuda você a se destacar na multidão
Kutools para Excel possui mais de 300 recursos, Garantindo que o que você precisa está a apenas um clique de distância...
Guia Office - Habilite leitura e edição com guias no Microsoft Office (inclui Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduza centenas de cliques do mouse para você todos os dias, diga adeus à mão do mouse.
- Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
- Traz guias eficientes para o Office (inclui Excel), assim como Chrome, Edge e Firefox.
