Note: The other languages of the website are Google-translated. Back to English

如何在Excel中基于一个或多个条件返回多个匹配值?

通常,使用VLOOKUP函数对我们大多数人来说查找特定值并返回匹配项很容易。 但是,您是否曾经尝试过根据一个或多个条件返回多个匹配值,如下面的屏幕快照所示? 在本文中,我将介绍一些用于解决Excel中复杂任务的公式。

根据一个或多个条件使用数组公式返回多个匹配值


根据一个或多个条件使用数组公式返回多个匹配值

例如,我要提取所有年龄为28岁且来自美国的姓名,请使用以下公式:

1。 将以下公式复制或输入到要查找结果的空白单元格中:

=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

备注:在以上公式中, B2:B11 是从中返回匹配值的列; F2, C2:C11 是第一个条件和包含第一个条件的列数据; G2, D2:D11 是第二个条件,并且包含此条件的列数据,请根据需要进行更改。

2。 然后按 Ctrl + Shift + Enter 键以获取第一个匹配结果,然后选择第一个公式单元格并将填充手柄向下拖动到这些单元格,直到显示错误值,现在,所有匹配值都将返回,如下图所示:

小贴士:如果只需要根据一种条件返回所有匹配值,请应用以下数组公式:

=IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1), ROW(1:1))),"" )


更多相关文章:

  • 在一个逗号分隔的单元格中返回多个查找值
  • 在Excel中,我们可以应用VLOOKUP函数从表单元格中返回第一个匹配的值,但是有时,我们需要提取所有匹配的值,然后用特定的分隔符(例如逗号,破折号等)分隔为单个单元格如下图所示。 我们如何在Excel中的一个逗号分隔的单元格中获取并返回多个查找值?
  • Vlookup并在Google表格中一次返回多个匹配值
  • Google表格中的常规Vlookup函数可以帮助您根据给定的数据查找并返回第一个匹配值。 但是,有时,您可能需要vlookup并返回所有匹配的值,如下图所示。 您有什么简便的方法可以解决Google表格中的此任务?
  • Vlookup并从下拉列表中返回多个值
  • 在Excel中,如何从下拉列表中进行vlookup并返回多个相应的值,这意味着当您从下拉列表中选择一项时,其所有相对值会立即显示,如以下屏幕截图所示。 本文,我将逐步介绍解决方案。
  • Vlookup并在Excel中垂直返回多个值
  • 通常,您可以使用Vlookup函数来获取第一个对应的值,但是有时您希望基于特定条件返回所有匹配的记录。 本文,我将讨论如何进行vlookup并将所有匹配值垂直,水平或返回到单个单元格中。
  • Vlookup并在Excel中返回两个值之间的匹配数据
  • 在Excel中,我们可以应用常规的Vlookup函数基于给定的数据获取相应的值。 但是,有时,我们想要vlookup并返回两个值之间的匹配值,如下面的屏幕快照所示,您如何在Excel中处理此任务?

 


  • 超级公式栏 (轻松编辑多行文本和公式); 阅读版式 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 和保存数据; 拆分单元格内容; 合并重复的行和总和/平均值...防止细胞重复; 比较范围...
  • 选择重复或唯一 行; 选择空白行 (所有单元格都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择...
  • 确切的副本 多个单元格,无需更改公式参考; 自动创建参考 到多张纸; 插入项目符号,复选框等...
  • 收藏并快速插入公式,范围,图表和图片; 加密单元 带密码 创建邮件列表 并发送电子邮件...
  • 提取文字,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级滤镜 (将过滤方案保存并应用于其他工作表); 高级排序 按月/周/日,频率及更多; 特殊过滤器 用粗体,斜体...
  • 结合工作簿和工作表; 根据关键列合并表; 将数据分割成多个工作表; 批量转换xls,xlsx和PDF...
  • 数据透视表分组依据 周号,周几等 显示未锁定的单元格 用不同的颜色 突出显示具有公式/名称的单元格...
kte选项卡201905
  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
officetab底部

 

按评论排序
注释 (25)
还没有评分。 成为第一位评论!
该评论由网站上的主持人最小化
我尝试了这个完全相同的公式; 100% 复制。 我唯一改变的是匹配和返回的数据。 当我使用此公式时,Excel 显示“您为此函数输入了太多参数)。=INDEX('2020 Volume Report'!$B$3:$B$100,SMALL(IF(COUNTIF($A$1,'2020 Volume报告'!$A$3:$A$100)*COUNTIF($A$3,'2020年成交量报告'!$D$3:$D$100),ROW('2020年成交量报告'!$A$3:$G$100)- MIN(ROW('2020 年成交量报告'!$A$3:$G$100))+1,"0"),ROW(A1),COLUMN(A1))
该评论由网站上的主持人最小化
嗨,你能把你的数据和公式错误作为一个屏幕截图在这里吗?
该评论由网站上的主持人最小化
您好,我如何将它用于水平条件。
该评论由网站上的主持人最小化
公式中+0后面的“1”是什么? 这不在示例一中。
该评论由网站上的主持人最小化
嗨,我尝试过相同的公式。 我得到了结果,但是当给 CSE 时它没有提供任何多重响应
该评论由网站上的主持人最小化

该评论由网站上的主持人最小化
关于使用数组公式返回基于一个或多个条件的多个匹配值:为什么如果我在除 A1 之外的其他任何地方都有数据,即使我更新了公式中的所有单元格引用,它也不起作用?
该评论由网站上的主持人最小化
在第一个示例中,需要对公式进行哪些更改才能返回小于 28 岁的每个人?
该评论由网站上的主持人最小化
嗨,

我想知道是否可以输入第二个标准,但与第一个标准的范围相同,

例如,对于上面使用的示例,我想搜索来自美国和法国的人的姓名所以单元格 F3 将包含法国,斯嘉丽和安德鲁也将填充在 G 列的列表中

提前感谢您的帮助。
该评论由网站上的主持人最小化
你好尼克,

乐意效劳。 如果你想同时得到美国和法国人的名字,我建议你使用我们的公式两次来得到结果。 请看截图,在 F2 和 G2 中是值“美国”和“法国”。 应用公式 =IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1 ), ROW(1:1))),"" ) 以获得美国的结果。 并应用公式 =IFERROR(INDEX($B$2:$B$11, SMALL(IF($G$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+ 1), ROW(1:1))),"" ) 得到法国的结果。 这很简单。 请试一试。

Sincerely,
曼迪
查看附件 (1 / 5)
该评论由网站上的主持人最小化
当我使用第二个公式并向下拖动时,什么也没有出现。 公式结果 (fx) 说它应该返回一些东西,但它是空白的。 我该如何纠正?
该评论由网站上的主持人最小化
你好阿莉西亚,

乐意效劳。 我尝试了文章中的第二个公式并将公式向下拖动,其余结果都返回了。 我认为您的问题可能有两个原因。 首先,您可能忘记按 Ctrl + Shift + Enter 键输入公式。 其次,匹配结果只有一个,所以没有其他结果不返回。 请检查一下。

Sincerely,
曼迪
该评论由网站上的主持人最小化
您好,
我尝试使用公式,它要么生成值 0,要么生成附加图像
查看附件 (1 / 5)
该评论由网站上的主持人最小化
你好,米尔库
你的截图显示的是MAC版的WPS软件,所以我不确定我们的公式是否可用。
我上传了一个 Excel 文件到这里,您可以尝试看看它是否可以在您的环境中正确计算。
谢谢!
查看附件 (1 / 5)
该评论由网站上的主持人最小化
你好,
在以下情况下扩展第一个公式需要什么:
一些 ID 为空白(例如单元格 A5 为空白),我想要一个附加条件,仅当 ID 不为空白时才输出行。 (所以输出应该是詹姆斯和阿卜杜勒。
谢谢!
该评论由网站上的主持人最小化
你好,乔,
为了解决您的问题,请应用以下公式:
=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11)*($A$2:$A$11<>0), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

请试一试,希望对你有帮助!
该评论由网站上的主持人最小化
嗨,

如果在单元格 H1 中我写“名称”并想将其与公式链接,那将如何工作?
然后我可以在单元格 H1 中写“ID”,结果会自动得到:AA1004; DD1009; PP1023(用于第一个公式)

预先感谢您!
该评论由网站上的主持人最小化
你好,玛丽
抱歉,我无法理解您的第一个问题,您能否更清楚详细地解释您的问题? 或者您可以在此处插入屏幕截图来描述您的问题。
至于第二个问题,您只需将单元格引用更改为:
=INDEX($A$2:$A$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

记得按 Ctrl + Shift + Enter 钥匙在一起。
请尝试,希望对您有所帮助!
该评论由网站上的主持人最小化
嘿嘿,谢谢你的公式。 它适用于“固定”值/文本作为标准。 但是,我尝试使用的标准之一是条件(值 <>0 ),但不适用于所描述的公式。 你们知道我应该改变什么来适应公式,所以我可以有一个条件作为标准之一,好吗?

最好的,

约翰
该评论由网站上的主持人最小化
你好,马库斯
要解决您的问题,请查看这篇文章:
https://www.extendoffice.com/documents/excel/6393-excel-vlookup-function.html#b3-2
这个任务有一些详细的解释。 您只需要将标准更改为您自己的。
谢谢!
该评论由网站上的主持人最小化
嗨,

首先,谢谢分享!

您能否为以下案例提供解决方案:

我有 3 列(A:包含参考信息,B:包含要搜索的信息,C:搜索结果)

图片网址如下

https://ibb.co/VHCd09K

A列-------------B列------------C列
文件名-------------------------名称----文件名,文件名,元素名称、名称
已更改元素------------------元素-------------已更改元素、元素名称、元素 ID
列位置
文档名称
元素名称
姓名:
类别
保修政策
雪道
元素 ID

我需要在 A 列中搜索与单元格 B2(名称)或 B3(元素)的任何部分匹配,并在一个单元格中获取结果,

谢谢你,贝扎德
该评论由网站上的主持人最小化
你好,贝扎德
也许下面的用户定义函数可以帮助你。
Public Function ConcatPartLookUp(rngInput As Range, rngSource As Range, Optional strDelimiter As String, Optional blCaseSensitive)
Dim rng As Range
If strDelimiter = "" Then strDelimiter = ","
If IsMissing(blCaseSensitive) Then
    blCaseSensitive = False
Else
    blCaseSensitive = True
End If
For Each rng In rngSource
    If blCaseSensitive Then
        If InStr(1, rng.Value, rngInput.Value, vbBinaryCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    Else
        If InStr(1, rng.Value, rngInput.Value, vbTextCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    End If
Next
If Len(ConcatPartLookUp) > 0 Then ConcatPartLookUp = Mid(ConcatPartLookUp, 2, Len(ConcatPartLookUp))
End Function


复制并粘贴此代码后,然后使用此公式:=ConcatPartLookUp(B2,$A$2:$A$8) 得到你需要的结果。
请试一试,希望对您有所帮助!
该评论由网站上的主持人最小化
嗨,

感谢您发布这些示例。
我正在尝试在我自己的工作表中实现它,但没有让它工作(可能是因为我使用的是欧洲版本的 excel)?

我想获取我轮班或为客户工作“一些”(>0)小时的日期。

所以在 I3 中是名称,在 J3 中是月份。 K3 和 L3 是班次(1 工作)和小时数(不知道如何设置,应该大于零)

我的预期结果是:
班次:I7 和 I8
小时:J7

所以我在 0 年 2 月 3 日为“第 10 个人”工作了 2022 多个小时
第 2 个人在 '10-10-2022' 和 28-10-2022 轮班

当我添加 '=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW ($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))' 在我的 Excel 表中,它不允许公式不同部分之间的逗号。
所以我需要将它们更改为';'。
但是当我尝试它时,它总是说:'#NAME?

那么有人可以帮我解决这个问题吗?

亲切的问候,

巴斯
[img]https://drive.google.com/file/d/1iIPQKuj_PNhqWyWlwJ4IQTqGNEd6B9Hw/view?usp=share_link[/img]
该评论由网站上的主持人最小化
嗨,如果有重复的值(例如两个亚当),我如何确保它只返回 1 个亚当而不是 2 个?
该评论由网站上的主持人最小化
你好,鲍比,
要仅提取唯一的匹配值,您应该应用以下公式:
粘贴公式后,请按 Ctrl + Shift + Enter 键在一起以获得正确的结果。
=IFERROR(INDEX($B$2:$B$5, MATCH(0, COUNTIF(H1:$H$1, $B$2:$B$5)+IF($D$2:$D$5<>$G$2, 1 , 0)+IF($C$2:$C$5<>$F$2, 1, 0), 0)), "")

请试一试,希望对您有所帮助!
这里还没有评论
留下你的意见
以访客身份发帖
×
评价此帖子:
0   产品特性
建议地点

关注我们

版权所有 © 2009 - extendoffice.com。 | 版权所有。 供电 ExtendOffice。 | 网站地图
Microsoft和Office徽标是Microsoft Corporation在美国和/或其他国家的商标或注册商标。
受Sectigo SSL保护