Como vlookup retornar vários valores em uma célula no Excel?
Normalmente, no Excel, quando você usa a função PROCV, se houver vários valores para atender aos critérios, você pode obter apenas o primeiro. Mas, às vezes, você deseja retornar todos os valores correspondentes que atendem aos critérios em uma célula, como mostrado a seguir na imagem, como você poderia resolver isso?
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 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 um recurso útil
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 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. Segure o ALT + F11 chaves, e abre o Microsoft Visual Basic para Aplicações janela.
2. Clique inserção > Móduloe cole o seguinte código no Janela 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. Segure o Alt + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.
2. Clique inserção > Móduloe cole o seguinte código no Janela 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 no aberto Microsoft Visual Basic para Aplicações janela e, em seguida, no 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:
Vlookup para retornar vários valores em uma célula com um recurso útil
Se você tem nosso Kutools for Excel, Com o seu Combinar linhas avançadas recurso, você pode rapidamente mesclar ou combinar as linhas com base no mesmo valor e fazer alguns cálculos conforme necessário.
Depois de instalar Kutools for Excel, faça o seguinte:
1. Selecione o intervalo de dados que você deseja combinar com 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 estalou para fora 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 em Combinar para escolher um separador para separar os dados combinados.
4. Então clique OK botão, e você obterá os seguintes resultados:
Baixe e teste grátis Kutools para Excel agora!
Mais artigos relativos:
- Função VLOOKUP com alguns exemplos básicos e avançados
- No Excel, a função VLOOKUP é uma função poderosa para a maioria dos usuários do Excel, que é usada para procurar um valor na extremidade esquerda do intervalo de dados e retornar um valor correspondente na mesma linha de uma coluna que você especificou. Este tutorial fala sobre como usar a função VLOOKUP com alguns exemplos básicos e avançados no Excel.
- Retorna vários valores correspondentes com base em um ou vários critérios
- Normalmente, pesquisar um valor específico e retornar o item correspondente é fácil para a maioria de nós usando a função VLOOKUP. Mas, você já tentou retornar vários valores correspondentes com base em um ou mais critérios? Neste artigo, apresentarei algumas fórmulas para resolver essa tarefa complexa no Excel.
- Vlookup e retornar vários valores verticalmente
- Normalmente, você pode usar a função Vlookup para obter o primeiro valor correspondente, mas, às vezes, você deseja retornar todos os registros correspondentes com base em um critério específico. Neste artigo, falarei sobre como vlookup e retornar todos os valores correspondentes verticalmente, horizontalmente ou em uma única célula.
- Vlookup e retornar vários valores da lista suspensa
- No Excel, como você poderia visualizar e retornar vários valores correspondentes de uma lista suspensa, o que significa que quando você escolhe um item da lista suspensa, todos os seus valores relativos são exibidos de uma vez. Neste artigo, irei apresentar a solução passo a passo.
Melhores ferramentas de produtividade de escritório
Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para obter o recurso que você mais precisa...
Office Tab traz interface com guias para o Office e torna seu trabalho muito mais fácil
- Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
- Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!