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

Quando você usa VLOOKUP no Excel, ele geralmente retorna o primeiro valor correspondente de um conjunto de dados. No entanto, há cenários em que você pode precisar retornar e concatenar múltiplos valores correspondentes (por exemplo, todos os nomes associados a uma classe específica). Como o VLOOKUP sozinho não consegue fazer isso, vamos explorar técnicas avançadas para alcançar o resultado desejado.

Pesquisar verticalmente e concatenar 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

Pesquisa vertical e concatenação de múltiplos valores correspondentes com as funções TEXTJOIN e FILTER

Se você está usando Excel 365 ou Excel 2021, a combinação das funções TEXTJOIN e FILTER oferece uma maneira poderosa e eficiente de concatenar múltiplos valores correspondentes.

Na célula de destino, insira a seguinte fórmula, depois arraste a fórmula para outras células para preenchê-las. Todos os valores correspondentes encontrados são extraídos e combinados em uma única célula. Veja a captura de tela:

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

Pesquisa vertical e concatenação de múltiplos valores correspondentes com Kutools para Excel

Cansado das limitações do VLOOKUP do Excel ao lidar com múltiplos valores correspondentes? Mas com o recurso Pesquisa Um-para-Muitos do Kutools para Excel, você pode pesquisar e concatenar múltiplos valores correspondentes com apenas alguns cliques!

Após instalar o Kutools para Excel, siga estas instruções:

Clique em "Kutools" > "Super PROC" > "Pesquisa um-para-muitos (retorna múltiplos resultados)" para abrir a caixa de diálogo. Na caixa de diálogo, especifique as operações conforme abaixo:

1. Selecione o "Intervalo de saída" e os "Valores de pesquisa" nas caixas de texto separadamente;
2. Selecione o intervalo da tabela que deseja usar;
3. Especifique a coluna-chave e a coluna de retorno nos menus suspensos "Coluna Chave" e "Coluna de Retorno" separadamente;
4. Finalmente, clique no botão OK.
   

Resultado: Agora, todos os valores correspondentes estão concatenados em uma única célula. Veja a captura de tela:

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

Se você se sente confortável com VBA (Visual Basic for Applications), pode criar uma Função Definida pelo Usuário (UDF) para concatenar múltiplos valores correspondentes. Este método funciona em todas as versões do Excel.

1. Mantenha pressionadas as teclas "ALT + F11" para abrir a janela "Microsoft Visual Basic for Applications".

2. Clique em "Inserir" > "Módulo" e cole o código a seguir na Janela do Módulo.

Código VBA: Pesquisa vertical e concatenação de 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. Em seguida, salve e feche este código, volte para a planilha e insira esta fórmula: =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) em uma célula em branco onde você deseja colocar o resultado, depois arraste a alça de preenchimento para baixo para preencher a fórmula em outras células. Todos os valores correspondentes com base em um dado específico foram retornados em uma única célula com separadores de vírgula e espaço, veja a captura de tela:

Concatenar múltiplos valores correspondentes no Excel pode ser alcançado através de vários métodos, cada um com suas próprias vantagens. Seja usando funções internas como TEXTJOIN e FILTER, ferramentas de terceiros como Kutools, ou soluções personalizadas em VBA, o importante é escolher o método que melhor se adapta à sua versão do Excel, nível de habilidade e requisitos específicos. Se você estiver interessado em explorar mais dicas e truques do Excel, nosso site oferece milhares de tutoriais.