Excel中的一对多查询,很多人都是不知道公式要怎么写的。就拿我们最常用的VLOOKUP函数、LOOKUP函数来说,仅仅依靠一个函数是无法做到的,有些人也会加上一些辅助列来达到一对多查询的目的,但都是比较麻烦的。

下面教你一组万能公式“INDEX SMALL IF ROW IFERROR”,学会这个函数组合,可以解决Excel中一对多查询90%以上的问题。

下面有一张图,想要在A1:F9单元格区域中,找出符合B11单元格“销售一部”的所有内容,并显示到以A13开始的单元格区域中,图片中符合条件的只有第2、第5和第6行,已经用其他颜色标出来。如果用这组函数来实现,你有什么思路吗?

excel函数公式怎么匹配两个条件(5个Excel函数组合一起使用)(1)

具体操作步骤如下:

1、选中A13单元格 -- 在编辑栏中输入公式“=IFERROR(INDEX(A$1:A$9,SMALL(IF($A$2:$A$9=$B$11,ROW($A$2:$A$9)),ROW(A1))),"")”-- 按组合键“Ctrl Shift Enter”结束该公式。

excel函数公式怎么匹配两个条件(5个Excel函数组合一起使用)(2)

2、可以看到A13单元格公式返回的结果是“销售1部”-- 将A13单元格往右拖拉至F13单元格 -- 往下拖拉至F15单元格 -- 即可得到部门为“销售一部”的所有数据。

excel函数公式怎么匹配两个条件(5个Excel函数组合一起使用)(3)

3、动图演示如下。

excel函数公式怎么匹配两个条件(5个Excel函数组合一起使用)(4)

4、公式解析。

(1)ROW($A$2:$A$9):

生成一个行号的数组{2;3;4;5;6;7;8;9},即A1:F9单元格区域中的第2行至第9行的行号。

excel函数公式怎么匹配两个条件(5个Excel函数组合一起使用)(5)

(2)IF($A$2:$A$9=$B$11,ROW($A$2:$A$9)):

判断$A$2:$A$9单元格区域中的内容是否跟$B$11单元格的内容相等,如果相等,返回

$A$2:$A$9对应的行号,否则,返回FALSE。此时该公式得到的结果是一组数组{2;FALSE;FALSE;5;6;FALSE;FALSE;FALSE}。数组中的2、5和6代表“销售1部”所在的行号。

excel函数公式怎么匹配两个条件(5个Excel函数组合一起使用)(6)

(3)SMALL(IF($A$2:$A$9=$B$11,ROW($A$2:$A$9)),ROW(A1)):

SMALL函数的作用是:在一列数值中,按从小到大的顺序取第n个值。第1个参数IF($A$2:$A$9=$B$11,ROW($A$2:$A$9))为数据区域。第2个参数ROW(A1)为返回的数据在数组或数组区域里的位置(从小到大)。IF($A$2:$A$9=$B$11,ROW($A$2:$A$9))返回的结果为{2;FALSE;FALSE;5;6;FALSE;FALSE;FALSE}。由于SMALL函数在计算最小值时忽略逻辑值TRUE和FALSE以及文本型数字。所以该公式最后得到的结果为{2;5;6;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}。

excel函数公式怎么匹配两个条件(5个Excel函数组合一起使用)(7)

(4)INDEX(A$1:A$9,SMALL(IF($A$2:$A$9=$B$11,ROW($A$2:$A$9)),ROW(A1))):

INDEX函数的作用是:用来引用我们所需的信息。其有3个参数。第1个参数表示我们要引用的区域,第2个参数是表示要引用的行数,第3个参数表示要引用的列数。最终的结果就是引用区域内行和列的交叉内容。这里返回的结果是第2行、第5行和第6行单元格的内容,即“销售1部”。

excel函数公式怎么匹配两个条件(5个Excel函数组合一起使用)(8)

(5)IFFEROR函数。

我们上两步返回的值都有错误值,这样看起来感觉不是很好,为了将错误值变成空,可以使用IFFEROR函数,IFFEROR函数有2个参数,如果我们将第2个参数设置为空,公式执行时会判断是否有错误值输出,如果有,直接让错误值不显示。下图是没有使用IFFEROR函数得到的结果。

excel函数公式怎么匹配两个条件(5个Excel函数组合一起使用)(9)

以上就是小编今天要跟大家将的一对多查询的通用组合公式。相信大家看了这篇教程之后还是一脸懵逼的,看一遍没看懂就多看几遍,再跟着练习几次就可以学会了。

如学习过程中,有什么问题,可以在评论区找我。这篇教程写了3小时,希望您可以转发和点赞此篇文章,给小编鼓励支持一下。

您的转发和点赞就是对小编最好的鼓励和支持!您的每一份赞赏、转发、评论、点赞、收藏都将成为我写出更多优质教程的动力!

私信发送002可获得本次教程的Excel表格练习模板!

,