在Excel中查找和返回一个单元格中的多个值(公式和VBA)

我们可以在Excel中的一个小区中查找并返回多个值(以逗号或空格分隔)吗?

我的许多同事和读者都经过多次询问这个问题。

Excel有一些惊人的查找公式, 如vlookup.索引/匹配(现在XLOOKUP.),但是没有一种方法可以返回多个匹配值。所有这些都是通过识别第一个匹配并返回它来完成的。

所以我做了一些vba编码来提出自定义函数(也称为a用户定义的功能)在Excel中。

更新:Excel释放动态阵列和诸如唯一和文本的令人敬畏的功能之后,现在可以使用简单的公式和返回一个单元格中所有匹配的值(在本教程中讨论)。

In this tutorial, I will show you how to do this (if you’re using the latest version of Excel – Microsoft 365 with all the new functions), as well as a way to do this in case you’re using older versions (using VBA).

让我们开始吧!

查找并返回一个小区中的多个值(使用公式)

如果您使用的是Excel 2016或先前版本,请转到下一节,其中我展示如何使用VBA执行此操作。

使用Microsoft 365订阅,您的Excel现在有很多功能强大的功能和功能,不存在于先前版本(例如Xlookup,动态阵列,唯一/过滤器功能等)

因此,如果您使用的是Microsoft 365(早期称为Office 365),则可以使用本节中所涵盖的方法可以在Excel中的一个单元格中查找并返回多个值。

就像你会看到的那样,这是一个非常简单的公式。

下面我有一个数据集,我有人的名字在列a和培训,他们已经接受了列B。

数据集查找并返回一个单元格excel中的多个值

单击此处下载示例文件并跟随

对于每个人,我想知道他们完成了什么培训。在列D中,我有唯一名称的列表(来自列A),我想快速查找并提取每个人所做过的所有培训,并将它们放在单个集合中(用逗号分隔)。

下面是这个公式:

= TextJoin(“,”,真实,如果(D2 = $ 2:$ 20,$ 2 $ 2:$ 20,“))

在Excel中的一个单元格中查找并返回多个值的公式

在单元格E2中输入公式后,将其复制为所有细胞你想要结果的地方。

这个公式是怎么起作用的?

让我解构这个公式并解释它是如何结合在一起的每一部分给我们的结果。

IF公式中的逻辑测试(D2 = $ 2:$ 20)检查名称单元格D2是否与范围A2:A20中的相同。

它通过A2:A20范围内的每个单元格,检查小区D2中的名称是否相同。如果它是相同的名称,它返回true,否则它返回false。

所以这部分公式将为您提供一个阵列,如下所示:

{true; false; false; true; false; false; false; false; false; false; false; false; false; false; false; false; false; false; false; false; false; false; false; false; false; false; false; false; false; false; false; false; false; false; false; false; false

由于我们只想获得鲍勃的培训(单元格D2中的值),我们需要为在上述阵列中返回True的单元格的所有相应培训。

通过指定IF公式的一部分作为具有培训的范围来轻松完成这一点。这使得如果单元格D2中的名称与A2:A20中的名称匹配,则IF公式将返回该人所采取的所有培训。

并且在阵列返回false的情况下,我们已将[value_if_false]值指定为“”(空白),因此它返回空白。

如果公式的一部分返回阵列,如下所示:

{“Excel”;“”;“”“”“PowerPoint”;“”;“”;“”;“”;“”;“”“;”“;”“;”“;”“”;“”“;”“”;“”;“”;“”“;”;“”;“”;“”}

它在哪里有训练鲍勃的名称已经拍摄和空白,无论何处都没有鲍勃。

现在,我们需要做的就是将这些培训名称(由逗号分隔)结合并将其返回在一个单元格中。

可以使用新的TextJoin公式轻松完成(在Excel 2019和Microsoft 365中的Excel中提供)

textjoin公式需要三个参数:

  • 界定符 - 在我们的示例中是“,”,因为我希望培训用逗号和空格字符分隔
  • true - 讲述textjoin公式忽略空单元格,只能组合不空的
  • 返回需要组合的文本的IF公式

如果您在Microsoft 365中使用的Excel已经具有动态阵列,则可以输入上述公式并按Enter键。如果您使用Excel 2019,则需要输入公式,并按住控件和Shift键,然后按Enter键

单击此处下载示例文件并跟随

在单个小区中获取多个查找值(没有重复)

由于唯一的公式仅在Microsoft 365中获得Excel,因此您将无法在Excel 2019中使用此方法

如果数据集中存在重复,如下所示,您需要更改公式一点,以便只能在单个单元格中获取唯一值列表。

数据重复

在上面的数据集中,有些人已经多次训练了。例如,鲍勃和斯坦已经两次培训了Excel训练,贝蒂已经拍了两次MS Word训练。但在我们的结果中,我们不想重复培训名称。

您可以使用以下公式来执行以下操作:

= textjoin(“,”,真实,独特(如果(d2 = $ 2:$ 20,$ 2:$ b $ 20,“)))

在没有重复的情况下在Excel中的一个单元格中找到并返回多个值的公式

上面的公式是一样的,只是做了一点小小的改变。我们在UNIQUE函数中使用了IF公式,以便在IF公式结果中出现重复的情况下,UNIQUE函数将删除它。

单击此处下载示例文件

查找并返回一个小区中的多个值(使用VBA)

如果您使用的是Excel 2016或先前版本,则无法访问TextJoin公式。因此,最好的方法,然后在单个单元格中查找并获得多个匹配值是通过使用您可以使用VBA创建的自定义公式。

要在单个单元格中获取多个查找值,我们需要在VBA中创建功能(类似于Vlookup功能),检查列中的每个单元格,如果找到查找值,则将其添加到结果中。

以下是可以执行此操作的VBA代码:

'代码aumit bansal(https://trumpex188金宝搏app官网滚球cel.com)函数singlecellextract(lookupvalue作为string,lookuprange as范围,columnumber用作整数)暗中ia vim dim measure作为i = 1的字符串,gookuprange.columns(1).cells。计数IF lookuprange.cells(i,1)= lookupValue然后结果=结果&lookuber)&“,i,columnumber)&”,“,”如果下一个我singlecellextract = left(结果,len(结果) -  1)结束功能

把这个代码放在哪里?

  1. 打开工作簿,单击Alt + F11(这将打开VBA编辑器窗口)。
  2. 在此VBA编辑器窗口中,左侧有一个项目资源管理器(列出所有工作簿和工作表)。右键单击工作簿中的任何对象,您希望此代码工作,然后转到Insert - > Module。在Excel VBA编辑器中插入一个模块
  3. 在模块窗口中(将出现在右边),复制并粘贴上面的代码。
  4. 现在你都是集。转到工作簿中的任何单元格和类型= SingleCellExtract并插入所需的输入参数(即,lookupvalue,lookuprange,columnnumber)。

这个公式是怎么起作用的?

这个函数的工作原理类似于VLOOKUP函数。

它接受3个参数作为输入:

1。LookupValue.- 我们需要在一系列单元格中查找的字符串。
2.lookuprange.一个单元格数组,我们需要从这里获取数据(在本例中是$B3:$C18)。
3.柱子—是返回匹配值的表/数组的列号(这里是2)。

使用此公式时,它会检查查找范围中最左侧列中的每个单元格当它找到匹配时,它会在您使用公式中的单元格中添加了结果。

Excel中单个单元格中的多个查找值

记住:将工作簿保存为启用宏的工作簿(。XLSM或.xls)来再次重用这个公式。此外,此函数只在此工作簿中可用,而不是在所有工作簿中。

单击此处下载示例文件

学习如何在Excel中使用VBA自动化乏味的重复任务。加入Excel VBA课程

在单个小区中获取多个查找值(没有重复)

您可能有可能在数据中重复。

如果您使用上面使用的代码,它也会为您提供重复。

如果您想要得到没有重复的结果,您需要稍微修改一下代码。

这是VBA代码,它将在单个单元格中为您提供多个查找值,而不会重复任何重复。

'代码aumit bansal(https://trumpex188金宝搏app官网滚球cel.com)函数multiplookupnorept(lookupvalue作为字符串,lookuprange为范围,columnumber用作整数)暗中ia vim iam dim dim结果作为i = 1的字符串,gookuprange.columns(1).cells.count如果lookuprange.cells(i,1)= lookupValue那么for for j = 1到i  -  1如果lookuprange.cells(j,1)= lookupvalue那么如果lookuprange.cells(j,columnumber)= lookuprange.cells(i,Clounnumber)如果下一个J结果=结果&“lookuprange.cells(i,columnumber)&”,“跳过:跳过:结束,如果下一个我多浮会upnnorept = left(结果,len(结果) -  1)结束功能

在VB编辑器中将此代码放在VB编辑器(如上图中所示)中,您将能够使用multiplookupnnorept.函数。

这是您将获得的结果的快照multiplookupnnorept.函数。

在一个单元格中Excel多个值-不重复

单击此处下载示例文件

在本教程中,我涵盖了如何在Excel中使用公式和VBA来查找并返回Excel中的一个单元格中的多个查找值。

While it can easily be done with a simple formula if you’re using Excel in Microsoft 365 subscription, if you’re using prior versions and don’t have access to functions such as TEXTJOIN, you can still do this using VBA by creating your own自定义功能

您也可能喜欢以下Excel教程:

Excel电子书订阅

免费Excel书籍

获得51个Excel提示电子书来飙升您的生产力,并更快地完成工作

55关于“Excel中的一个小区中的查找和返回多个值的想法”(公式&VBA)“

  1. for some reason when i put the VBA code in and go to run it it keeps coming up with an error on this section – Function SingleCellExtract(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) and i’m not sure why can you help?

  2. 如何在符号分隔的单个单元格中查找多个值
    list1
    OP512E08,OP513N16
    OP435R74,OP647C84,OP747B38
    OP12F45,OP832J43,OP21P67,OP495G74

    列表2
    PRODUCT55、OP513N16 PROODUCT61 OP495G74 ——————————————> 1
    OP512E08,Product31,Product48,Product19,OP513N16 --------> 2
    产品43,OP495G74,Product22,OP747B38,Product74,Product23 - > 3

    预期结果
    2,1,2
    3.
    1,3

    我想在所有list2中查找list1的所有部分,但每个单元格的结果在一个单元格中作为预期的结果
    谢谢你

  3. 你好,
    非常感谢这个令人敬畏的代码获取多个值在一个单一的单元格和没有重复。这对我来说非常有效。
    唯一的问题是它只运行一次,直到文件打开。我重新打开文件并重新计算公式的那一刻,它停止工作,即使在粘贴代码后也不会恢复。我还将文件保存为“启用宏的”文件,但它仍然没有解决问题。
    对这个问题有任何帮助吗?

    非常感谢。

  4. 当2个标准匹配时,然后在一个逗号中返回多个查找值
    A2=B2 Then Result From Range by " flokupmultiple " -请.......

  5. 伟大的代码,第一个不适合我,但是,第二个可以确保结果中没有多个值,工作很好!谢谢!

  6. 你好,
    我希望从下拉列表中选择的多个项目(每个在新行上的同一单元格中列出的项目中获取值,并在新行上列出下一个单元格中列出的每个值。
    这可能吗?
    我可以发给你我当前的文件,所以你可以看看我在使用什么吗?

    谢谢

  7. 你好,

    非常感谢这个解决方案。
    但它会有一个选项,它可以检查(不仅列a)中的每个单元格,才能说出A + B + C列,它会将我返回列D的值吗?

    谢谢,

  8. 你好,

    我试着用这个公式,效果很好。
    但我有一个问题:
    是否有可能不仅可以返回来自柯尔姆C的值,而且还可以返回,但也可以是来自康伦姆D和E的示例值?

    谢谢你的帮助,

  9. 我试图对电子邮件地址运行这一点,但我没有提到的结果。请帮助我已经用尽了我所能的所有东西。我完全按照文档中提到的那样保存,但输出给了我#Value错误

  10. 无论如何要获得结果总和。IE。如果结果为值,请参见10.3,5.1,7.5,我希望它返回这些值的总和,因此22.9

  11. 我如何在2个不同的工作簿中完成它?需要查找一个值(在单元格中输入用户) - 将其与另一个工作簿进行比较,并使用与其相关联的相应的数字(左侧2列)响应 - 输出1个单元格分离而不复制。谢谢

  12. 你好,Sumit先生,我收到您的excel邮件订阅,我访问您的网站真的是深刻的知识和概念的清理。这段代码函数multiplelookupnoreport非常棒。
    衷心感谢......

  13. 你好,我想知道是否有可能嵌套的多重elookupnoreport函数,因为它将不能与if函数,我一直试图使用它。谢谢。

  14. 嗨,有人可以帮助我的singlecellextract是可能使用这个udf如果我的lookupvalue是一个逗号分隔的值?

  15. 你好,
    我是一个非常新的VBA,我有一个场景如下。
    1.连接到DB,
    2.执行查询(结果将是单个列值)
    3.将查询结果作为List值存储到单个单元格中。

    谁能帮我做这项工作?谢谢! !

  16. 这工作得很好,但我想我可能遇到了一个限制,它不会看更多的约91,000行匹配。我有大约80万行需要这个来查看

  17. 你好,umit,

    您的VBA代码很棒,在很大程度上为我的目的服务。

    但我面临着又一个需要解决的问题。请帮我编码。

    问题在于所需的ColumnNumberi.e结果列一些单元格为空。因此,您的代码接受空白值(具有分隔符)以及其他值。

    以下示例将清除问题:

    col a col b col c col d
    (销售人员)(产品)(销售培生)(结果)
    1个超人玩具超人玩具,雄辩
    2蜘蛛侠蜘蛛侠,玩具
    3蝙蝠侠文具蝙蝠侠文具,玩具,肥皂
    克里希杂货店,肥皂
    5超人
    6个蜘蛛侠杂货店
    7个蝙蝠侠玩具
    8克里希
    9超人文具
    10个蜘蛛侠玩具
    11蝙蝠侠肥皂
    12克里希肥皂

  18. 有人会帮助我拍摄在2个班次工作的运营商的转移时间表。
    它应该从数据库中选择操作员,并根据他们在相关机器的专业知识填充移位表?

  19. 你好呀。我想做类似的事情。我有一张桌子,人们在第2行中命名,并一直在第2列下的日期。我希望能够为每年的年度休假“al”生成逗号分隔的列表。
    是这样的:
    日期p1 p2 p3 al
    1/1 al.
    2/1 Al Al Al P1,P2,P3
    3/1 Al Al P1,P2

    所以它类似于你的代码,但沿着一行看,而不是在列中。

    谢谢

    • 嗨克雷格,
      你可以在最后一列用一些if语句来做这个。=如果(B2 =“基地”,B 1美元," ")&如果(C2 =“基地”,“”& C 1美元," ")等。不会用逗号分隔。写在电话上,这样可以为错误找借口。

  20. 我需要在不同的工作表上使用此代码 - 从创建列表的一个工作表。我需要编辑什么以使其发生?根本不是一个编码者......

    • 对我来说同样的问题。我需要这可以在另一个工作表上工作。你能搞清楚吗?

  21. 你好,umit,
    我在你的论坛中找到了一个ELL中的多个查找值,并帮助了我很多。
    现在我需要改进这一点,我需要你的帮助。

    我们使用了仅为一列或单元格作为查找引用的代码。现在我需要再包含一个。2列作为s查找引用获取相同的结果。

    根据您的示例代码的代码如下。

    函数singlecellextractinward(lookupvalue作为字符串,lookuprange为范围,columnumber作为整数)
    暗淡我是双重的
    昏暗结果1作为字符串
    Dim Result2作为字符串
    如果结果2 =空
    Result2 = "最近没有向内"
    singlecellextractinward =结果2.
    如果
    对于i = 1到lookuprange.columns(1).cells.count
    如果lookuprange。Cells(i, 1) = lookupvalue
    结果1 =结果1&“”&lookuprange.cells(i,columnumber)&“,”
    singlecellextractinward =左(结果1,Len(结果1) - 1)
    如果
    接下来我
    结束函数

    您能否请帮助我在此代码上查找2列作为参考。

  22. 你好,我找到了你的代码,很有帮助。我有一个查询,我需要用相同的进程再查找一列。我该怎么做呢?请帮帮我。

  23. 亲爱的Sumit,
    看来我正在寻找的东西,但它不起作用!我得到了#name错误。我把它放在VB编辑器中并将其保存为.xslm。首先使用跨床单的功能,怀疑这是问题。然后尝试了它,我是一个带有示例机智的单张表仍然有#name错误。
    你知道我哪里做错了吗?救错了还是怎么了?

  24. 这是太棒了!是否有一种简单的方法来扩展此功能以搜索多个列,例如,如果列D是另一个名称列表(销售代表2)?
    此外,有没有办法排除空白单元格?如果发生丢失的名称,例如丢失的名称?

  25. 这次真是万分感谢。我发现了类似的东西,虽然返回vlookupall,给出了类似的结果,但遵循内部逻辑有点难。

  26. 嗨,我很欣赏你的帖子,显示如何使用VBA来做这个,但是我想知道是否有一种方法是使用Excel公式执行此操作?

  27. 嗨sumit,
    你的宏确实是我所需要的,所以我很高兴找到它,但我无法让它工作 - 我是vba的新手,所以可能只是我

    我已经将它包含到我的宏启用的电子表格中,每次它执行都有一个编译错误,就像下面劳拉提到的那样。

    编译错误:
    语法错误

    SingleCellexTract =左(结果,(结果) - 1))

    我已经尝试改变括号,但没有改善。看起来它找不到附加的函数,但我只是猜测。有什么建议吗?

    它可以是我的本地设置

    • 固定!

      我认为示例xlsm向下负载很好,但上面的代码文本包括呼叫中的一些有趣的错误

      SingleCellexTract =左(结果,Len(结果) - 1)

      在复制/粘贴后,似乎有一个有趣的角色。当我刚刚删除它并用手键入它,然后它很好。

      真的很高兴尽我所需要的。

  28. 你好!谢谢你的VBA - 我如何纠正它,所以Vlookup值不是区分大小写?目前,如果我在查找“Apple”,例如,我需要输入“Apple”。如果我使用小写“a”,它不会查找该值。谢谢!

    • 嘿Ashley ..您可以使用以下代码进行:

      函数SingleCellExtract(Lookupvalue为字符串,LookupRange为范围,ColumnNumber为整数)
      只要
      昏暗的结果作为字符串
      对于i = 1到lookuprange.columns(1).cells.count
      如果lcase(lookuprange.cells(i,1))= lcase(lookupvalue)那么
      结果=结果&“”&lookuprange.cells(i,columnumber)&“,”
      如果
      接下来我
      SingleCellexTract =左(结果,Len(结果) - 1)
      结束函数

      • 嗨sumit,

        我们可以获得代码来反转此功能我是否有多个HCODE,如H310,H302等,在一个列和Corrosponding语句中,在另一个列中,如腐蚀性,如这两个代码,我可以获得一个代码,用于在同一单元格中的不同H代码进行V查找。通过COMA到他们的CORROSPONDING语句而无需重复,例如,如果具有相同语句的两个H代码我得到了正常声明
        Lokesh.

  29. 我想知道是否还有人会回复这个帖子....

    我正在尝试使用此宏,但我得到以下错误:

    编译错误:
    语法错误

    它突出了以下部分,因为它被困在上面的一步
    SingleCellexTract =左(结果,Len(结果) - 1)

    我的数据是3列,我的公式如下:
    = SingleCellextract(E3,$ A:$ C,3)

    E3是我希望它在范围内找到的值(一个数字)
    $ - x:$ c是范围
    图3是第3列,其中我希望公式拔出结果(结果是文本)。

    我在做什么不正确的?

  30. 嗨sumit,

    你的另一个好主意。代码优化和选项集成

    公共功能floonkupmultiple(Byref LookupValue为String,_
    ByRef LookUpRange作为Excel。范围,_
    Byref Columnnumber长,_
    可选的byref bunique作为布尔= true)作为字符串的变体

    '从匹配特定值的列表中获取所有值

    昏暗的苗条
    Dim Strfilter作为字符串
    Dim lgElement As Long

    对于lgrow = 1到lookuprange.columns(1).cells.count
    如果是Bunique那么
    如果lookuprange.cells(lgrow,1).value2 = lookupValue那么
    对于Lgelement = 1至Lgrow - 1
    如果lookuprange.cells(lgelement,1).value2 = lookupValue那么
    如果lookuprange.cells(lgelement,columnnumber).value2 = lookuprange.cells(lgrow,columnumber).value2然后转到跳过
    如果
    下一个legelement.

    strFilter = strFilter & " " & LookUpRange。Cells(lgRow, ColumnNumber) & ", "

    跳过:
    如果
    别的
    如果lookuprange.cells(lgrow,1).value2 = lookupValue那么strFilter = strFilter & " " & LookUpRange。细胞(lgRow ColumnNumber)。Value2 &”、“
    如果
    下一个lgRow

    '删除最后“,”
    FloOkupMultiple = VBA.Left(Strfilter,VBA.Len(Strfilter) - 1)

    结束函数

  31. 我有个问题!学生名称与ID和班级组。如何获取列表并使用组合框 - 选择类,名称显示在其他工作表上,其中包含其ID和类名。

    • 嗨Saji,很高兴你喜欢它。

      要在不重复的情况下获取值,请使用此代码:

      函数SingleCellExtract(Lookupvalue为字符串,LookupRange为范围,ColumnNumber为整数)
      只要
      昏暗的结果作为字符串
      对于i = 1到lookuprange.columns(1).cells.count
      如果LookupRange。Cells(i, 1) = Lookupvalue Then
      对于J = 1到i - 1
      如果lookuprange.cells(j,1)= lookupValue则
      如果LookupRange。Cells(J, ColumnNumber) = LookupRange。细胞(我ColumnNumber)
      转到跳过
      如果
      如果
      接下来J.
      结果=结果&“”&lookuprange.cells(i,columnumber)&“,”
      跳过:
      如果
      接下来我
      SingleCellexTract =左(结果,Len(结果) - 1)
      结束函数

评论都关门了。