Como usar a função PROCV para retornar múltiplos valores em uma única célula no Excel?

A função PROCV é uma poderosa ferramenta no Excel, mas, por padrão, ela só retorna o primeiro valor correspondente. E se você precisar recuperar todos os valores correspondentes e combiná-los em uma única célula? Essa é uma necessidade comum ao analisar conjuntos de dados ou resumir informações. Neste guia, vamos mostrar passo a passo como retornar múltiplos valores em uma única célula usando fórmulas e recursos úteis.

Retornar múltiplos valores em uma célula com a função TEXTJOIN (Excel 2019 e Office 365)

• Retornar todos os valores correspondentes em uma célula
• Retornar todos os valores correspondentes sem duplicatas em uma célula

Retornar múltiplos valores em uma célula com o Kutools

Retornar múltiplos valores em uma célula com Função Definida pelo Usuário

• Retornar todos os valores correspondentes em uma célula
• Retornar todos os valores correspondentes sem duplicatas em uma célula

Retornar múltiplos valores em uma célula com a função TEXTJOIN (Excel 2019 e Office 365)

Se você tiver uma versão mais recente do Excel, como o Excel 2019 ou o Office 365, há uma nova função - TEXTJOIN, com essa poderosa função, você pode rapidamente procurar e retornar todos os valores correspondentes em uma única célula.

  Retornar todos os valores correspondentes em uma célula

Por favor, aplique a fórmula abaixo em uma célula em branco onde deseja colocar o resultado, depois pressione as teclas Ctrl + Shift + Enter juntas para obter o primeiro resultado, e então 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 na captura de tela abaixo:

  Retornar todos os valores correspondentes sem duplicatas em uma célula

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

Por favor, copie e cole a seguinte fórmula em uma célula em branco, depois pressione as teclas Ctrl + Shift + Enter juntas para obter o primeiro resultado, e então copie esta fórmula para preencher outras células, e você obterá todos os valores correspondentes sem duplicatas conforme mostrado na captura de tela abaixo:

Retornar múltiplos valores em uma célula com o Kutools

Com o recurso "Mesclar Linhas Avançado" do Kutools para Excel, você pode facilmente recuperar múltiplos valores correspondentes em uma única célula — sem fórmulas complexas! Diga adeus às soluções manuais e desbloqueie uma maneira mais eficiente de lidar com suas tarefas de busca no Excel. Vamos explorar como o Kutools para Excel torna tudo isso possível!

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

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

2. Clique em "Kutools" > "Mesclar e Dividir" > "Mesclar Linhas Avançado", veja a captura de tela:

3. Na caixa de diálogo "Mesclar Linhas Avançado" que apareceu:

• Clique no nome da coluna-chave para ser combinada com base nela, e depois clique em "Chave Primária".
• Depois, clique em outra coluna cujos dados você deseja combinar com base na coluna-chave, e clique na lista suspensa do campo "Cálculo", escolha um delimitador para separar os dados combinados na seção "Consolidar".
• Então, clique no botão OK.

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:

Dicas: Se você quiser remover conteúdo duplicado ao mesclar células, basta marcar a opção "Excluir Valores Duplicados" 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 experimente gratuitamente o Kutools para Excel agora!

Retornar múltiplos valores em uma célula com Função Definida pelo Usuário

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

  Retornar todos os valores correspondentes em uma célula

1. Mantenha pressionadas as teclas "ALT + F11", e 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: PROCV para retornar múltiplos 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. Depois, salve e feche este código, volte para a planilha e insira esta fórmula: =CONCATENARSE(E2,$A$2:$A$11, $C$2:$C$11, ", ") em uma célula em branco específica onde deseja colocar o resultado, depois arraste a alça de preenchimento para baixo para obter todos os valores correspondentes em uma célula que você deseja, veja a captura de tela:

  Retornar todos os valores correspondentes sem duplicatas em uma célula

Para ignorar as duplicatas nos valores correspondentes retornados, por favor, use o código abaixo.

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: PROCV e retornar múltiplos valores únicos correspondentes 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 "Microsoft Visual Basic for Applications", e depois, na caixa de diálogo "Referências – VBAProject" que apareceu, marque a opção "Microsoft Scripting Runtime" na caixa de listagem "Referências Disponíveis", veja as capturas de tela:

4. Depois, clique em OK para fechar a caixa de diálogo, salve e feche a janela de código, retorne à planilha, e insira esta fórmula: =PesquisaMultiplaSemRepeticao(E2,$A$2:$C$11,3) em uma célula em branco onde deseja exibir o resultado, e depois arraste a alça de preenchimento para baixo para obter todos os valores correspondentes, veja a captura de tela:

Seja optando por fórmulas como TEXTJOIN combinada com funções de matriz, utilizando ferramentas como Kutools para Excel ou Função Definida pelo Usuário, todos os métodos ajudam a simplificar tarefas complexas de busca. Escolha o método que melhor atende às suas necessidades. Se você estiver interessado em explorar mais dicas e truques do Excel, nosso site oferece milhares de tutoriais.

Mais artigos relacionados: