编按:说起查找引用,想必大家也很熟悉了,Excel里也自带了许多查找引用的函数。今天就来给大家系统讲讲经典的四大查找:一对一查找、一对多查找、多对一查找、多对多查找。神秘、典型的万金油公式在多个地方大显身手

不管是一个条件查找返回多个结果,还是多条件查找返回一个、多个结果,它都有不俗表现。

“查找”是使用EXCEL过程中是非常普遍的工作,所以EXCEL设计了很多各式各样的“查询与引用类函数”

打开网易新闻 查看更多图片

今天就来给大家分享几种常见的“查找引用”情况和一些经典的嵌套函数、数组函数。

1、一对一查询:

一个匹配条件,对应一个匹配值。

这是最常见、最简单的条件匹配,同时也是解法最多的一种。

我们给出两种“快准狠”的方案,供大家选择:

1)=VLOOKUP($E$2,$A$1:$B$16,2,0),没什么可讲的了,VLOOKUP函数的基础用法。

2)=INDEX($B$2:$B$16,MATCH($E$2,$A$2:$A$16,0))

这个就是经常说到的INDEX+MATCH的经典组合了。通过MATCH函数查到匹配条件在A列中的位置序号,返回给INDEX函数作为第二参数,引出B列的内容。

一对一查找,大家记这两个就好了,其它的函数解法拿来学习学习思路即可。

2、一对多查询:

在匹配条件列中存在,但不局限于唯一性,导致有多个匹配值存在。

打开网易新闻 查看更多图片

这是一个经典用法“万金油”函数,这个函数的别称充分说明它的应用环境很广,同时也决定了它在写法上的变化很多。

我们列几个写法吧:

1)匹配条件对应字段2中的最大值

{=MAX(IF($A$2:$A$16=$E$2,$B$2:$B$16,""))},此函数返回13。

2)匹配条件第二次出现时对应字段2的值

{=INDEX($B$2:$B$16,SMALL(IF($A$2:$A$16=$E$2,ROW($1:$15),99^9),2))},此函数返回3。

3)列出所有匹配条件的值

{=IFERROR(INDEX($B$2:$B$16,SMALL(IF($A$2:$A$16=$E$2,ROW($1:$15),99^9),ROW(E1))),"")},下拉填充单元格,最后形成的效果如下图:

通过上面3个案例,我们可以看到“万金油”函数的几个共同点:

a、键入公式后,都需要按CTRL+SHIFT+ENTER三键结束的数组函数;

b、都是围绕IF函数的判断,形成新的数列,再使用SMALL或者LARGE函数来确定我们需要的序号,即可返回给INDEX函数索引出对应的匹配值。

这就是“万金油”的精髓,一般人我不告诉他。

3、多对一查找:

多个匹配条件确定一个匹配值的情况

两列条件决定一个匹配值,我们可以使用下列函数来解决。

1)=SUMPRODUCT(($A$2:$A$16=$F$2)*($B$2:$B$16=$F$3),$C$2:$C$16)

SUMPRODUCT是多条件匹配用的最多,而且写法最简单的写法,语法如下:

=SUMPRODUCT((【条件1区域】比较符【条件1】)*(【条件2区域】比较符【条件2】)*(【条件n区域】比较符【条件n】),【匹配值列】)

2)=LOOKUP(1,0/(($A$2:$A$16=$F$2)*($B$2:$B$16=$F$3)),($C$2:$C$16)),LOOKUP是利用了二分法运算原理做的函数思路,一定要注意条件需要用括号括起来(所有条件连乘之后,再用0除),语法如下:

=LOOKUP(1,0/((【条件1区域】比较符【条件1】)*(【条件2区域】比较符【条件2】)*(【条件n区域】比较符【条件n】)),【匹配值列】)

上面的例子是多对一查找的在一维数据上的匹配方式,还有一种特殊情况需要大家注意。

如果是下图所示的二维数据,又该如何操作呢?

打开网易新闻 查看更多图片

横纵交叉点匹配值,也属于多条件查询,我们此时还是使用INDEX+MATCH的经典嵌套函数解决:

=INDEX($B$19:$E$25,MATCH($H$18,$A$19:$A$25,0),MATCH($H$19,$B$18:$E$18,0)),用两个MATCH函数分别确定横纵匹配条件出现的序号,再返回给INDEX函数索引出横纵交叉点上的值。

4、多对多查找:

多个查找条件,匹配多个结果。

例如上图的案例,AN有两组对应值,我们需要匹配出所有的匹配值,可以使用下面的函数:

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF(($A$2:$A$16=$F$2)*($B$2:$B$16=$F$3),ROW($1:$15),9^9),ROW(F1))),"")}

这里还是用的万金油公式,鉴于篇幅原因,就不多解释了。

上面就是匹配查询问题的几种常见解题思路,一文写不尽整个EXCEL。

对于匹配查询的问题,还有很多的个案,只能具体问题具体分析,但是大部分个案都可以使用“万金油”解出,大家多多练习吧。

做Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

学习交流请加微信:hclhclsc进微信学习群。

八大查找函数公式,轻松搞定数据中的多条件查找

VLOOKUP&LOOKUP双雄战(四):在横向和逆向查询上的血拼!

Excel教程:如何制作带有层次和透视感的图表?

八大查找函数公式,轻松搞定数据中的多条件查找

版权申明:

本文作者E图表述;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。