Note: The other languages of the website are Google-translated. Back to English

Como vlookup encontrar o primeiro, segundo ou enésimo valor de correspondência no Excel?

Supondo que você tenha duas colunas com Produtos e quantidades, conforme a captura de tela abaixo mostrada. Para descobrir rapidamente as quantidades da primeira ou da segunda banana, o que você faria?

Aqui, a função vlookup pode ajudá-lo a lidar com esse problema. Neste artigo, mostraremos como vlookup encontrar o primeiro, segundo ou enésimo valor de correspondência com a função Vlookup no Excel.

Vlookup encontra o primeiro, segundo ou enésimo valor de correspondência no Excel com a fórmula

O vlookup encontra facilmente o primeiro valor correspondente no Excel com o Kutools para Excel


Vlookup encontra o primeiro, segundo ou enésimo valor de correspondência no Excel

Faça o seguinte para encontrar o primeiro, segundo ou enésimo valor de correspondência no Excel.

1. Na célula D1, insira os critérios que você deseja ver, aqui eu insiro Banana.

2. Aqui encontraremos o primeiro valor de correspondência de banana. Selecione uma célula em branco, como E2, copie e cole a fórmula =INDEX($B$2:$B$6,MATCH(TRUE,EXACT($D$1,$A$2:$A$6),0)) na Barra de Fórmula e, em seguida, pressione Ctrl + mudança + Entrar chaves simultaneamente.

Nota: Nesta fórmula, $ B $ 2: $ B $ 6 é o intervalo dos valores correspondentes; $ A $ 2: $ A $ 6 é o intervalo com todos os critérios para vlookup; $ D $ 1 é a célula que contém os critérios de vlookup especificados.

Então você obterá o primeiro valor de correspondência de banana na célula E2. Com esta fórmula, você só pode obter o primeiro valor correspondente com base em seus critérios.

Para obter qualquer enésimo valor relativo, você pode aplicar a seguinte fórmula: =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),1)) + Ctrl + mudança + Entrar juntas, esta fórmula retornará o primeiro valor correspondido.

Notas:

1. Para encontrar o segundo valor de correspondência, altere a fórmula acima para =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),2)), e depois pressione Ctrl + mudança + Entrar chaves simultaneamente. Veja a imagem:

2. O último número na fórmula acima significa o enésimo valor de correspondência dos critérios vlookup. Se você alterá-lo para 3, ele obterá o terceiro valor de correspondência e mudará para n, o enésimo valor de correspondência será descoberto.


O Vlookup encontra o primeiro valor correspondente no Excel com o Kutools para Excel

Yvocê pode encontrar facilmente o primeiro valor correspondente no Excel sem se lembrar das fórmulas com o Procure um valor na lista fórmula fórmula de Kutools for Excel.

Antes de aplicar Kutools for Excel, Por favor baixe e instale primeiro.

1. Selecione uma célula para localizar o primeiro valor correspondente (diz célula E2) e clique em Kutools > Fórmula Helper > Fórmula Helper. Veja a imagem:

3. No Fórmula Helper caixa de diálogo, configure da seguinte maneira:

  • 3.1 no Escolha uma fórmula caixa, encontre e selecione Procure um valor na lista;
    Tips: Você pode verificar o filtros caixa, digite determinada palavra na caixa de texto para filtrar a fórmula rapidamente.
  • 3.2 no Tabela_array caixa, selecione o tabela que contém os primeiros valores de valor correspondentes.;
  • 3.2 no Valor_procurado caixa, selecione a célula que contém o critérios você retornará o primeiro valor com base em;
  • 3.3 no Coluna caixa, especifique a coluna da qual você retornará o valor correspondente. Ou você pode inserir o número da coluna na caixa de texto diretamente, conforme necessário.
  • 3.4 Clique no OK botão. Veja a imagem:

Agora, o valor da célula correspondente será preenchido automaticamente na célula C10 com base na seleção da lista suspensa.

  Se você quiser ter uma avaliação gratuita (30-dia) deste utilitário, por favor clique para fazer o downloade, em seguida, aplique a operação de acordo com as etapas acima.


As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2021 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Recursos completos de avaliação gratuita de 30 dias. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
parte inferior da aba do escritório
Comentários (43)
Ainda não há classificações. Seja o primeiro a avaliar!
Este comentário foi feito pelo moderador no site
Oi, você pode fornecer o vídeo para a fórmula acima mencionada para obter o 2º, 3º valor dos dados
Este comentário foi feito pelo moderador no site
Muito obrigado!!!
Este comentário foi feito pelo moderador no site
E se a banana puder ser amarela ou verde, como podemos usar essa fórmula para mostrar a quantidade certa com base em dois valores (em vez de um agora)? Obrigado pela ajuda!
Este comentário foi feito pelo moderador no site
Se o resultado do valor for #NUM! você poderia me mostrar a fórmula para adicionar para que ela retorne ao resultado ZERO. Obrigada
Este comentário foi feito pelo moderador no site
Basta adicionar SEERRO(sua fórmula, o resultado que você deseja retornar), por exemplo, a fórmula é =soma(A1:A6), então ela seria convertida para =SEERRO(soma(A1:A6),""), ele será retorne em branco se o resultado for um erro como #NUM!.
Este comentário foi feito pelo moderador no site
Ajude-me a encontrar o valor máximo de Bananaa usando uma fórmula. Ou seja, para exibir 300
Este comentário foi feito pelo moderador no site
Como encontrar o valor máximo de Bananaa
Este comentário foi feito pelo moderador no site
=Sumproduct(((A2:A6)=D1)*((B2:B6)=Max(B2:B6))*(B2:B6))
Este comentário foi feito pelo moderador no site
[quote]=Sumproduct(((A2:A6)=D1)*((B2:B6)=Max(B2:B6))*(B2:B6))Por Ferdhy[/quote] agradeço sua ajuda FERDHY. eu tentei a fórmula, mas como max(B2:B6) é 500 (laranja), o valor que recebo é 0.
Este comentário foi feito pelo moderador no site
Oi, basta usar isso =SUMPRODUCT(MAX(((A2:A8)=D1)*(B2:B8))) uma vez que você mudar em D1 e colocar Banana, você deve obter 300, se você colocar Orange, você receberá 500 .
Este comentário foi feito pelo moderador no site
Você também pode usar:
=max(if(A2:A6=D1, B2:B6)) + Ctrl + Shift + Enter
Este comentário foi feito pelo moderador no site
Como posso aumentar o último número automaticamente ao arrastar a fórmula para baixo: =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6) -ROW($A$2)+1),2)),
Este comentário foi feito pelo moderador no site
Caro Warthogb,

Se você deseja obter automaticamente todos os valores correspondentes arrastando a fórmula para baixo, aplique a seguinte fórmula de matriz:



=SEERRO(ÍNDICE($B$2:$B$7,PEQUENO(SE($D$1=$A$2:$A$7,FILA($A$2:$A$7)-FILA($A$2)+1), 1+(ROW(A1)-1))), "") + Ctrl + Shift + Enter
Este comentário foi feito pelo moderador no site
Crystal, Muito obrigado, só tive a chance hoje 27/8/48 de ver sua ajuda, farei a fórmula ainda hoje :)
Este comentário foi feito pelo moderador no site
Oi,
Apliquei esta fórmula mas no meu caso tenho números no lugar do nome do produto. Quando arrasto a fórmula para procurar o próximo número na lista, recebo um erro.

volume de eventos apenas volume de eventos ímpares
1 0.3 1 0.3
1 2.5 1 2.5
2 1.1 3 #NUM
2 0.5 3 #NUM
3 0
3 0.2
3 1
Este comentário foi feito pelo moderador no site
Querida Abby,
O recurso de arrastar para baixo só pode funcionar para os mesmos valores de vlookup. Mas no seu caso, os valores de vlookup são diferentes (1 e 3).
Por favor, use esta fórmula de matriz: =SEERRO(ÍNDICE($B$2:$B$8,SMALL(IF($C$4=$A$2:$A$8,ROW($A$2:$A$8)-ROW($A $2)+1),1+(ROW(A1)-1))), "") + Ctrol + Shift + Enter e arraste a fórmula para baixo para obter todos os valores correspondentes no mesmo valor de vlookup conforme a captura de tela abaixo.
Este comentário foi feito pelo moderador no site
Tutorial incrível! Funcionou como um encanto, mesmo em várias folhas no mesmo arquivo! Muito Obrigado!!
Este comentário foi feito pelo moderador no site
Minha fórmula atual é {=SEERRO(ÍNDICE(Planilha3!$C$2:$C$596,SMALL(IF(Planilha3!$A$2:$A$596=Planilha2!A19,ROW(Planilha3!$A$2:$A$596)) -ROW(INDEX(Sheet3!$A$2:$A$596,1,1))+1),P19)),0)} Mas como eu aplicaria isso com vários critérios, digamos duas correspondências?
Este comentário foi feito pelo moderador no site
meu problema é parecido
me ping se você encontrar solução
Este comentário foi feito pelo moderador no site
crie uma coluna auxiliar que concatene seus critérios, então use o concatenar como critério!

Espero que funcione!
Este comentário foi feito pelo moderador no site
Estou com um pequeno problema com essa fórmula, ela não funciona exatamente para o meu caso:
=ÍNDICE($B$2:$B$6,PEQUENO(SE($D$1=$A$2:$A$6,FILA($A$2:$A$6)-FILA($A$2)+1),1) ) + Ctrl + Shift + Enter

e se o critério que procuro não for exatamente o mesmo todas as vezes (Banana), mas se tornar parte de uma frase (banana republic) e assim por diante; o que então? Ao alterar o número "n" no final desta fórmula, recebo "#NUM!" resposta. Tenho uma coluna de vocabulário que quero pesquisar seu significado na segunda coluna e, digitando uma palavra, preciso obter todas as ocorrências dessa palavra em qualquer frase a ser listada. Alguma ajuda sobre isso?
Obrigado,
RG
Este comentário foi feito pelo moderador no site
É possível usar esta fórmula para descobrir se um número está entre dois números. Abaixo está minha fórmula. Tentando ver se uma listagem com indivíduos e um valor está entre outras células definidas (Exemplo: $ 50,000 e $ 74,999)


=ArrayFormula(INDEX('4 - Lista de Doadores'!$B$2:$B$1000,SMALL(IF('4 - Lista de Doadores'!$F$2:$F$1000>=D$2,ROW('4 - Lista de Doadores '!$F$2:$F$1000)-ROW('4 - Lista de doadores'!$F$2)+1),$A6)))
Este comentário foi feito pelo moderador no site
Prezada Sra/Sr,

Eu tenho um problema:
Eu conhecia uma quantidade do produto, quero descobrir o nome do produto do primeiro ou segundo valor de correspondência de 200, o que você faria?
Muito obrigado!

Sim Van Narith
Este comentário foi feito pelo moderador no site
Dia bom,
Supondo que o valor de 200 localizações na célula F2, tente esta fórmula: =VLOOKUP(F2,IF({1,0},$B$2:$B$7,$A$2:$A$7),2,0).
Espero que possa ajudar. Obrigado por seu comentário.
Este comentário foi feito pelo moderador no site
se um membro foi chamado em 1º de outubro (conjunto de dados de outubro) e não foi revivido, novamente o cce o chamou em 15 de novembro (conjunto de dados de novembro). O membro é revivido em 16 de novembro. Ao verificar o renascimento usando o VLookup, ele faz SIM para as entradas de outubro e novembro. Como evitar, deve mostrar “SIM” para a entrada de novembro, na verdade, quando foi revivida, e também deixar a entrada de outubro como “NÃO”.
Este comentário foi feito pelo moderador no site
É possível encontrar uma média dos dados não exclusivos. Ou seria possível ter uma lista suspensa na célula dos vários valores?
Este comentário foi feito pelo moderador no site
Bom dia,
Desculpe, não posso ajudá-lo com isso ainda. Obrigado pelo seu comentário.
Este comentário foi feito pelo moderador no site
E se você quiser o penúltimo, penúltimo, enésimo último basta adicionar um contador (contar o número de eventos já ocorridos) ao final e subtrair por 0,1,n respectivamente.

Muito obrigado! eu tava procurando isso a muito tempo
Este comentário foi feito pelo moderador no site
Bom dia,
Desculpe, não posso ajudá-lo com isso ainda. Obrigado pelo seu comentário.
Este comentário foi feito pelo moderador no site
Se a primeira ou qualquer outra entrada para a coluna B de 'banana' for uma célula em branco, da qual não exijo esse número, quais alterações são necessárias nesta fórmula para pular a célula em branco na coluna B.
Este comentário foi feito pelo moderador no site
Desculpe, estou usando esta fórmula
=INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),1))
Este comentário foi feito pelo moderador no site
RESOLVIDO:
=SMALL(IF(A2:A7=D1,IF(B2:B7<>"",B2:B7)),1)

Se for necessário trocar o 2º ou 3º número ),1) para 2 ou 3

Esta fórmula não requer índice, pois analisa diretamente o valor na célula
Este comentário foi feito pelo moderador no site
Correção da fórmula anterior:
O valor estava lendo o menor ou maior.

Fórmula atualizada
=INDEX($B$2:$B$7,SMALL(IF($A2:$A6=$D$1,IF($B$2:$B$7<>"",ROW($A2:$A6)-ROW($A2)+1)),1))

Isso pula a célula em branco e coloca o valor da célula não em branco. Substitua +1 por +2 ou +3 para o 2º ou 3º valor
Este comentário foi feito pelo moderador no site
Explicação perfeita, obrigado.
Este comentário foi feito pelo moderador no site
Eu tenho um cenário... Como faço para obter o último preço de qualquer coisa para referência... Exemplo: O primeiro preço da banana foi 200... Ao comprar pela segunda vez; Eu preciso exibir 200 na minha célula de preço esperado e aí se eu comprar isso no dia a 220, vou colocar esse valor manualmente como 220... Sempre na próxima vez vou comprar banana; Preciso exibir 220 do último preço de compra
Este comentário foi feito pelo moderador no site
Try =INDEX($B$2:$B$6,XMATCH(TRUE,EXACT($D$1,$A$2:$A$6),0,-1))

Isso é essencialmente reverter a ordem de pesquisa e retornar a primeira correspondência usando a função XMATCH.

Antes tarde do que nunca, espero que ajude alguém :)
Não há comentários postados aqui ainda
carregar mais
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos

Siga-nos

Copyright © 2009 - www.extendoffice.com. | Todos os direitos reservados. Distribuído por ExtendOffice. | | | Mapa do site
Microsoft e o logotipo do Office são marcas comerciais ou marcas registradas da Microsoft Corporation nos Estados Unidos e / ou em outros países.
Protegido por Sectigo SSL