提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何在Excel中查找和连接多个相应的值?

众所周知, VLOOKUP Excel中的函数可以帮助我们查找一个值并返回另一列中的相应数据,但通常情况下,如果有多个匹配数据,它只能获得第一个相对值。 在本文中,我将讨论如何在一个单元格或垂直列表中查找和连接多个相应的值。

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

使用用户定义函数在单元格中查找并连接多个匹配值

使用Kutools for Excel在一个单元格中查找并连接多个匹配值


在单元格中查找并连接多个匹配值:

您订购的 Kutools for Excel先进的组合行 utiltiy,你可以快速的将多个重复的行组合成一个基于关键列的记录,也可以对其他列应用一些计算,如sum,average,count等。

  • 1。 指定您想要组合其他列的键列;
  • 2。 为您的组合数据选择分隔符。

doc根据标准10合并文本

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!



假设,我有以下范围的数据,以垂直方向获取基于特定值的所有对应值,如下图所示,您可以应用数组公式。

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 表示你想查找的具体值;

B1:B16 是您想要返回相应数据的列范围;

$ 1:$ 16 指示范围内的行参考。

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

doc vlookup连接3


有时,您不希望垂直获取相对值,而是希望将匹配的值放入一个单元格中,并将它们与特定的分隔符连接起来。 在这种情况下,以下用户定义函数可能会对您有所帮助。

1. 按住 ALT + F11 键打开 Microsoft Visual Basic for Applications 窗口。

2。 点击 插页 > 模块,并将以下代码粘贴到 模块 窗口。

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

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
        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加载项,在60天免费试用没有限制.

安装后 Kutools for Excel请按照以下步骤进行:

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

2。 然后点击 Kutools > 内容 > 先进的组合行,看截图:

doc vlookup连接5

3。 在 先进的组合行 对话框中,单击要合并的列名称,然后单击 首要的关键 按钮,看截图:

doc vlookup连接6

4。 然后点击您想要返回匹配值的另一个列名称,然后单击 结合 要选择一个分隔符来分隔组合值,请参见截图:

doc vlookup连接7

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

doc vlookup连接8  2 doc vlookup连接9

立即下载并免费试用Kutools for Excel!


Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能30天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    sym-john · 2 months ago
    Is there any way to get the unique "name" for "class1"
  • To post as a guest, your comment is unpublished.
    Russell · 6 months ago
    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 |
    • To post as a guest, your comment is unpublished.
      Jeff F · 2 months ago
      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.
  • To post as a guest, your comment is unpublished.
    jeff · 7 months ago
    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)," "," / ")
    • To post as a guest, your comment is unpublished.
      skyyang · 7 months ago
      Hi, jeff,
      Thanks for your sharing, you must be a warmhearted man.
  • To post as a guest, your comment is unpublished.
    Dana Rohde · 1 years ago
    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!!
  • To post as a guest, your comment is unpublished.
    Jacob Nelson · 1 years ago
    Is there a way to delete the duplicate values in the concatenate?
  • To post as a guest, your comment is unpublished.
    Sree Kanth · 1 years ago
    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
  • To post as a guest, your comment is unpublished.
    Brett Bieker · 2 years ago
    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
    • To post as a guest, your comment is unpublished.
      Anand · 1 years ago
      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
  • To post as a guest, your comment is unpublished.
    slohman · 3 years ago
    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
  • To post as a guest, your comment is unpublished.
    Deepan Saha · 3 years ago
    How to get the result. Please help.

    data data1 result
    a 1 a1
    b 2 a2
    c b1
    b2
    c1
    c2