Como fazer uma procura vertical (vlookup) e concatenar múltiplos valores correspondentes no Excel?

Ao usar a função VLOOKUP no Excel, ela geralmente retornará apenas o primeiro valor correspondente que encontrar para um dado critério de pesquisa. No entanto, há muitos cenários comuns onde você pode precisar recuperar e combinar todos os valores correspondentes associados a uma chave específica, como listar todos os alunos em uma turma ou todos os produtos associados a uma determinada categoria. Como a função VLOOKUP padrão é limitada nesse aspecto, você pode se perguntar como alcançar a capacidade de procurar e concatenar múltiplos resultados correspondentes em uma única célula. Abaixo, exploraremos vários métodos práticos e eficientes para realizar essa tarefa, adequados para diferentes versões do Excel e preferências de usuário.

Procura vertical (Vlookup) e concatenação de múltiplos valores correspondentes no Excel

• Usando as funções TEXTJOIN e FILTER (Excel 365/2021)
• Usando Kutools para Excel
• Usando Função Definida pelo Usuário
• Usando código VBA para Múltiplas Concatenações

Procurar verticalmente (Vlookup) e concatenar múltiplos valores correspondentes com as funções TEXTJOIN e FILTER

Se você está usando o Excel 365 ou Excel 2021, a combinação das funções TEXTJOIN e FILTER fornece uma abordagem eficiente baseada em fórmulas para procurar verticalmente e concatenar todos os valores correspondentes. Essa solução é especialmente adequada para conjuntos de dados dinâmicos e atualizados, pois atualizará automaticamente o resultado quando os dados de origem mudarem. É melhor aplicada quando a sua versão do Excel suporta a função FILTER, que é exclusiva das versões mais recentes do Office.

Na célula de destino, insira a seguinte fórmula e, em seguida, arraste a fórmula para baixo se desejar aplicá-la a outras linhas também. Todos os valores correspondentes serão extraídos e combinados em uma célula. Veja a captura de tela:

=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$16, $A$2:$A$16=D2, ""))

Procurar verticalmente e concatenar múltiplos valores correspondentes com Kutools para Excel

Se você achar métodos de fórmulas embutidas complicados ou sua versão do Excel não suporta funções avançadas como TEXTJOIN e FILTER, o Kutools para Excel oferece uma solução gráfica amigável ao usuário. O recurso Pesquisa Um-para-Muitos no Kutools permite que você procure e concatene múltiplos resultados correspondentes em poucos passos, tornando-o adequado tanto para iniciantes quanto para usuários avançados. Com o Kutools, não há necessidade de escrever fórmulas ou códigos complicados, e é especialmente útil ao lidar com grandes ou variáveis conjuntos de dados que exigem pesquisas repetidas e agregações.

Após instalar o Kutools para Excel, siga os passos abaixo:

Clique em Kutools > Super PROC > Pesquisa um-para-muitos (retornar vários resultados) para abrir a caixa de configuração. Dentro dessa caixa, você pode configurar rapidamente suas opções de pesquisa e saída usando os seguintes passos:

1. Selecione suas células de saída alvo para os resultados concatenados e as células contendo os valores que deseja pesquisar;
2. Indique o intervalo da tabela que contém tanto a chave de pesquisa quanto as colunas de resultados;
3. Especifique qual coluna contém as chaves de pesquisa (Coluna Chave) e a coluna cujos valores serão concatenados (Coluna de Retorno);
4. Clique no botão OK para confirmar suas configurações e processar os dados.
   

Resultado: O Kutools agora exibirá todos os valores correspondentes e concatenados em sua célula de saída selecionada. Veja a captura de tela:

Este método é altamente recomendado para aqueles que preferem trabalhar a partir da interface do Excel sem fórmulas ou códigos complexos. Ele também reduz a probabilidade de erros de fórmula e melhora a produtividade ao lidar com tarefas repetitivas de pesquisa e concatenação.

Procura vertical e concatenação de múltiplos valores correspondentes com Função Definida pelo Usuário

Para usuários proficientes em VBA (Visual Basic for Applications), ou aqueles que usam versões mais antigas do Excel que não têm suporte para arrays dinâmicos ou função FILTER, você pode criar uma Função Definida pelo Usuário personalizada (UDF) para alcançar a concatenação flexível de múltiplos resultados. Este método é universalmente compatível com todas as versões do Excel e pode ser personalizado para símbolos de separação específicos ou condições.

1. Pressione e mantenha as teclas ALT + F11 para abrir a janela Microsoft Visual Basic for Applications.

2. Clique em Inserir > Módulo e cole o seguinte código na Janela de Módulo.

Código VBA: Procurar verticalmente e concatenar múltiplos valores correspondentes em uma célula

Function ConcatenateMatches(LookupValue As String, LookupRange As Range, ReturnRange As Range, Optional Delimiter As String = ", ") As String
'Updateby Extendoffice
    Dim Cell As Range
    Dim Result As String
    Result = ""
    For Each Cell In LookupRange
        If Cell.Value = LookupValue Then
            Result = Result & Cell.Offset(0, ReturnRange.Column - LookupRange.Column).Value & Delimiter
        End If
    Next Cell
    If Result <> "" Then
        Result = Left(Result, Len(Result) - Len(Delimiter))
    End If
    ConcatenateMatches = Result
End Function

3. Salve e feche o editor VBA. Volte para sua planilha e use esta UDF inserindo a fórmula: =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) em uma célula em branco onde você deseja seu resultado. Arraste a alça de preenchimento para baixo para copiar a fórmula para outras células, conforme necessário. Todos os valores correspondentes com base em um valor específico serão retornados e concatenados em uma célula, separados por vírgula e espaço. Veja a captura de tela:

Procura vertical e concatena múltiplos valores correspondentes com código VBA

Para cenários que exigem uso repetitivo ou para aqueles que desejam evitar funções personalizadas nas células da planilha, você pode usar uma macro VBA pronta para concatenar resultados diretamente. Este método funciona bem em ambientes compartilhados onde nem todos os usuários podem ter a mesma versão ou complementos.

1. Clique em Ferramentas de Desenvolvimento > Visual Basic para abrir o editor VBA.

2. Na janela VBA, clique em Inserir > Módulo, depois cole este código no módulo:

Sub VLookupAndConcatenate()
    Dim ws As Worksheet
    Dim dataRange As Range, lookupRange As Range, resultRange As Range
    Dim dict As Object
    Dim i As Long, lastRow As Long
    Dim lookupValue As Variant, result As String
    Dim delimiter As String
    delimiter = ", "
    Set dict = CreateObject("Scripting.Dictionary")
    Set ws = ActiveSheet
    On Error Resume Next
    Set dataRange = Application.InputBox( _
        Prompt:="Please select the data range (contains lookup column and result column)", _
        Title:="Select Data Range", _
        Type:=8)
    On Error GoTo 0
    If dataRange Is Nothing Then Exit Sub
    On Error Resume Next
    Set lookupRange = Application.InputBox( _
        Prompt:="Please select the lookup range (single column)", _
        Title:="Select Lookup Range", _
        Type:=8)
    On Error GoTo 0
    If lookupRange Is Nothing Then Exit Sub
    On Error Resume Next
    Set resultRange = Application.InputBox( _
        Prompt:="Please select the starting cell for results output", _
        Title:="Select Output Location", _
        Type:=8)
    On Error GoTo 0
    If resultRange Is Nothing Then Exit Sub
    resultRange.Resize(lookupRange.Rows.Count, 1).ClearContents
    For i = 1 To dataRange.Rows.Count
        lookupValue = dataRange.Cells(i, 1).Value
        If Not dict.Exists(lookupValue) Then
            dict.Add lookupValue, dataRange.Cells(i, 2).Value
        Else
            dict(lookupValue) = dict(lookupValue) & delimiter & dataRange.Cells(i, 2).Value
        End If
    Next i
    For i = 1 To lookupRange.Rows.Count
        lookupValue = lookupRange.Cells(i, 1).Value
        If dict.Exists(lookupValue) Then
            resultRange.Cells(i, 1).Value = dict(lookupValue)
        Else
            resultRange.Cells(i, 1).Value = "Not Found"
        End If
    Next i
    MsgBox "Operation completed! Processed " & lookupRange.Rows.Count & " lookup values.", vbInformation
End Sub

3. Clique no botão para executar a macro. As caixas de entrada solicitarão que você selecione o intervalo de dados, intervalo de pesquisa, intervalo de resultados. O resultado concatenado é então exibido diretamente nas células de saída selecionadas.

Essa abordagem de macro é particularmente útil se você frequentemente realiza várias buscas de concatenação com diferentes valores, pois evita encher a planilha com chamadas de UDF.

Você pode facilmente ajustar o delimitador no código, se necessário, e estender a macro para gerar resultados em uma célula ou arquivo de acordo com seu fluxo de trabalho.

Concatenar múltiplos valores correspondentes no Excel é possível usando várias abordagens, cada uma com benefícios específicos dependendo da sua situação. Seja você escolher fórmulas de matriz dinâmica, complementos como Kutools para Excel ou métodos baseados em VBA, você melhorará sua capacidade de analisar e exibir dados agrupados de forma eficiente. Dependendo do tamanho e complexidade do seu conjunto de dados, considere qual abordagem oferece o melhor desempenho e facilidade de manutenção para você ou sua equipe. Nas operações diárias, verifique a consistência dos dados, evite células mescladas e verifique os intervalos de referência para obter os melhores resultados. Se você encontrar erros nos cálculos da fórmula, verifique novamente se seus intervalos correspondem aos dados e se você está usando o método correto de entrada da fórmula para sua versão do Excel.

Para técnicas mais avançadas do Excel e uma ampla gama de guias práticas de como fazer, visite nossa extensa biblioteca de tutoriais.