跳到主要内容

根据一个或多个条件查找并返回多个值

通常，您可以使用Vlookup函数来获取第一个对应的值，但是有时您希望基于特定条件返回所有匹配的记录。 本文，我将讨论如何进行vlookup并将所有匹配值垂直，水平或返回到单个单元格中。

Vlookup并垂直返回所有对应的值

Vlookup并水平返回所有对应的值

Vlookup并将所有对应的值返回到一个单元格中

Vlookup并垂直返回所有对应的值

要根据特定条件垂直返回所有匹配值，请应用以下数组公式：

1。 输入此公式或将其复制到要输出结果的空白单元格中：

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($E$2=$A$2:$A$20, ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

备注：在以上公式中， C2：C20 该列是否包含您要返回的匹配记录； A2：A20 该列是否包含条件； 和 E2 是您要基于其返回值的特定条件。 请根据需要更改它们。

2。 然后按 Ctrl + Shift + Enter 键一起获得第一个值，然后向下拖动填充手柄以获取所需的所有相应记录，请参见屏幕截图：

提示：

要根据更具体的值垂直查找并返回所有匹配值，请应用以下公式，然后按 Ctrl + Shift + Enter 键。

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20))*(--($F$2=$B$2:$B$20))), ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

Vlookup并水平返回所有对应的值

如果要获取水平显示的匹配值，下面的数组公式可以为您提供帮助。

1。 输入此公式或将其复制到要输出结果的空白单元格中：

=IFERROR(INDEX($C$2:$C$20,SMALL(IF($F$1=$A$2:$A$20,ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")

备注：在以上公式中， C2：C20 该列是否包含您要返回的匹配记录； A2：A20 该列是否包含条件； 和 F1 是您要基于其返回值的特定条件。 请根据需要更改它们。

2。 然后按 Ctrl + Shift + Enter 键一起获得第一个值，然后向右拖动填充手柄以获取所需的所有相应记录，请参见屏幕截图：

提示：

要根据更具体的值横向查找并返回所有匹配值，请应用以下公式，然后按 Ctrl + Shift + Enter 键。

=IFERROR(INDEX($C$2:$C$20,SMALL(IF(1=((--($F$1=$A$2:$A$20))*(--($F$2=$B$2:$B$20))),ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")

Vlookup并将所有对应的值返回到一个单元格中

要将vlookup并将所有对应的值返回到单个单元格中，应应用以下数组公式。

1。 在公式下方输入或复制到空白单元格中：

=TEXTJOIN(", ",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

备注：在以上公式中， C2：C20 该列是否包含您要返回的匹配记录； A2：A20 该列是否包含条件； 和 F1 是您要基于其返回值的特定条件。 请根据需要更改它们。

2。 然后按 Ctrl + Shift + Enter 键一起将所有匹配的值合并到一个单元格中，请参见屏幕截图：

提示：

要根据单个单元格中更具体的值查找并返回所有匹配值，请应用以下公式，然后按 Ctrl + Shift + Enter 键。

=TEXTJOIN(", ",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

请注意： 此公式仅在Excel 2016及更高版本中成功应用。 如果您没有Excel 2016，请查看 了解更多 把它弄下来。

更多有关Vlookup的文章：

  • Vlookup并从下拉列表中返回多个值
  • 在Excel中，如何从下拉列表中进行vlookup并返回多个相应的值，这意味着当您从下拉列表中选择一项时，其所有相对值会立即显示，如以下屏幕截图所示。 本文，我将逐步介绍解决方案。
  • Vlookup在Excel中返回空白而不是0或N / A
  • 通常，当您应用vlookup函数返回相应的值时，如果匹配的单元格为空，则它将返回0，如果找不到匹配的值，则会收到错误＃N / A值。 而不是显示0或＃N / A值，如何使其显示空白单元格？
  • Vlookup从Excel表返回多列
  • 在Excel工作表中，您可以应用Vlookup函数从一列中返回匹配值。 但是，有时，您可能需要从多个列中提取匹配的值，如下图所示。 如何使用Vlookup函数从多个列中同时获取相应的值？
  • 跨多个工作表的Vlookup值
  • 在excel中，我们可以轻松地应用vlookup函数在工作表的单个表中返回匹配值。 但是，您是否曾经考虑过如何在多个工作表中实现vlookup价值？ 假设我有以下三个具有数据范围的工作表，现在，我想根据这三个工作表中的条件来获取相应值的一部分。
  • 超级公式栏 （轻松编辑多行文本和公式）； 阅读视图 （轻松读取和编辑大量单元格）； 粘贴到过滤范围...
  • 合并单元格/行/列 和保存数据； 拆分单元格内容； 合并重复的行和总和/平均值...防止细胞重复； 比较范围...
  • 选择重复或唯一 行; 选择空白行 （所有单元格都是空的）； 超级查找和模糊查找 在许多工作簿中； 随机选择...
  • 确切的副本 多个单元格，无需更改公式参考； 自动创建参考 到多张纸； 插入项目符号，复选框等...
  • 收藏并快速插入公式，范围，图表和图片； 加密单元 带密码 创建邮件列表 并发送电子邮件...
  • 提取文字，添加文本，按位置删除， 删除空间; 创建和打印分页小计； 在单元格内容和注释之间转换...
  • 超级筛选 （将过滤方案保存并应用于其他工作表）； 高级排序 按月/周/日，频率及更多； 特殊过滤器 用粗体，斜体...
  • 结合工作簿和工作表; 根据关键列合并表； 将数据分割成多个工作表; 批量转换xls，xlsx和PDF...
  • 数据透视表分组依据 周号，周几等 显示未锁定的单元格 用不同的颜色 突出显示具有公式/名称的单元格...
kte选项卡201905
  • 在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50％，并减少数百次鼠标单击！
officetab底部
Comments (8)
No ratings yet. Be the first to rate!
Ahmed Abosamaha
Ahmed Abosamaha
This comment was minimized by the moderator on the site
gents

would you please share with me the formula of the three ways but for data in horizontal table.
Reply
0
0
skyyang
skyyang     Ahmed Abosamaha
This comment was minimized by the moderator on the site
Hello, Ahmed,
To solve your problem, please apply the below formulas:
Get the results vertically: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($A$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), ROW(1:1))),"" )
Get the results horizontally: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($D$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), COLUMN(A1))),"" )
Ge the results in one cell: =TEXTJOIN(", ",TRUE,IF($B$1:$K$1=J5,$B$2:$K$2,""))
Note: These formulas are array formulas, you should press Ctrl + Shift + Enter keys together to get the correct result.

https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-vlookup-data.png

Pease try, hope this can help you!
Reply
Report
0
0
Ahmed Abosamaha
Ahmed Abosamaha     skyyang
This comment was minimized by the moderator on the site
thanks for your greet support and quick response
Reply
0
0
itika
itika
This comment was minimized by the moderator on the site
how do u get all rows to fill up like in yr video without pressing ctrl+shift+enter
Reply
0
0
Taia
Taia
This comment was minimized by the moderator on the site
After I extend the data, some of the cells is showing a "0" instead of blank
Reply
0
0
Adam
Adam     Taia
This comment was minimized by the moderator on the site
It means that there is missing data or the wrong type of data in the cell(s) from the column that is being referrenced. For instance, Im working with a bunch of data and I want all ID numbers for workers under a certain supervisor. However, in the table column being referenced with all workers' ID numbers, some cells had the workers last name in it and not their ID numbers. Those exact cells produced 0s when the entire formula was put in, and this is because it was text within a column that is mostly numbers.
Reply
0
0
Win E
Win E
This comment was minimized by the moderator on the site
How can you tweak the formula so that you have multiple outputs in column D? E.g. I want to populate column D with each of the countries, such that all of their respective cities will be returned in E? The only work around I have can see is manually changing $D$2
Reply
0
0
Jeff
Jeff
This comment was minimized by the moderator on the site
Thank you very much, you save lots of work here!





Regarding your formula {=TEXTJOIN(", ",TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,"")},

I changed a little : (=TEXTJOIN(CHAR(10),TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,""))}





Regards,



Jeff
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