跳到主要内容

如何在Excel中的一个单元格中通过vlookup返回多个值?

通常,在Excel中,当您使用VLOOKUP函数时,如果有多个值与条件匹配,则只需获取第一个即可。 但是,有时,您希望将符合条件的所有相应值返回到一个单元格中,如下面的屏幕截图所示,如何解决呢?

Vlookup使用TEXTJOIN函数将多个值返回到一个单元格中(Excel 2019和Office 365)

Vlookup使用用户定义的函数将多个值返回到一个单元格

Vlookup通过有用的功能将多个值返回到一个单元格


Vlookup使用TEXTJOIN函数将多个值返回到一个单元格中(Excel 2019和Office 365)

如果您拥有较高版本的Excel(例如Excel 2019和Office 365),则有一个新功能- 文字加入,借助这一强大的功能,您可以快速进行vlookup并将所有匹配的值返回到一个单元格中。

Vlookup将所有匹配的值返回到一个单元格

请将以下公式应用于要放入结果的空白单元格,然后按 Ctrl + Shift + Enter 键一起获得第一个结果,然后将填充手柄向下拖动到要使用此公式的单元格,您将获得所有对应的值,如下图所示:

=TEXTJOIN(",",TRUE,IF($A$2:$A$11=E2,$C$2:$C$11,""))

请注意: 在上式中 A2:A11 查找范围包含查找数据, E2 是查找值, C2:C11 是您要从中返回匹配值的数据范围,“,“是用于分隔多个记录的分隔符。

Vlookup将所有没有重复的匹配值返回到一个单元格

如果要基于查找数据返回所有匹配值而不重复,则以下公式可能会对您有所帮助。

请复制以下公式并将其粘贴到空白单元格中,然后按 Ctrl + Shift + Enter 键在一起以获得第一个结果,然后复制此公式以填充其他单元格,您将获得所有对应的值,而无需使用重复的值,如下面的屏幕截图所示:

=TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$11, IF(E2=$A$2:$A$11, $C$2:$C$11, ""), 0),"")=MATCH(ROW($C$2:$C$11), ROW($C$2:$C$11)), $C$2:$C$11, ""))

请注意: 在上式中 A2:A11 查找范围包含查找数据, E2 是查找值, C2:C11 是您要从中返回匹配值的数据范围,“,“是用于分隔多个记录的分隔符。

Vlookup使用用户定义的函数将多个值返回到一个单元格

上面的TEXTJOIN函数仅适用于Excel 2019和Office 365,如果您具有其他较低的Excel版本,则应使用一些代码来完成此任务。

Vlookup将所有匹配的值返回到一个单元格

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

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

VBA代码:Vlookup将多个值返回到一个单元格

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。 然后保存并关闭此代码,返回到工作表,然后输入以下公式: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") 放入要放置结果的特定空白单元格中,然后向下拖动填充手柄以在所需的一个单元格中获取所有对应的值,请参见屏幕截图:

请注意: 在上式中 A2:A11 查找范围包含查找数据, E2 是查找值, C2:C11 是您要从中返回匹配值的数据范围,“,“是用于分隔多个记录的分隔符。

Vlookup将所有没有重复的匹配值返回到一个单元格

要忽略返回的匹配值中的重复项,请使用以下代码。

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

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

VBA代码:Vlookup并将多个唯一匹配的值返回到一个单元格中

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。 插入代码后,然后单击 工具 > 参考资料 在打开 Microsoft Visual Basic应用程序 窗口,然后在弹出 参考– VBAProject 对话框,检查 Microsoft脚本运行时 在选项 可用参考 列表框,请参阅屏幕截图:

4。 然后点击 OK 关闭对话框,保存并关闭代码窗口,返回到工作表,然后输入以下公式: =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:

请注意: 在上式中 A2:C11 是您要使用的数据范围, E2 是查找值,数字 3 是包含返回值的列号。

Vlookup通过有用的功能将多个值返回到一个单元格

 如果您有我们的 Kutools for Excel,其 高级组合行 功能,您可以根据相同的值快速合并或合并行,并根据需要进行一些计算。

请注意:申请这个 高级组合行,首先,您应该下载 Kutools for Excel,然后快速轻松地应用该功能。

安装后 Kutools for Excel,请执行以下操作:

1。 选择要根据另一列合并一个列数据的数据范围。

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

3。 在弹出 高级组合行 对话框:

  • 单击要基于其组合的键列名称,然后单击 首要的关键
  • 然后单击要基于键列合并其数据的另一列,然后单击 结合 选择一个分隔符以分隔组合数据。

4. 然后点击 OK 按钮,您将获得以下结果:

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


更多相关文章:

  • VLOOKUP函数以及一些基本和高级示例
  • 在Excel中,VLOOKUP函数对于大多数Excel用户而言是一项功能强大的函数,用于在数据范围的最左侧查找值,并在您指定的列的同一行中返回匹配值。 本教程通过Excel中的一些基本示例和高级示例讨论如何使用VLOOKUP函数。
  • 根据一个或多个条件返回多个匹配值
  • 通常,使用VLOOKUP函数对我们大多数人来说查找特定值并返回匹配项很容易。 但是,您是否曾经尝试过根据一个或多个条件返回多个匹配值? 在本文中,我将介绍一些解决Excel中复杂任务的公式。
  • Vlookup并垂直返回多个值
  • 通常,您可以使用Vlookup函数来获取第一个对应的值,但是有时您希望基于特定条件返回所有匹配的记录。 本文,我将讨论如何进行vlookup并将所有匹配值垂直,水平或返回到单个单元格中。
  • Vlookup并从下拉列表中返回多个值
  • 在Excel中,如何从下拉列表中进行vlookup并返回多个相应的值,这意味着当您从下拉列表中选择一项时,它的所有相对值会立即显示。 本文,我将逐步介绍解决方案。

最佳办公生产力工具

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

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

kte选项卡201905


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

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (43)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I have created a problem.
"I" have combined a "Textjoin" end "Vlookup" to return multiple values in to one single cell.
My problem is that the formula have to have an exact value to look for and I want it to lookup an "almost" match or Partial match.

Example: I have made a schedule how we ate going to work and a D1 is working from 07:30-16:00. And to lookup D1 is not the problem, the problem is that my boss sometimes puts other stuff togeather with the D1... Like "D1 +" or "D1 meeting".
Since my formula only lookup "D1" it misses for example the "D1 +".

My formula (that I have gotten from the web) =TEXTJOIN(" och ";SANT;OM($B$3:$B$15=$C$22:$F$22;$A$3:$A$15;""))It´s in swedish so "SANT" is "TRUE" and "OM" is "IF".

How can I make the formula lookup all the cells that have some form of "D1" in it and return all those to the same cell?
No matter if it says "D1 +" or "D1 meeting" or whatever.
The reson I want this, is because the boss always leave "D1" but can add other text behind the "D1"... and just because of that, my boss messes up my formula.
This comment was minimized by the moderator on the site
Hi!
This is a great VBA-Code which could help me a lot.But when I start the Function MultipleLookupNoRept Excel crashs...I´ve got a Dataset with about 6.000 Rows (Excel 2013).... is this too much for the VBA Function?

Thanks!
This comment was minimized by the moderator on the site
Hello Mr.XXL,Sorry to hear that. The row limit for Excel 2013 is 1048576. Therefore, maybe the VBA code is the reason for the crash.
Anyway, I would love to offer you another VBA code for Vlookup To Return All Matching Values Without Duplicates Into One Cell. Please use the VBA code below:
Option Explicit

Function Lookup_concat(Search_string As String, _
Search_in_col As Range, Return_val_col As Range)

Dim i As Long
Dim temp() As Variant
Dim result As String
ReDim temp(0)

For i = 1 To Search_in_col.Count
If Search_in_col.Cells(i, 1) = Search_string Then
temp(UBound(temp)) = Return_val_col.Cells(i, 1).Value
ReDim Preserve temp(UBound(temp) + 1)
End If
Next

If temp(0) <> "" Then
ReDim Preserve temp(UBound(temp) - 1)
Unique temp
For i = LBound(temp) To UBound(temp)
result = result & " " & temp(i)
Next i
Lookup_concat = Trim(result)
Else
Lookup_concat = ""
End If

End Function

Function Unique(tempArray As Variant)

Dim coll As New Collection
Dim Value As Variant

On Error Resume Next
For Each Value In tempArray
If Len(Value) > 0 Then coll.Add Value, CStr(Value)
Next Value
On Error GoTo 0

ReDim tempArray(0)

For Each Value In coll
tempArray(UBound(tempArray)) = Value
ReDim Preserve tempArray(UBound(tempArray) + 1)
Next Value

End Function

After you insert this VBA code in the Module, please type the formula =Lookup_concat(E2,$A$2:$A$14,$C$2:$C$14) into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values. Please see the file I uploaded in this comment. Hope it solves your problem. 
Sincerely,Mandy

This comment was minimized by the moderator on the site
Hi, Thanks so much this worked!I used it to pull dates, that populated in the serial number format (<span style="letter-spacing: 0.2px; color: inherit; font-family: inherit; font-style: inherit; font-variant-ligatures: inherit; font-variant-caps: inherit; font-weight: inherit;">Changing the format to short date format using =TEXT(A2,”mm/dd/yy”) OR =DATEVALUE(A2) are not working. Do you have any solutions?</span>
This comment was minimized by the moderator on the site
Thank you for the explanations, however the function 'MultipleLookupNoRept' does not work on my file, could you tell me if an error exists.
This comment was minimized by the moderator on the site
Hi, Hasnae,Please check if you miss the third step -  check Microsoft Scripting Runtime option in the Available References list box.

This comment was minimized by the moderator on the site
Thank you so much for the code. Is there a way I can use the code to look up multiple values from multiple sheets? I tried to combine your function with IFERROR function but it doesn't seem to work.
This comment was minimized by the moderator on the site
Can this be modified to place the sum of those values? Instead of (400 400 400 400 400 400), can it sum them to show (2400)?
This comment was minimized by the moderator on the site
How with HLookUp function?
This comment was minimized by the moderator on the site
thanks for the code. I have modified it to allow you to optionally specify your own separator, Default is " ", if you specify the separator as"#cr" it will insert a CR/LF so the values will be on a separate line in the cell. It only applies the separator if there are multiple values

Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long, Optional ByVal pSep As Variant)

' ### Returns multiple values from a table into 1 cell ###

' pValue is the key value to lookup

' WorkRng is the Table you want to look up

' pIndex is the column # for the values to be returned from the pWorkRng

' pSep (optional) is the separator to be used. if omitted then default is a space (it doesn't apply the separator for the 1st entry)

' if the separtor = "#cr" it will separate the values on different line in the cell

Dim rng As Range

Dim sSep As String

Dim xResult As String

Dim Item1 As Boolean

Item1 = True



If IsMissing(pSep) = True Then

sSep = vbCr

Else

If pSep = "#cr" Then

sSep = vbCrLf

Else

sSep = pSep

End If

End If



xResult = ""

For Each rng In pWorkRng

If rng = pValue Then

If Item1 Then

xResult = xResult & rng.Offset(0, pIndex - 1)

Item1 = False

Else

xResult = xResult & sSep & rng.Offset(0, pIndex - 1)

End If

End If

Next

MYVLOOKUP = xResult

End Function
This comment was minimized by the moderator on the site
Thank you for this, the line breaks are what i needed to top this formula off! Question, is there a way to modify the code so that two values are compared? For example, similar to what we see with index and match, can i look for Product and Quantity columns, and based on those parameters it outputs results from the Region Column?
This comment was minimized by the moderator on the site
Thanks a lot for this code, it is very helpful. Does anyone know away to sum the values in the cell to just have at total of them.
Cheers
This comment was minimized by the moderator on the site
Hello, James, to sum values based on the corresponding items, the following article may help you, please chek it:
https://www.extendoffice.com/documents/excel/1268-excel-combine-duplicate-rows-and-sum.html
This comment was minimized by the moderator on the site
I have a server, it has connected with multiple applications. I want to compare compare two column and get the related applications details for that server.

What is the command for that.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations