Como utilizar o VLOOKUP para devolver vários valores numa única célula no Excel?
O VLOOKUP é uma função poderosa no Excel, mas, por predefinição, devolve apenas o primeiro valor correspondente. E se precisar de recuperar **todos** os valores correspondentes e combiná-los numa única célula? Trata-se de um requisito comum ao analisar conjuntos de dados ou resumir informações. Neste guia, explicamos passo a passo métodos para devolver vários valores numa única célula, utilizando tanto fórmulas como funcionalidades integradas do Excel.
Devolver vários valores numa célula com a função TEXTJOIN (Excel 2019 e Office 365)
- Devolver todos os valores correspondentes numa única célula
- Devolver todos os valores correspondentes sem duplicados numa única célula
Devolver vários valores numa célula com o Kutools
Devolver vários valores numa célula com uma Função Definida pelo Utilizador
- Devolver todos os valores correspondentes numa única célula
- Devolver todos os valores correspondentes sem duplicados numa única célula
Devolver vários valores numa célula com a função TEXTJOIN (Excel 2019 e Office 365)
Se tiver uma versão mais recente do Excel, como o Excel 2019 ou o Office 365, existe uma nova função — TEXTJOIN — que, graças ao seu poder, lhe permite usar o VLOOKUP para encontrar rapidamente e devolver todos os valores correspondentes numa única célula.
Devolver todos os valores correspondentes numa única célula
Aplique a fórmula seguinte numa célula vazia onde pretende colocar o resultado, prima em seguida as teclas Ctrl + Shift + Enter simultaneamente para obter o primeiro resultado e arraste depois a alça de preenchimento até à célula onde pretende utilizar esta fórmula; assim, obterá todos os valores correspondentes, conforme ilustrado na imagem seguinte:
Devolver todos os valores correspondentes sem duplicados numa única célula
Se pretender obter todos os valores correspondentes com base nos dados de pesquisa, sem duplicados, a fórmula seguinte poderá ajudá-lo.
Copie e cole a fórmula seguinte numa célula vazia, prima em seguida as teclas Ctrl + Shift + Enter simultaneamente para obter o primeiro resultado e copie depois esta fórmula para preencher outras células; assim, obterá todos os valores correspondentes sem duplicados, conforme ilustrado na imagem seguinte:
Devolver vários valores numa célula com o Kutools
Com a funcionalidade «Mesclar Linhas Avançado» do Kutools para Excel, recupere facilmente vários valores correspondentes numa única célula — sem fórmulas complexas! Diga adeus às soluções manuais e descubra uma forma mais eficiente de gerir as suas tarefas de procura no Excel. Veja como o Kutools para Excel torna isto possível!
Após instalar o Kutools para Excel, proceda da seguinte forma:
1. Selecione o intervalo de dados cujos valores de uma coluna pretende combinar com base noutra coluna.
2. Clique em «Kutools» > «Mesclar e Dividir» > «Mesclar Linhas Avançado», conforme ilustrado:
3. Na caixa de diálogo «Mesclar Linhas Avançado» que aparece:
- Clique no nome da coluna chave com base na qual pretende combinar e, de seguida, clique em «Chave primária».
- Em seguida, clique noutra coluna cujos dados pretende combinar com base na Coluna Chave, aceda à lista pendente no campo «Operação» e escolha um separador para unir os dados combinados na secção «Combinar».
- Depois, clique no botão OK.
Todos os valores correspondentes de outra coluna, com base num valor comum, são combinados numa única célula. Veja as imagens:
 |  |  |
Dicas: Se pretender remover conteúdos duplicados ao fundir células, basta marcar a opção «Excluir valores duplicados» na caixa de diálogo. Desta forma, apenas as entradas únicas são combinadas numa única célula, garantindo dados mais limpos e organizados — sem qualquer esforço extra. Veja as imagens:
 | |  |
Transfira e experimente gratuitamente o Kutools para Excel agora!
Devolver vários valores numa célula com uma Função Definida pelo Utilizador
A função TEXTJOIN acima indicada está disponível apenas no Excel 2019 e no Office 365; se utilizar versões anteriores do Excel, terá de recorrer a códigos para concluir esta tarefa.
Devolver todos os valores correspondentes numa única célula
1. Prima e mantenha premidas as teclas «ALT + F11» para abrir a janela do Microsoft Visual Basic para Aplicações.
2. Clique em «Inserir» > «Módulo» e cole o código seguinte na janela do módulo.
Código VBA: Procurar com VLOOKUP e devolver vários valores numa 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, guarde e feche este código, regresse à folha de cálculo e introduza a seguinte fórmula: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") numa célula vazia onde pretenda obter o resultado; depois, arraste a alça de preenchimento para baixo para obter todos os valores correspondentes numa única célula, conforme ilustrado:
Devolver todos os valores correspondentes sem duplicados numa única célula
Para ignorar duplicados nos valores correspondentes devolvidos, utilize o código seguinte.
1. Prima e mantenha premidas as teclas «Alt + F11» para abrir a janela do Microsoft Visual Basic para Aplicações.
2. Clique em «Inserir» > «Módulo» e cole o código seguinte na janela do módulo.
Código VBA: Procurar com VLOOKUP e devolver vários valores correspondentes únicos numa 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 do «Microsoft Visual Basic para Aplicações» que se abre e, na caixa de diálogo «Referências – VBAProject» exibida, selecione a opção «Microsoft Scripting Runtime» na lista «Referências disponíveis», conforme ilustrado:
 | |  |
4. Em seguida, clique em OK para fechar a caixa de diálogo, guarde e feche a janela do código, regresse à folha de cálculo e introduza esta fórmula: =MultipleLookupNoRept(E2,$A$2:$C$11,3) numa célula vazia onde pretende apresentar o resultado; depois, arraste a alça de preenchimento para baixo para obter todos os valores correspondentes, conforme ilustrado:
Quer opte por fórmulas como a TEXTJOIN combinada com funções matriciais, quer recorra a ferramentas como o Kutools para Excel ou a funções definidas pelo utilizador, todas estas abordagens simplificam tarefas complexas de procura. Escolha o método que melhor se adapta às suas necessidades. Se estiver interessado em explorar mais dicas e truques do Excel, o nosso site disponibiliza milhares de tutoriais.
Mais artigos relacionados:
- Função VLOOKUP com exemplos básicos e avançados
- No Excel, a função VLOOKUP é uma ferramenta poderosa para a maioria dos utilizadores, permitindo procurar um valor na coluna mais à esquerda de um intervalo de dados e devolver o valor correspondente na mesma linha de uma coluna por si especificada. Este tutorial mostra como utilizar a função VLOOKUP com exemplos básicos e avançados no Excel.
- Devolver vários valores correspondentes com base num ou em vários critérios
- Normalmente, encontrar um valor específico e obter o item correspondente é algo simples para a maioria de nós com a função VLOOKUP. Mas já alguma vez tentou devolver vários valores correspondentes com base num ou mais critérios? Neste artigo, apresento-lhe algumas fórmulas para resolver esta tarefa complexa no Excel.
- Procurar com VLOOKUP e devolver vários valores verticalmente
- Normalmente, pode utilizar a função VLOOKUP para obter o primeiro valor correspondente, mas por vezes pretende devolver todos os registos que correspondam a um determinado critério. Neste artigo, explicamos como usar o VLOOKUP para encontrar e devolver todos os valores correspondentes — verticalmente, horizontalmente ou numa única célula.
- Procurar com VLOOKUP e devolver vários valores a partir de uma lista pendente
- No Excel, como utilizar o VLOOKUP para procurar e devolver vários valores correspondentes a partir de uma lista pendente — ou seja, ao selecionar um item na lista, todos os seus valores relacionados aparecem imediatamente? Neste artigo, apresento-lhe a solução passo a passo.
As Melhores Ferramentas de Produtividade para o Office
Potencie as suas competências no Excel com Kutools para Excel e experimente uma eficiência como nunca antes.Kutools para Excel Oferece mais de 300 funcionalidades avançadas para impulsionar a produtividade e Economizar Tempo.Clique aqui para obter a funcionalidade de que mais precisa...
Office Tab Traz uma interface com separadores para o Office e torna o seu trabalho muito mais fácil
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
Todos os suplementos Kutools — num único instalador.
Kutools for Office reúne suplementos para Excel, Word, Outlook e PowerPoint, além do Office Tab Pro, sendo a solução ideal para equipas que trabalham em várias aplicações do Office.
- Pacote tudo-em-um— suplementos para Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Um instalador, uma licença— configuração em minutos (pronto para MSI)
- Funciona melhor em conjunto— produtividade simplificada em todas as aplicações do Office
- Teste gratuito de 30 dias com todas as funcionalidades— sem registo, sem cartão de crédito
- Melhor relação qualidade-preço— poupe face à compra individual dos suplementos