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

Como vlookup retornar vários valores em uma célula no Excel?

Normalmente, no Excel, quando você usa a função PROCV, se houver vários valores para atender aos critérios, você pode obter apenas o primeiro. Mas, às vezes, você deseja retornar todos os valores correspondentes que atendem aos critérios em uma célula, como mostrado a seguir na imagem, como você poderia resolver isso?

Vlookup para retornar vários valores em uma célula com a função TEXTJOIN (Excel 2019 e Office 365)

Vlookup para retornar vários valores em uma célula com função definida pelo usuário

Vlookup para retornar vários valores em uma célula com um recurso útil


Vlookup para retornar vários valores em uma célula com a função TEXTJOIN (Excel 2019 e Office 365)

Se você tiver a versão superior do Excel, como Excel 2019 e Office 365, há uma nova função - TEXTJOIN, com esta função poderosa, você pode rapidamente vlookup e retornar todos os valores correspondentes em uma célula.

Vlookup para retornar todos os valores correspondentes em uma célula

Por favor, aplique a fórmula abaixo em uma célula em branco onde você deseja colocar o resultado e pressione Ctrl + Shift + Enter juntas para obter o primeiro resultado e, em seguida, arraste a alça de preenchimento para baixo até a célula que deseja usar esta fórmula, e você obterá todos os valores correspondentes, conforme mostrado abaixo:

=TEXTJOIN(",",TRUE,IF($A$2:$A$11=E2,$C$2:$C$11,""))

NOTA Na fórmula acima, A2: A11 é o intervalo de pesquisa contém os dados de pesquisa, E2 é o valor de pesquisa, C2: C11 é o intervalo de dados do qual você deseja retornar os valores correspondentes, ","é o separador para separar os vários registros.

Vlookup para retornar todos os valores correspondentes sem duplicatas em uma célula

Se você deseja retornar todos os valores correspondentes com base nos dados de pesquisa sem duplicatas, a fórmula abaixo pode ajudá-lo.

Copie e cole a seguinte fórmula em uma célula em branco e pressione Ctrl + Shift + Enter juntas para obter o primeiro resultado e, em seguida, copie esta fórmula para preencher outras células, e você obterá todos os valores correspondentes sem os dulpicados, conforme a captura de tela abaixo:

=TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$11, IF(E2=$A$2:$A$11, $C$2:$C$11, ""), 0),"")=MATCH(ROW($C$2:$C$11), ROW($C$2:$C$11)), $C$2:$C$11, ""))

NOTA Na fórmula acima, A2: A11 é o intervalo de pesquisa contém os dados de pesquisa, E2 é o valor de pesquisa, C2: C11 é o intervalo de dados do qual você deseja retornar os valores correspondentes, ","é o separador para separar os vários registros.

Vlookup para retornar vários valores em uma célula com função definida pelo usuário

A função TEXTJOIN acima está disponível apenas para Excel 2019 e Office 365, se você tiver outras versões anteriores do Excel, você deve usar alguns códigos para concluir esta tarefa.

Vlookup para retornar todos os valores correspondentes em uma célula

1. Segure o ALT + F11 chaves, e abre o Microsoft Visual Basic para Aplicações janela.

2. Clique inserção > Móduloe cole o seguinte código no Janela Módulo.

Código VBA: Vlookup para retornar vários valores em uma célula

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

3. Em seguida, salve e feche este código, volte para a planilha e insira esta fórmula: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") em uma célula em branco específica onde deseja colocar o resultado, em seguida, arraste a alça de preenchimento para baixo para obter todos os valores correspondentes em uma célula que você deseja, consulte a captura de tela:

NOTA Na fórmula acima, A2: A11 é o intervalo de pesquisa contém os dados de pesquisa, E2 é o valor de pesquisa, C2: C11 é o intervalo de dados do qual você deseja retornar os valores correspondentes, ","é o separador para separar os vários registros.

Vlookup para retornar todos os valores correspondentes sem duplicatas em uma célula

Para ignorar as duplicatas nos valores correspondentes retornados, faça com o código abaixo.

1. Segure o Alt + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.

2. Clique inserção > Móduloe cole o seguinte código no Janela Módulo.

Código VBA: Vlookup e retorna vários valores correspondentes exclusivos em uma célula

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
'Updateby Extendoffice
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & ","
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function

3. Após inserir o código, clique em Ferramentas > Referência no aberto Microsoft Visual Basic para Aplicações janela e, em seguida, no Referências - VBAProject caixa de diálogo, verifique Tempo de execução de scripts da Microsoft opção no Referências Disponíveis caixa de lista, veja as capturas de tela:

4. Então clique OK para fechar a caixa de diálogo, salve e feche a janela de código, retorne à planilha e insira esta fórmula: =MultipleLookupNoRept(E2,$A$2:$C$11,3) into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values, see screenshot:

NOTA Na fórmula acima, A2: C11 é o intervalo de dados que você deseja usar, E2 é o valor de pesquisa, o número 3 é o número da coluna que contém os valores retornados.

Vlookup para retornar vários valores em uma célula com um recurso útil

 Se você tem nosso Kutools for Excel, Com o seu Combinar linhas avançadas recurso, você pode rapidamente mesclar ou combinar as linhas com base no mesmo valor e fazer alguns cálculos conforme necessário.

NOTAPara aplicar isso Combinar linhas avançadas, em primeiro lugar, você deve baixar o Kutools for Excele, em seguida, aplique o recurso de forma rápida e fácil.

Depois de instalar Kutools for Excel, faça o seguinte:

1. Selecione o intervalo de dados que você deseja combinar com os dados de uma coluna com base em outra coluna.

2. Clique Kutools > Unir e dividir > Combinar linhas avançadas, veja a captura de tela:

3. No estalou para fora Combinar linhas avançadas caixa de diálogo:

  • Clique no nome da coluna-chave a ser combinada com base e clique em Chave primária
  • Em seguida, clique em outra coluna cujos dados você deseja combinar com base na coluna-chave e clique em Combinar para escolher um separador para separar os dados combinados.

4. Então clique OK botão, e você obterá os seguintes resultados:

Baixe e teste grátis Kutools para Excel agora!


Mais artigos relativos:

  • Função VLOOKUP com alguns exemplos básicos e avançados
  • No Excel, a função VLOOKUP é uma função poderosa para a maioria dos usuários do Excel, que é usada para procurar um valor na extremidade esquerda do intervalo de dados e retornar um valor correspondente na mesma linha de uma coluna que você especificou. Este tutorial fala sobre como usar a função VLOOKUP com alguns exemplos básicos e avançados no Excel.
  • Retorna vários valores correspondentes com base em um ou vários critérios
  • Normalmente, pesquisar um valor específico e retornar o item correspondente é fácil para a maioria de nós usando a função VLOOKUP. Mas, você já tentou retornar vários valores correspondentes com base em um ou mais critérios? Neste artigo, apresentarei algumas fórmulas para resolver essa tarefa complexa no Excel.
  • Vlookup e retornar vários valores verticalmente
  • Normalmente, você pode usar a função Vlookup para obter o primeiro valor correspondente, mas, às vezes, você deseja retornar todos os registros correspondentes com base em um critério específico. Neste artigo, falarei sobre como vlookup e retornar todos os valores correspondentes verticalmente, horizontalmente ou em uma única célula.
  • Vlookup e retornar vários valores da lista suspensa
  • No Excel, como você poderia visualizar e retornar vários valores correspondentes de uma lista suspensa, o que significa que quando você escolhe um item da lista suspensa, todos os seus valores relativos são exibidos de uma vez. Neste artigo, irei apresentar a solução passo a passo.

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
Como eu ajustaria essa fórmula para separar cada valor retornado, mas "," bem como retornar apenas valores únicos?
Este comentário foi feito pelo moderador no site
Obrigado pelo código!!

Quanto aos curingas, uma maneira de contornar é usar INSTR

Você pode substituir o [ If rng = pValue Then ] por [ InStr(1, rng.Value, pValue) Then ] e se você não quiser diferenciar maiúsculas de minúsculas, use [ InStr(1, rng.Value, pValue, vbTextCompare) Então ]
Este comentário foi feito pelo moderador no site
Obrigado pelo código VBA acima. Você pode me dizer como fazer os resultados entrarem em uma nova linha na célula, ou seja, como Alt-Enter 300 400 1000 1300
Este comentário foi feito pelo moderador no site
Obrigado por compartilhar o código acima. Estou usando isso há vários meses, mas hoje não parece funcionar. Estou recebendo células em branco em vez do erro usual quando há dados a serem retornados. Alguma ideia?
Este comentário foi feito pelo moderador no site
Trabalho incrível.. Consegui exatamente o que eu quero !!! Adoro !!
Este comentário foi feito pelo moderador no site
Oi, estou realmente impressionado com o trabalho e é tão fácil criar um para usar esta função. no entanto, preciso de mais suporte. Minhas ? é como posso selecionar um número de uma célula com várias células na minha matriz vlookup. ou seja, se a célula A1 = 100, A2 = 350, A3 = 69 C1 = 100; 1222; 12133 C2 = 69; 222 D1 = Apple D2 = banana Então, como posso selecionar 100 da minha coluna de matriz de tabela C para derivar o correspondente D1 = maçã Por favor, note que eu tenho números de 7 dígitos no meu valor de pesquisa e matriz de tabela que é separada por um ";". Eu realmente apreciaria se você pudesse resolver isso e me ajudar a economizar muito tempo.
Este comentário foi feito pelo moderador no site
Obrigado pelo código VBA. Consegui exatamente o que quero! Modifiquei apenas o código " rng.Offset(0, pIndex - 1) " para " rng.Offset(0, pIndex - 2) " . Assim, o MYVLOOKUP é capaz de pesquisar da direita para a esquerda.
Este comentário foi feito pelo moderador no site
Isso é exatamente o que eu estava procurando e não pensei em apenas fazer minha própria UDF. No entanto, não funcionará exatamente como PROCV. Se a string que você está procurando não estiver apenas na primeira coluna, ela poderá fornecer dados fora do intervalo original passado. Nome Número Outro nome Coluna fora do intervalo passado Jay 1 Jay 1 Jay 2 Jay 2 Chris 3 Chris 3 Jorge 4 Jorge 4 Jay 5 Jay 5 Jorge 6 Jorge 6 Se a tabela acima fosse células A1:D7 se você passou apenas A1:C7 seu A função "MYVLOOKUP" retorna 1 1 2 2 5 5 quando você espera que ela retorne 1 2 5. As alterações abaixo corrigem o problema: Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Update 20150310 'Updated 6 /9/16 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long, i As Long Rows = pWorkRng.Rows.Count For i = 1 To Rows If pWorkRng.Cells(i, 1). Value = pValue Then xResult = xResult & " " & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) End If Next i 'For Each rng In pWorkRng ' If rng = pValue Then ' xResult = xResult & " " & rng.Offset(0, pIndex - 1) ' End If 'Next MYVLOOKUP = xResult End Function
Este comentário foi feito pelo moderador no site
Isso funciona muito bem, mas preciso de ajuda com o comando para remover duplicatas dos resultados. Falando sério, ótimo trabalho.
Este comentário foi feito pelo moderador no site
Isso funciona muito bem, mas ainda preciso de ajuda com a função de comando para remover duplicatas dos resultados.
Este comentário foi feito pelo moderador no site
Notifique-me ou acompanhe comentários
Este comentário foi feito pelo moderador no site
Devolve nada! depois de aplicar MYLOOKUP não dando nenhum resultado, mas em branco.
Este comentário foi feito pelo moderador no site
Olá, funciona bem. O que eu gostaria de fazer é adaptar o código para separar os resultados dos valores com "///" ou qualquer outro marcador (por motivos técnicos, não quero apenas um separador de caractere único). Além disso, notei que esta fórmula não funciona com um curinga. Eu sei que estou pedindo demais, mas não funciona, pois o vlookup pode funcionar quando procuro =myvlookup("*"&E6&"*",$A$2:$C$15,2) o que faria/poderia fazer. Alguma ajuda?
Este comentário foi feito pelo moderador no site
Atenção. Eu descobri como obter qualquer separador nessa saída. É rudimentar. Mas eu percebi. xResult = xResult & "///" & rng.Offset(0, pIndex - 1) A última e mais desejada coisa é permitir que ele funcione com curingas nos critérios de pesquisa. Obrigado novamente por esta bela e brilhante solução. Extremamente útil. Agora só quero que a macro seja executada e instalada no meu excel de forma permanente, não importa o que eu esteja fazendo, para que eu possa usá-la quando precisar. E curingas! Muito obrigado. Curingas são tudo o que resta a fazer.
Este comentário foi feito pelo moderador no site
Para obter um registro exclusivo, você pode usar abaixo: (modificado por consultar outro código de usuário) Função MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Update 20150310 'Updated 6/9/16 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long, i As Long Rows = pWorkRng.Rows.Count For i = 1 To Rows If pWorkRng.Cells(i, 1).Value = pValue Then xResult = xResult & "," & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) End If Next i Dim varSection As Variant Dim sTemp As String Dim sDelimiter As String sDelimiter = "," For Each varSection In Split(xResult, sDelimiter) If InStr(1, sDelimiter & sTemp & sDelimiter, sDelimiter & varSection & sDelimiter, vbTextCompare) = 0 Then sTemp = sTemp & sDelimiter & varSection End If Next varSection MYVLOOKUP = Mid(sTemp, Len(sDelimiter) + 1) End Function
Este comentário foi feito pelo moderador no site
Isso funcionou perfeitamente, mas demorei algum tempo para que a função funcionasse corretamente na minha planilha de 20 guias e mais de 50 mil linhas. Agora, a GRANDE questão é como pegar essa string delimitada e usar cada entrada como um valor de pesquisa de Índice/Correspondência (não casado com Índice/Correspondência, mas parece mais rápido) em outro conjunto de dados, retornando o valor SUM de todos os retornos em uma célula . Meu cenário é que eu tenho um único pedido que tem várias faturas. Sua função MYVLOOKUP funciona perfeitamente para relatar todas as faturas em uma célula. O que eu quero fazer agora é pegar cada retorno concatenado com a célula reportada, girar por aquela matriz e totalizar os valores de pagamento de cada fatura de volta na célula da fórmula. Agradeço qualquer ajuda que você possa oferecer sobre isso e obrigado pela função MYVLOOKUP!
Este comentário foi feito pelo moderador no site
Não importa o que eu faça, sempre consigo #valor! retornado em vez de um resultado. vlookup funciona bem, então os dados funcionam. Já segui o processo de habilitação de macros. Até juntei tudo em uma única folha. Alguma ideia??
Este comentário foi feito pelo moderador no site
Ótima macro, útil. Mas preciso saber se ele pode ser modificado para verificar 2 critérios e alguém encontrou de qualquer maneira para fazer com que os curingas funcionem nele. Qualquer ajuda?
Este comentário foi feito pelo moderador no site
Existe uma maneira de alterar o resultado para que, em vez de mostrar 1000 1000 -1000, mostre, por exemplo, 1,000/1,000/(1,000) ?
Este comentário foi feito pelo moderador no site
Ótima função, no entanto, fragmentar 100,000 registros prova um pouco demais para o meu laptop pobre, precisarei deixá-lo funcionar durante a noite!
Este comentário foi feito pelo moderador no site
Isso é incrível, obrigado!
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