Como vlookup e concatenar vários valores correspondentes no Excel?
Como todos nós sabemos, o Vlookup A função no Excel pode nos ajudar a pesquisar um valor e retornar os dados correspondentes em outra coluna, mas em geral, ela só pode obter o primeiro valor relativo se houver vários dados correspondentes. Neste artigo, falarei sobre como vlookup e concatenar vários valores correspondentes em apenas uma célula ou uma lista vertical.
Vlookup e retorna vários valores correspondentes verticalmente com a fórmula
Vlookup e concatenar vários valores correspondentes em uma célula com a função definida pelo usuário
Vlookup e concatenar vários valores correspondentes em uma célula com o Kutools para Excel
Vlookup e retorna vários valores correspondentes verticalmente com a fórmula
Supondo que eu tenha o seguinte intervalo de dados, para obter todos os valores correspondentes com base em um valor específico verticalmente, conforme mostrado a seguir, você pode aplicar uma fórmula de matriz.
1. Insira esta fórmula: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") em uma célula em branco onde você deseja colocar o resultado, por exemplo, E2, e pressione Ctrl + Shift + Enter juntas para obter a base de valor relativa em um critério específico, consulte a captura de tela:
Note: Na fórmula acima:
A1: A16 é o intervalo da coluna que contém o valor específico que você deseja procurar;
D2 indica o valor específico que você deseja visualizar;
B1: B16 é o intervalo de colunas a partir do qual você deseja retornar os dados correspondentes;
$ 1: $ 16 indica a referência de linhas dentro do intervalo.
2. Em seguida, selecione a célula E2 e arraste a alça de preenchimento para baixo até as células até obter células em branco, e todos os valores correspondentes são listados na coluna, conforme mostrado a seguir:
Vlookup e concatenar vários valores correspondentes em uma célula com a função definida pelo usuário
Em vez de obter os valores relativos verticalmente, às vezes, você deseja os valores correspondentes em uma célula e concatená-los com um separador específico. Neste caso, a seguinte Função Definida pelo Usuário pode lhe fazer um favor.
1. Mantenha pressionada a 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 Módulo Janela.
Código VBA: Vlookup e concatena vários valores correspondentes em uma célula
Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
If x = lookupval Then
result = result & " " & x.Offset(0, indexcol - 1)
End If
Next x
CusVlookup = result
End Function
3. Em seguida, salve e feche este código, volte para a planilha e insira esta fórmula: = cusvlookup (D2, A1: B16,2) em uma célula em branco onde você deseja colocar o resultado e pressione Entrar chave, todos os valores correspondentes com base em dados específicos foram retornados em uma célula com separador de espaço, consulte a captura de tela:
Note: Na fórmula acima: D2 indica os valores da célula que você deseja pesquisar, A1: B16 é o intervalo de dados que você deseja buscar os dados, o número 2 é o número da coluna da qual o valor correspondente deve ser retornado, você pode alterar essas referências conforme sua necessidade.
Vlookup e concatenar vários valores correspondentes em uma célula com o Kutools para Excel
Se você tem Kutools for Excel, Com o seu Combinar linhas avançadas recurso, você pode rapidamente terminar este trabalho com facilidade. Este recurso pode ajudá-lo a combinar todos os valores correspondentes com delimitadores específicos com base nos mesmos dados em outra coluna.
| Kutools for Excel : com mais de 300 suplementos úteis do Excel, grátis para testar sem limitação em 30 dias. |
Depois de instalar Kutools for Excel, faça o seguinte:
1. Selecione o intervalo de dados que você deseja obter os valores correspondentes com base nos dados específicos.
2. Então clique Kutools > Unir e dividir > Combinar linhas avançadas, veja a captura de tela:
3. No Combinar linhas avançadas caixa de diálogo, clique no nome da coluna que deseja combinar com base e clique em Chave primária botão, veja a captura de tela:
4. Em seguida, clique em outro nome de coluna para a qual deseja retornar os valores correspondentes e clique em Combinar para escolher um separador para separar os valores combinados, consulte a captura de tela:
5. E, em seguida, clique em Ok botão, todos os valores correspondentes com base nos mesmos valores foram combinados com um separador específico, consulte as capturas de tela:
 |
 |
 |
Baixe e teste grátis Kutools para Excel agora!
Demonstração: Vlookup e concatena vários valores correspondentes em uma célula com Kutools para Excel
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!
