Ir para o conteúdo principal

Como vlookup e concatenar vários valores correspondentes no Excel?

Autor: Xiao Yang Última modificação: 2020-05-09

Como todos nós sabemos, o Vlookup A função no Excel pode nos ajudar a pesquisar um valor e retornar os dados correspondentes em outra coluna, mas em geral, ela só pode obter o primeiro valor relativo se houver vários dados correspondentes. Neste artigo, falarei sobre como vlookup e concatenar vários valores correspondentes em apenas uma célula ou uma lista vertical.

Vlookup e retorna vários valores correspondentes verticalmente com a fórmula

Vlookup e concatenar vários valores correspondentes em uma célula com a função definida pelo usuário

Vlookup e concatenar vários valores correspondentes em uma célula com o Kutools para Excel


Supondo que eu tenha o seguinte intervalo de dados, para obter todos os valores correspondentes com base em um valor específico verticalmente, conforme mostrado a seguir, você pode aplicar uma fórmula de matriz.

doc vlookup concatenar 1

1. Insira esta fórmula: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") em uma célula em branco onde você deseja colocar o resultado, por exemplo, E2, e pressione Ctrl + Shift + Enter juntas para obter a base de valor relativa em um critério específico, consulte a captura de tela:

doc vlookup concatenar 2

Note: Na fórmula acima:

A1: A16 é o intervalo da coluna que contém o valor específico que você deseja procurar;

D2 indica o valor específico que você deseja visualizar;

B1: B16 é o intervalo de colunas a partir do qual você deseja retornar os dados correspondentes;

$ 1: $ 16 indica a referência de linhas dentro do intervalo.

2. Em seguida, selecione a célula E2 e arraste a alça de preenchimento para baixo até as células até obter células em branco, e todos os valores correspondentes são listados na coluna, conforme mostrado a seguir:

doc vlookup concatenar 3


Em vez de obter os valores relativos verticalmente, às vezes, você deseja os valores correspondentes em uma célula e concatená-los com um separador específico. Neste caso, a seguinte Função Definida pelo Usuário pode lhe fazer um favor.

1. Mantenha pressionada a 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 Módulo Janela.

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

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
    If x = lookupval Then
        result = result & " " & x.Offset(0, indexcol - 1)
    End If
Next x
CusVlookup = result
End Function

3. Em seguida, salve e feche este código, volte para a planilha e insira esta fórmula: = cusvlookup (D2, A1: B16,2) em uma célula em branco onde você deseja colocar o resultado e pressione Entrar chave, todos os valores correspondentes com base em dados específicos foram retornados em uma célula com separador de espaço, consulte a captura de tela:

doc vlookup concatenar 4

Note: Na fórmula acima: D2 indica os valores da célula que você deseja pesquisar, A1: B16 é o intervalo de dados que você deseja buscar os dados, o número 2 é o número da coluna da qual o valor correspondente deve ser retornado, você pode alterar essas referências conforme sua necessidade.


Se você tem Kutools for Excel, Com o seu Combinar linhas avançadas recurso, você pode rapidamente terminar este trabalho com facilidade. Este recurso pode ajudá-lo a combinar todos os valores correspondentes com delimitadores específicos com base nos mesmos dados em outra coluna.

Kutools for Excel : com mais de 300 suplementos úteis do Excel, grátis para testar sem limitação em 30 dias.

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

1. Selecione o intervalo de dados que você deseja obter os valores correspondentes com base nos dados específicos.

2. Então clique Kutools > Unir e dividir > Combinar linhas avançadas, veja a captura de tela:

3. No Combinar linhas avançadas caixa de diálogo, clique no nome da coluna que deseja combinar com base e clique em Chave primária botão, veja a captura de tela:

doc vlookup concatenar 6

4. Em seguida, clique em outro nome de coluna para a qual deseja retornar os valores correspondentes e clique em Combinar para escolher um separador para separar os valores combinados, consulte a captura de tela:

doc vlookup concatenar 7

5. E, em seguida, clique em Ok botão, todos os valores correspondentes com base nos mesmos valores foram combinados com um separador específico, consulte as capturas de tela:

doc vlookup concatenar 8 2 doc vlookup concatenar 9

 Baixe e teste grátis Kutools para Excel agora!


Kutools for Excel: com mais de 300 suplementos úteis do Excel, grátis para testar sem limitação em 30 dias. Baixe e teste grátis agora!

Melhores ferramentas de produtividade de escritório

🤖 Assistente de IA do Kutools: Revolucionar a análise de dados com base em: Execução Inteligente   |  Gerar Código  |  Crie fórmulas personalizadas  |  Analise dados e gere gráficos  |  Invocar funções do Kutools...
Recursos mais comuns: Encontre, destaque ou identifique duplicatas   |  Excluir linhas em branco   |  Combine colunas ou células sem perder dados   |   Rodada sem Fórmula ...
Super pesquisa: VLookup de múltiplos critérios    VLookup de múltiplos valores  |   VLookup em várias planilhas   |   Pesquisa Difusa ....
Lista suspensa avançada: Crie rapidamente uma lista suspensa   |  Lista suspensa de dependentes   |  Lista suspensa de seleção múltipla ....
Gerenciador de colunas: Adicione um número específico de colunas  |  Mover colunas  |  Alternar status de visibilidade de colunas ocultas  |  Compare intervalos e colunas ...
Recursos em destaque: Foco da Grade   |  Vista de Design   |   Grande Barra de Fórmula    Gerenciador de pastas de trabalho e planilhas   |  Biblioteca (Auto texto)   |  Data Picker   |  Combinar planilhas   |  Criptografar/Descriptografar Células    Enviar e-mails por lista   |  Super Filtro   |   Filtro Especial (filtro negrito/itálico/tachado...) ...
15 principais conjuntos de ferramentas12 Texto Ferramentas (Adicionar texto, Remover Personagens, ...)   |   50+ de cores Tipos (Gráfico de Gantt, ...)   |   Mais de 40 práticos Fórmulas (Calcule a idade com base no aniversário, ...)   |   19 Inclusão Ferramentas (Insira o código QR, Inserir imagem do caminho, ...)   |   12 Conversão Ferramentas (Números para Palavras, Conversão de moedas, ...)   |   7 Unir e dividir Ferramentas (Combinar linhas avançadas, Dividir células, ...)   |   ... e mais

Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo.  Clique aqui para obter o recurso que você mais precisa...

Descrição


Office Tab 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!
Comments (16)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Is there any way to get the unique "name" for "class1"
This comment was minimized by the moderator on the site
Hello, sym-john,
Maybe the below article can solve your problem, please view it:
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html
This comment was minimized by the moderator on the site
This is working great for me - is there anyway to change it that it checks if the cell contains rather than a complete match? Basically I have a list of tasks where:
Column A: Dependencies (eg 10003 10004 10008)
Column B: Task Reference (eg 10001)
Column C: Dependent Tasks (the column for the formula result) - where it would lookup the task reference to see which rows contain it in Column A, and then list the Task Reference of those tasks.

E.g:

Row | Column A | Column B | Column C
1 | | 10001 | 10002 10003
2 | 10001 | 10002 | 10003
3 | 10001 10002 | 10003 |
This comment was minimized by the moderator on the site
you would want to use the Instr() function which will check for something in a string of text in a cell. You can also use Left() and Right() if you are looking for the starting or ending details.
This comment was minimized by the moderator on the site
The cusVlookup worked great for me. Another way to have a different separator is to wrap in two substitute functions. The first (from inside to out) replaces the first space with no space, the second replaces all other spaces with a " / " in mine. Could use "," if you want commas.
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,2)," ","",1)," "," / ")

Also, if your lookup value isn't the first column, you can use 0 or negative numbers to go to column to the left.
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,-1)," ","",1)," "," / ")
This comment was minimized by the moderator on the site
Hi, jeff,
Thanks for your sharing, you must be a warmhearted man.
This comment was minimized by the moderator on the site
I have to say, I have been trying to get a formula for combining multiple values and returning them to a single cell for 2 days now. This "How To" has saved me!! Thank you SO much! I would never have gotten it without your Module!
I do have 2 questions though. I have the deliminator as a comma instead of a space and because of that it starts out with a comma. Is there a way to prevent the start comma but keep the rest?
My second question is; When I use the fill handle it changes the range values as well as the cell value I want to look up. I want it to continue to change the cell number I want to look up but keep the same range values. How can I make this happen?

Thank you so much for your help!!
This comment was minimized by the moderator on the site
Is there a way to delete the duplicate values in the concatenate?
This comment was minimized by the moderator on the site
Hello, Jacob,
May be the following article can help you to solve your problem.
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Is there a way to list the duplicate values only once, using the vba code and formula above? I am not sure where to put the countif>1 statement in the formula bar, or in the vba itself. Please help
This comment was minimized by the moderator on the site
you can add two extra condition to skip blank cells and to skip duplicates:For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
If ConcatenateRange.Cells(i).Value <> "" Then 'SKIP BANKS
If InStr(xResult, ConcatenateRange.Cells(i).Value) = 0 Then 'SKIP IF FOUND DUPLICATE
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
End If
End If
Next i
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations