匹配算法详细讲解（查找多个匹配值）

使用Vlookup函数、Index Match函数进行查找时，只能返回查找到的第一个匹配值。如何返回符合条件的多个匹配值呢？

本文介绍两种方法。第一种方法使用Index Aggregate函数；第二种方法使用Textjoin函数。Textjoin函数是Excel 2019和office 365新引入的函数。

本文将分别介绍在单个条件查找和多个条件查找情形下，这两种方法的应用。

1

问题描述

如下图所示，要求根据F2单元格的部门，查找姓名。

2

两种查找方法

方法一：Index Aggregate函数

在G2单元格输入公式：

=IFERROR(INDEX($B$2:$B$11,AGGREGATE(15,6,

1/($A$2:$A$11=$F$2)*(ROW($A$2:$A$11)-1),ROW(A1))),"")

向下复制G2单元格的公式，一直到公式返回空值。

公式解析：

（1）Aggregate函数用于返回A2:A11中“研发部”所在的行号。Aggregate函数的第3个参数1/($A$2:$A$11=$F$2)*(ROW($A$2:$A$11)-1)返回的结果为{1;2;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;7;8;#DIV/0!;#DIV/0!}，即A2:A11中“研发部”所在的行号分别为第1、2、7、8行。

Aggregate函数第1个参数“15”代表Small函数，第2个参数“6”表示忽略错误值。第4个参数“ROW(A1)”返回值“1”，表示Aggregate函数返回第1个最小值，即“1”。

（2）Index函数用于返回B2:B11中指定位置的值。在G2单元格的公式中Aggregate函数返回值“1”，Index函数则返回“皮卡球”。在G3单元格的公式中Aggregate函数返回值“2”，Index函数则返回“朱猪侠”。

方法二：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函数返回的结果为{"皮卡球";"朱猪侠";"";"";"";"";"易水寒";"蓝精灵";"";""}。

Texjoin函数将IF函数返回的数组以逗号作为分隔符连接，并且忽略IF函数返回的空值。

3

多条件查找

上述介绍的两种方法是以单个条件查找为例进行说明。如果需要以多个条件查找，这两种方法仍然适用，只需要在公式中添加更多查找条件即可。

例如以F2单元格的部门、G2单元格的性别为条件，查找符合条件的姓名。

方法一：Index Aggregate函数

在H2单元格输入公式：

=IFERROR(INDEX($B$2:$B$11,AGGREGATE(15,6,

1/(($A$2:$A$11=$F$2)*($C$2:$C$11=$G$2))*(ROW($A$2:$A$11)-1),ROW(A1))),"")

方法二：Textjoin函数

在H2单元格输入公式：

=TEXTJOIN(",",TRUE,IF(($A$2:$A$11=$F$2)*($C$2:$C$11=$G$2),$B$2:$B$11,""))

按Ctrl Shift Enter结束公式输入。