跳到主要内容

如何在Excel中vlookup并连接多个对应值？

众所周知， VLOOKUP Excel中的函数可以帮助我们查找一个值并在另一列中返回相应的数据，但是通常，如果存在多个匹配数据，则它只能获取第一个相对值。 在本文中，我将讨论如何仅在一个单元格或垂直列表中进行vlookup并串联多个对应的值。

Vlookup并使用公式垂直返回多个匹配值

Vlookup并使用用户定义的函数连接单元格中的多个匹配值

使用 Kutools for Excel Vlookup 并连接单元格中的多个匹配值

假设，我有以下数据范围，要根据特定的值垂直获取所有相应的值（如以下屏幕截图所示），可以应用数组公式。

doc vlookup串联1

1。 输入以下公式： =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))),"") 放入要放入结果的空白单元格，例如E2，然后按 Ctrl + Shift + Enter 键一起获得基于特定条件的相对值，请参见屏幕截图：

doc vlookup串联2

备注：在上式中：

A1：A16 是包含要查找的特定值的列范围；

D2 指示您要vlookup的特定值；

B1：B16 是您要从中返回相应数据的列范围；

$ 1：$ 16 指示范围内的行引用。

2。 然后选择单元格E2，并将填充手柄向下拖动到这些单元格，直到获得空白单元格，所有匹配的值都列在该列中，如以下屏幕截图所示：

doc vlookup串联3

有时，您希望将匹配的值合并到一个单元格中，并使用特定的分隔符将它们串联起来，而不是垂直获取相对值。 在这种情况下，以下用户定义功能可能会有所帮助。

1. 按住 ALT + F11 键打开 Microsoft Visual Basic应用程序 窗口。

2。 点击 插页 > 模块，然后将以下代码粘贴到 模块 窗口。

VBA代码：Vlookup并连接单元格中的多个匹配值

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。 然后保存并关闭此代码，返回到工作表，然后输入以下公式： = cusvlookup（D2，A1：B16,2） 放入要放入结果的空白单元格，然后按 输入 键，基于特定数据的所有相应值都已返回到一个带有空格分隔符的单元格中，请参见屏幕截图：

doc vlookup串联4

备注：在上式中： D2 表示您要查找的单元格值， A1：B16 是您要获取数据的数据范围，数字 2 是要从中返回匹配值的列号，您可以根据需要更改这些引用。

如果你有 Kutools for Excel，其 高级组合行 功能，您可以轻松轻松地完成这项工作。 此功能可帮助您基于另一列中的相同数据，将所有匹配值与特定定界符组合在一起。

Kutools for Excel : 带有300多个便捷的Excel加载项，可以在30天内免费试用.
前往下载
30天免费试用购买
贝宝/ MyCommerce

安装后 Kutools for Excel，请执行以下操作：

1. 根据特定数据选择要获取相应值的数据范围。

2。 然后点击 库工具 > 合并与拆分 > 高级组合行，请参见屏幕截图：

3。 在 高级组合行 对话框中，单击要合并的列名，然后单击 首要的关键 按钮，请参见屏幕截图：

doc vlookup串联6

4。 然后，单击要返回匹配值的另一个列名，然后单击“确定”。 结合 选择一个分隔符以分隔组合值，请参见屏幕截图：

doc vlookup串联7

5。 然后点击 Ok 按钮，所有基于相同值的对应值已与特定的分隔符组合在一起，请参见屏幕截图：

doc vlookup串联8 2 doc vlookup串联9

 立即下载和免费试用Excel的Kutools！

Kutools for Excel：具有300多个方便的Excel加载项，可以在30天内免费试用，没有任何限制。 立即下载并免费试用！

最佳办公生产力工具

🤖 Kutools 人工智能助手：基于以下内容彻底改变数据分析： 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup  |   多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧   |  工作簿和工作表管理器   |  资源库 （自动文本）   |  日期选择器   |  合并工作表   |  加密/解密单元格   |  按列表发送电子邮件   |  超级筛选   |   特殊过滤器 （过滤粗体/斜体/删除线...）...
前 15 个工具集:  12 文本 工具 (添加文本, 删除字符，...）   |   50+ 图表 类型 (甘特图，...）   |   40+ 实用 公式 (根据生日计算年龄，...）   |   19 插入 工具 (插入二维码, 从路径插入图片，...）   |   12 转化 工具 (小写金额转大写, 货币兑换，...）   |   7 合并与拆分 工具 (高级组合行, 分裂细胞，...）   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能，体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述

Office Tab 为 Office 带来选项卡式界面，让您的工作更加轻松

  • 在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50％，并减少数百次鼠标单击！
Comments (16)
No ratings yet. Be the first to rate!
sym-john
sym-john
This comment was minimized by the moderator on the site
Is there any way to get the unique "name" for "class1"
Reply
0
0
skyyang
skyyang     sym-john
This comment was minimized by the moderator on the site
Hello, sym-john,
Maybe the below article can solve your problem, please view it:
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html
Reply
Report
0
0
Russell
Russell
This comment was minimized by the moderator on the site
This is working great for me - is there anyway to change it that it checks if the cell contains rather than a complete match? Basically I have a list of tasks where:
Column A: Dependencies (eg 10003 10004 10008)
Column B: Task Reference (eg 10001)
Column C: Dependent Tasks (the column for the formula result) - where it would lookup the task reference to see which rows contain it in Column A, and then list the Task Reference of those tasks.

E.g:

Row | Column A | Column B | Column C
1 | | 10001 | 10002 10003
2 | 10001 | 10002 | 10003
3 | 10001 10002 | 10003 |
Reply
0
0
Jeff F
Jeff F     Russell
This comment was minimized by the moderator on the site
you would want to use the Instr() function which will check for something in a string of text in a cell. You can also use Left() and Right() if you are looking for the starting or ending details.
Reply
0
0
jeff
jeff
This comment was minimized by the moderator on the site
The cusVlookup worked great for me. Another way to have a different separator is to wrap in two substitute functions. The first (from inside to out) replaces the first space with no space, the second replaces all other spaces with a " / " in mine. Could use "," if you want commas.
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,2)," ","",1)," "," / ")

Also, if your lookup value isn't the first column, you can use 0 or negative numbers to go to column to the left.
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,-1)," ","",1)," "," / ")
Reply
0
0
skyyang
skyyang     jeff
This comment was minimized by the moderator on the site
Hi, jeff,
Thanks for your sharing, you must be a warmhearted man.
Reply
Report
0
0
Dana Rohde
Dana Rohde
This comment was minimized by the moderator on the site
I have to say, I have been trying to get a formula for combining multiple values and returning them to a single cell for 2 days now. This "How To" has saved me!! Thank you SO much! I would never have gotten it without your Module!
I do have 2 questions though. I have the deliminator as a comma instead of a space and because of that it starts out with a comma. Is there a way to prevent the start comma but keep the rest?
My second question is; When I use the fill handle it changes the range values as well as the cell value I want to look up. I want it to continue to change the cell number I want to look up but keep the same range values. How can I make this happen?

Thank you so much for your help!!
Reply
0
0
Jacob Nelson
Jacob Nelson
This comment was minimized by the moderator on the site
Is there a way to delete the duplicate values in the concatenate?
Reply
0
0
skyyang
skyyang     Jacob Nelson
This comment was minimized by the moderator on the site
Hello, Jacob,
May be the following article can help you to solve your problem.
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html

Please try, hope it can help you!
Reply
Report
0
0
carmela
carmela     skyyang
This comment was minimized by the moderator on the site
Is there a way to list the duplicate values only once, using the vba code and formula above? I am not sure where to put the countif>1 statement in the formula bar, or in the vba itself. Please help
Reply
0
0
Jerry
Jerry     carmela
This comment was minimized by the moderator on the site
you can add two extra condition to skip blank cells and to skip duplicates:For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
If ConcatenateRange.Cells(i).Value <> "" Then 'SKIP BANKS
If InStr(xResult, ConcatenateRange.Cells(i).Value) = 0 Then 'SKIP IF FOUND DUPLICATE
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
End If
End If
Next i
Reply
0
0
sreetechnical@gmail.com
This comment was minimized by the moderator on the site
This is amazing but i am looking for something else, i have a table with RollNo StudentName sub1, sub2, sub3 ... Total Result, When I enter Rollnumber it should give a result like "SName Sub1 64, sub2 78,... Total 389, Result pass", is it possible
Reply
Report
0
0
Brett Bieker
Brett Bieker
This comment was minimized by the moderator on the site
Loved the function for Excel 2013 but amended it slightly to change the separating character to ";" instead of " " and then remove the prefixed ";" from the concantenated values Results matching values in my example would have ;result01 or ;result01;result02 . Added the extra If Left(xResult, 1) = ";" to remove any extra ";" at the beginning of the string if it is the 1st character. I'm sure there is a neater way of doing it but it worked for me. :) Function CusVlookup(pValue As String, pWorkRng As Range, pIndex As Long) Dim rng As Range Dim xResult As String xResult = "" For Each rng In pWorkRng If rng = pValue Then xResult = xResult & ";" & rng.Offset(0, pIndex - 1) If Left(xResult, 1) = ";" Then xResult = MID(xResult,2,255) End If End If Next CusVlookup = xResult End Function
Reply
0
0
Anand
Anand     Brett Bieker
This comment was minimized by the moderator on the site
Make if condition for result if empty.

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice 20151118
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
If x = lookupval Then
If Not result = "" Then
result = result & " " & x.Offset(0, indexcol - 1)
Else
result = x.Offset(0, indexcol - 1)
End If
Next x
CusVlookup = result
End Function
Reply
0
0
slohman
slohman
This comment was minimized by the moderator on the site
When using the cusvlookup is there a way to add the last name as well with a comma in between that might appear in Column C
Reply
0
0
Deepan Saha
Deepan Saha
This comment was minimized by the moderator on the site
How to get the result. Please help. data data1 result a 1 a1 b 2 a2 c b1 b2 c1 c2
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations