Como fazer uma pesquisa vertical e retornar o último valor correspondente no Excel?
No Excel, a função VLOOKUP é comumente usada para pesquisar e recuperar dados de uma tabela. No entanto, por padrão, "VLOOKUP" só retorna o primeiro valor correspondente que encontra. E se você precisar retornar o último valor correspondente em vez disso? Para alcançar isso, usamos fórmulas alternativas com funções como "LOOKUP", "XLOOKUP", "INDEX", "MATCH" ou "Kutools". Também exploraremos como otimizar esses métodos para melhor desempenho e usabilidade.
Pesquisa vertical e retorno do último valor correspondente no Excel
Pesquisa vertical e retorno do último valor correspondente com a função LOOKUP
A função "LOOKUP" é uma ferramenta poderosa no Excel que pode ser usada para encontrar o último valor correspondente em um conjunto de dados.
Por favor, insira a seguinte fórmula na célula especificada e, em seguida, arraste a alça de preenchimento para baixo nas células para obter o último valor correspondente conforme segue:
=LOOKUP(2,1/($A$2:$A$12=E2),$C$2:$C$12)
- "A2:A12" é o intervalo contendo a coluna de pesquisa;
- "E2" é a célula que contém o valor a ser pesquisado;
- "C2:C12" é o intervalo contendo os valores de retorno.
- "1/($A$2:$A$12=E2)" cria uma matriz de erros #DIV/0! e 1 onde a condição é atendida.
- "LOOKUP(2,...)" procura o último 1 na matriz, encontrando efetivamente a última correspondência.
Pesquisa vertical e retorno do último valor correspondente com Kutools para Excel
O "Kutools para Excel" fornece uma maneira fácil e eficiente de realizar pesquisas avançadas, incluindo retornar o último valor correspondente de um conjunto de dados. Siga estas etapas para alcançar isso sem fórmulas complexas.
Após instalar o Kutools para Excel, por favor faça o seguinte:
1. Clique em "Kutools" > "Super PROC" > "Procurar de baixo para cima", veja a captura de tela:
2. Na caixa de diálogo "Procurar de baixo para cima", por favor faça as seguintes operações:
- Selecione as células de valor de pesquisa e células de saída da seção "Área de Colocação da Lista e Valores de Pesquisa";
- Em seguida, especifique os itens correspondentes da seção "Intervalo de Dados".
- Finalmente, clique no botão "OK".
Então, todos os últimos itens correspondentes foram retornados de uma vez, veja a captura de tela:
Se você quiser substituir o valor de erro #N/D por outro valor de texto, basta clicar no botão "Opções" e marcar a opção "Substituir valores não encontrados (#N/D) por um valor especificado", depois digite o texto que você precisa.
Pesquisa vertical e retorno do último valor correspondente com funções INDEX e MATCH
Embora a função VLOOKUP tradicional não suporte essa funcionalidade, você pode combinar as poderosas funções "INDEX" e "MATCH" para alcançá-la. Este método é dinâmico, eficiente e funciona em todas as versões do Excel.
Por favor, insira a seguinte fórmula na célula especificada, pressione "Ctrl" + "Shift" + "Enter" no Excel 2019 e versões anteriores, pressione a tecla "Enter" diretamente no Excel 365, Excel 2021 e versões posteriores.
=INDEX($C$2:$C$12,MATCH(2,1/($A$2:$A$12=E2)))Depois, arraste a alça de preenchimento para baixo nas células para obter o último valor correspondente conforme segue:
- "A2:A12" é o intervalo contendo a coluna de pesquisa;
- "E2" é a célula que contém o valor a ser pesquisado;
- "C2:C12" é o intervalo contendo os valores de retorno.
- "1/($A$2:$A$12=E2)" cria uma matriz de erros #DIV/0! e 1 onde a condição é atendida.
- "MATCH(2, 1/($A$2:$A$12=E2))" procura o número 2 na matriz criada por 1/($A$2:$A$12=E2). Como 2 não está na matriz, MATCH encontra a posição do último 1 válido, que corresponde à última linha correspondente em $A$2:$A$12.
- "INDEX($C$2:$C$12,...)" usa o número da linha do MATCH para recuperar o valor correspondente de $C$2:$C$12.
Pesquisa vertical e retorno do último valor correspondente com a função XLOOKUP
A função XLOOKUP, introduzida no Excel 365, 2021 e versões posteriores, é uma substituição poderosa e versátil para "VLOOKUP" e "HLOOKUP". Uma de suas principais características é a capacidade de realizar pesquisas em ordem inversa, tornando-a perfeita para encontrar o último valor correspondente em um conjunto de dados.
Por favor, insira a seguinte fórmula na célula especificada e, em seguida, arraste a alça de preenchimento para baixo nas células para obter o último valor correspondente conforme segue:
=XLOOKUP(E2, $A$2:$A$12, $C$2:$C$12, , , -1)
- "E2": O valor de pesquisa;
- "A2:A12": A matriz de pesquisa, ou seja, o intervalo onde a função busca o valor de pesquisa;
- "C2:C12": A matriz de retorno, ou seja, o intervalo de onde o valor correspondente é retornado;
- ,,: Essas duas vírgulas representam argumentos opcionais para if_not_found e match_mode. Deixamos eles vazios neste caso.
- "-1": Especifica o modo de pesquisa para começar a procurar a partir da parte inferior do intervalo.
Retornar o último valor correspondente no Excel pode ser alcançado usando uma variedade de métodos, dependendo das suas necessidades e da versão do Excel que você está usando. Cada método tem seus próprios pontos fortes, e sua escolha deve depender da versão do Excel que você possui. Ao dominar um ou mais desses métodos, você melhorará significativamente suas habilidades de gerenciamento de dados e simplificará seu fluxo de trabalho no Excel. Se você estiver interessado em explorar mais dicas e truques do Excel, nosso site oferece milhares de tutoriais.
Mais artigos relacionados:
- Pesquisa Vertical em Múltiplas Planilhas
- No Excel, podemos facilmente aplicar a função vlookup para retornar os valores correspondentes em uma única tabela de uma planilha. Mas, você já considerou como fazer a pesquisa vertical em várias planilhas? Suponha que eu tenha as seguintes três planilhas com intervalos de dados, e agora quero obter parte dos valores correspondentes com base nos critérios dessas três planilhas.
- Usar Vlookup com Correspondência Exata e Aproximada no Excel
- No Excel, vlookup é uma das funções mais importantes para nós procurarmos um valor na coluna mais à esquerda da tabela e retornar o valor na mesma linha do intervalo. Mas, você aplica a função vlookup com sucesso no Excel? Neste artigo, vou falar sobre como usar a função vlookup no Excel.
- Vlookup para Retornar Branco ou Valor Específico Em Vez de 0 ou N/A
- Normalmente, quando você aplica a função vlookup para retornar o valor correspondente, se sua célula de correspondência estiver em branco, ela retornará 0, e se o valor de correspondência não for encontrado, você receberá um erro #N/A conforme mostrado abaixo na captura de tela. Em vez de exibir o 0 ou o valor #N/A, como você pode fazê-lo mostrar uma célula em branco ou outro valor de texto específico?
- Vlookup e Retornar Toda / Linha Inteira de Um Valor Correspondente no Excel
- Normalmente, você pode fazer a pesquisa vertical e retornar um valor correspondente de um intervalo de dados usando a função Vlookup, mas você já tentou encontrar e retornar toda a linha de dados com base em critérios específicos conforme mostrado na captura de tela a seguir.
- Vlookup e Concatenar Múltiplos Valores Correspondentes no Excel
- Como todos sabemos, a função Vlookup no Excel pode nos ajudar a procurar um valor e retornar os dados correspondentes em outra coluna, mas em geral, só pode obter o primeiro valor relativo se houver vários dados correspondentes. Neste artigo, falarei sobre como fazer a pesquisa vertical e concatenar múltiplos valores correspondentes em apenas uma célula ou em uma lista vertical.
Melhores Ferramentas de Produtividade para Office
Impulsione suas habilidades no Excel com Kutools para Excel e experimente uma eficiência sem igual. Kutools para Excel oferece mais de300 recursos avançados para aumentar sua produtividade e economizar tempo.Clique aqui para obter o recurso que você mais precisa...
Office Tab traz interface de abas para o Office e facilita muito seu trabalho
- Habilite a edição e leitura com abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas abas da mesma janela, em vez de novas janelas.
- Aumente sua produtividade em50% e reduza centenas de cliques do mouse todos os dias!