使用Vlookup函数、Index+Match函数进行查找时,只能返回查找到的第一个匹配值。如何返回符合条件的多个匹配值呢?
本文介绍两种方法。第一种方法使用Index+Aggregate函数;第二种方法使用Textjoin函数。Textjoin函数是Excel 2019和office 365新引入的函数。
本文将分别介绍在单个条件查找和多个条件查找情形下,这两种方法的应用。
1
问题描述
2
两种查找方法
方法一:Index+Aggregate函数
在G2单元格输入公式:
1/($A$2:$A$11=$F$2)*(ROW($A$2:$A$11)-1),ROW(A1))),"")
向下复制G2单元格的公式,一直到公式返回空值。
公式解析:
Aggregate函数的第3个参数1/($A$2:$A$11=$F$2)*(ROW($A$2:$A$11)-1)返回的结果为
Aggregate函数第1个参数“15”代表Small函数,第2个参数“6”表示忽略错误值。第4个参数“ROW(A1)”返回值“1”,表示Aggregate函数返回第1个最小值,即“1”。
方法二:Textjoin函数
在G2单元格输入公式:
=TEXTJOIN(",",TRUE,IF($A$2:$A$11=$F$2,$B$2:$B$11,""))
按Ctrl+Shift+Enter完成公式输入。
使用Textjoin函数返回的多个匹配值以指定的分隔符(本例为“,”)连接,并且返回的值在同一个单元格内。
公式解析:
Textjoin函数使用指定的分隔符连接字符串。Textjoin函数的第1个参数“,”表示返回的结果用“,”分隔;第2个参数“True”表示忽略空值。
第3个参数是If函数返回的数组。IF($A$2:$A$11=$F$2,$B$2:$B$11,""),当A2:A11中的部门为“研发部”时,返回对应的B2:B11中的值,否则返回空文本。IF函数返回的结果为{"皮卡球";"朱猪侠";"";"";"";"";"易水寒";"蓝精灵";"";""}。
3
多条件查找
上述介绍的两种方法是以单个条件查找为例进行说明。如果需要以多个条件查找,这两种方法仍然适用,只需要在公式中添加更多查找条件即可。
例如以F2单元格的部门、G2单元格的性别为条件,查找符合条件的姓名。
方法一:Index+Aggregate函数
在H2单元格输入公式:
方法二:Textjoin函数
在H2单元格输入公式:
=TEXTJOIN(",",TRUE,IF(($A$2:$A$11=$F$2)*($C$2:$C$11=$G$2),$B$2:$B$11,""))
按Ctrl+Shift+Enter结束公式输入。