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

VLOOKUP é uma função poderosa no Excel, mas, por padrão, ela retorna apenas o primeiro valor correspondente. E se você precisar recuperar todos os valores correspondentes e combiná-los em uma célula? Este é um requisito comum ao analisar conjuntos de dados ou resumir informações. Neste guia, mostraremos métodos passo a passo para retornar vários valores em uma única célula usando fórmulas e recursos úteis.

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

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

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 função definida pelo usuário

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

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:

  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:

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

Com Kutools for Excel's Combinar linhas avançadas recurso, você pode facilmente recuperar vários valores correspondentes em uma única célula — sem necessidade de fórmulas complexas! Diga adeus às soluções alternativas manuais e desbloqueie uma maneira mais eficiente de lidar com suas tarefas de pesquisa no Excel. Vamos explorar como Kutools for Excel torna tudo possível!

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

1. Selecione o intervalo de dados no qual você deseja combinar 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 pop out 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 na lista suspensa do Divisão de campo, escolha um separador para separar os dados combinados dos Combinar seção.
• Então clique OK botão.

Todos os valores correspondentes de outra coluna, com base no mesmo valor, são combinados em uma única célula. Veja as capturas de tela:

Tips:Se você quiser remover conteúdo duplicado ao mesclar células, basta marcar a caixa Excluir valores duplicados opção na caixa de diálogo. Isso garante que apenas entradas únicas sejam combinadas em uma única célula, tornando seus dados mais limpos e organizados sem nenhum esforço extra. Veja as capturas de tela:

Baixe e teste grátis Kutools para Excel agora!

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. Mantenha pressionado o ALT + F11 chaves, e abre o Microsoft Visual Basic para Aplicações janela.

2. Clique inserção > Móduloe cole o código a seguir na janela do 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:

  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. Mantenha pressionado o Alt + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.

2. Clique inserção > Móduloe cole o código a seguir na janela do 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ências na janela aberta do Microsoft Visual Basic for Applications e, em seguida, na janela pop-out 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:

Quer você opte por fórmulas como TEXTJOIN combinadas com funções de matriz, aproveite ferramentas como Kutools para Excel ou função definida pelo usuário, todas as abordagens ajudam a simplificar tarefas de pesquisa complexas. Escolha o método que melhor se adapta às suas necessidades. Se você estiver interessado em explorar mais dicas e truques do Excel, nosso site oferece milhares de tutoriais.

Mais artigos relativos: