细数LOOKUP系列函数在Excel数据查询中的应用

​数据查询是Excel数据处理中的一项核心业务，也是日常办公中使用频率非常高的一项操作 。而LOOKUP系列函数则是数据查询最基础的方法，也是日常办公中必须掌握的一项Excel技能。

LOOKUP系列函数指LOOKUP、VLOOKUP、HLOOKUP这三个函数（以及其他的扩展函数），其中应用最广泛的自然要数VLOOKUP了。本文将会逐一介绍VLOOKUP函数的各种应用场景，及其相应的方法技巧，并深入分析每种查询方法的特点和注意事项。同时，对查询操作涉及到的其他典型函数（或组合）应用进行简要介绍。

本文内容较长，先给大家梳理一下文章目录。

一、目录

二、Excel中的数据查询基础知识

三、正向查询和逆向查询

1.用VLOOKUP进行正向查询

2.用VLOOKUP进行逆向查询

3.更加便捷的查询方法INDEX+MATCH

四、用VLOOKUP进行精确查询和模糊查询

五、多条件多结果查询

1.一对一查询

2.一对多查询

（1）用VLOOKUP函数，借助辅助列查询多个结果

（2）借助“Excel扩展函数包”查询多个结果

（3）用INDEX+SMALL+IF函数组合查询多个结果

3.多对一查询

4.多对多查询

六、总结

二、Excel中的数据查询基础知识

如下图所示的一张表格，如果按照【姓名】查询对应【身份证号】叫作“正向查询”反之则叫作“逆向查询”。

如果查询包含“渔坪村”这个关键字的信息，叫作“模糊查询”，反之如果查找“高峰镇渔坪村3组”这个完整字段信息，则叫作“精确查询”。

如果是查询【身份证号】对应的【姓名】，叫作一对一查询，即一个条件一个查询结果。同理，根据【姓名】【班级】这两个字段，来查找对应的【身份证号】则叫作多对一查询，比如下表中的“李杰”有两个，一个在2班，一个在3班，只根据【姓名】一个字段，无法准确定位到正确的身份证号，因此需要进行“多对一查询”（也叫多条件查询）。至于一对多查询、多对多查询也是类似的道理。

三、正向查询和逆向查询

1. 用VLOOKUP进行正向查询

正向查询是VLOOKUP函数最基础的用法。如下图所示，我们要查找金凯风的班级信息，只需在I3单元格输入公式=VLOOKUP(H3,A2:F17,2,0)，确定即可。

这个公式的基本原理如下图所示。我们在A2:F17这个范围来查询H3单元格（金凯风）对应的信息，返回对应查询范围A2:F17的第2列（班级）信息，最后一个参数一般为0（或FALSE），表示精确查找。

后面介绍的所有关于VLOOKUP函数的查询方式也都是基于上述原理，只是稍加变通而已。

1. 用VLOOKUP进行逆向查询

从本质上说，VLOOKUP函数只能进行上述类似的正向查询，即条件列必须在结果列之前。但是我们可以通过互换两列的位置来实现逆向查询。当然这里的互换位置不是真的要改变原表结构，而是通过数组重装的的方式来实现。

比如，如下图所示，现在要根据身份证号查询对应姓名信息，可以用如下公式实现：

=VLOOKUP(H3,IF({1,0},C2:C17,A2:A17),2,0)

这里的第2个参数依然表示查询范围，但却并不像A2:F17这么直接，而是用一个IF函数互换了C列和A列数据的前后顺序。相当于查询范围变成了如下图所示的结果，它是一个16行，2列的数据块儿。我们需要返回的结果在这个范围的第2列，因此第3个参数才是2。最有一个参数为0，同样表示精确查找。

我们可以选中上述公式的IF({1,0},C2:C17,A2:A17)部分，按F9键查看这个重装之后的数组。

1. 更加便捷的查询方法INDEX+MATCH

其实在Excel中有这么一个函数组合，可以无视查询条件和返回结果的前后顺序，逆向查询和正向查询一样简单，它就是INDEX+MATCH函数组合。

如下图所示，在I3单元格输入公式=INDEX(C2:C17,MATCH(H3,A2:A17,0))即可得到查询结果。

INDEX+MATCH函数组合的工作原理如下：

四、用VLOOKUP进行精确查询和模糊查询

在90%以上的情境中我们用的都是精确查询，正如上文所示的查询案例一样。但有时候模糊查询也能取得意想不到的效果。

比如我们现在要查询一个“渔坪村”的学生，就只能根据家庭住址中包含“渔坪村”关键字的信息进行查询。其实原理也很简单，就是利用通配符*来进行模糊匹配。

如下图所示，查询公式为=VLOOKUP("*"&H3&"*",IF({1,0},D2:D17,A2:A17),2,0)。

五、多条件多结果查询

1. 一对一查询

上文所示的正向查询和逆向查询案例，都是一对一查询的典型应用，也即根据一个查询条件，查询得到一个结果。因此VLOOKUP的一对一查询就不再赘述。

1. 一对多查询

一对多查询，即根据一个条件查询出多个满足条件的结果。比如，我们可以一次性查询出所有的贫困户学生的姓名。虽然通过筛选也可以做好这件事，但很多时候我们需要的只能是查询的方式。

这里有3种方法，第1种方法是Excel原生的VLOOKUP查询，但是需要借助一个辅助列，步骤相对稍微复杂；第2种方法非常简单，但是需要借助一个叫做“Excel扩展函数包”的插件；第3种方法最复杂，是利用INDEX+SMALL+IF函数组合来实现一对多查询。下面逐一介绍。

（1）用VLOOKUP函数，借助辅助列查询多个结果

第一步，建立一个辅助列，一般情况下最好是在表格的最前面插入一列，方便正向查询，如下图所示。

然后，在A3单元格输入公式=($I$3=F3)+N(A2)，其作用是用来统计F3:F17这个范围内，“是”第几次出现。

这个公式可以这么来理解：

$I$3=F3判断I3和F3是否相同，如果相同返回TRUE，在计算中会自动转化为数字1。注意这里的$I$3这种绝对引用方式，其目的是为了在向下拖动的过程中，I3单元格的引用始终不变，即始终拿I3和F列数据进行比对。

公式后面的+N(A2)是最巧妙的地方，如果没有这部分，那么辅助列返回的就是1,1,0,1,0,0...这样的结果。N(A2)函数是用来取得A2单元格的数值，如果是非数值，则返回0，这样才能得到累加的效果。

第二步，用VLOOKUP进行查询。

在J3单元格输入公式=VLOOKUP(ROW(A1),$A$2:$B$17,2,0)，然后向下拖动（或双击右下角）填充至最后一行。这样就查询出了所有的贫困户学生姓名。

这个公式中查询条件大家也发现了，并不是I3单元格，而是ROW(A1)，而ROW(A1)=1，再向下拖动过程中公式中的ROW(A1)会变成ROW(A2)、ROW(A3)...对应数字1、2、3...也就是说我们是在查询贫困户第几次出现，即A列对应的信息。

有了前文的正向查询的基础，公式剩下的部分就好理解了。

第三步，用IFERROR函数美化查询结果。

我们也发现了除了查询出所有贫困户学生姓名，还在末尾出现了一些#N/A符号，它表示查询不到结果。我们可以通过在原公式外面套一个IFERROR函数即可解决问题。对应公式为=IFERROR(VLOOKUP(ROW(A1),$A$2:$B$17,2,0),"")表示，如果查询不到则留空。

注意事项：公式中的第一个参数一定要采用$A$2:$B$17这样的绝对引用方式。

（2）借助“Excel扩展函数包”查询多个结果

第一步，先去“方方格子”官网下载“Excel扩展函数包”，根据自己电脑安装的OFFICE版本，选择安装32位还是64位，一般情况下安装32位即可。

然后就可以在Excel中进行一对多查询了。

这里用到的函数是MLookup，它和VLOOKUP函数非常类似，比如查询所有姓名为“李杰”的班级信息，只需在I3单元格输入公式=MLookup(H3,A2:B17,2,-1)即可，返回的所有查询结果会用逗号分隔。

MLookup函数最后一个参数表示返回第几个查询结果，如果为-1，则表示返回所有查询结果。

这个MLookup函数虽然好用，但是也有缺点，它无法进行逆向查询，比如无法查询出所有贫困户学生的姓名。其原因在于MLookup不支持数组查询，所以通过重整数组顺序进行逆向查询的方法是行不通的。

（3）用INDEX+SMALL+IF函数组合查询多个结果

其实一对多查询在日常使用中很少用VLOOKUP函数，更多的则是使用INDEX+SMALL+IF函数组合来实现。

先在I3单元格输入如下公式，然后按CTRL+SHIFT+ENTER三键确定，最后用鼠标拖动向下填充即可。

=IFERROR(INDEX($A$1:$A$17,SMALL(IF($E$3:$E$17=$H$3,ROW($A$3:$A$17)),ROW(A1))),"")

这是一个数组公式，因为相对比较复杂，就不做详细解释，其主要思路是用SMALL和IF两个函数配合，查询对应行号，然后用INDEX函数定位对应行的数据。

1. 多对一查询

“多对一查询”又叫“多条件查询”，比如根据【姓名】和【班级】查询对应的身份证号。

如下图所示，叫“李杰”的同学有两个，无法根据【姓名】这一个条件精确定位其【身份证号】，还需要一个【班级】条件。

这个时候VLOOKUP就有些不太好用了，还得LOOKUP函数来救场。如下图所示，我们在J3单元格输入公式=LOOKUP(1,0/((A2:A17=H3)*(B2:B17=I3)),C2:C17)，回车确定，这样就可以查询到2班李杰的身份证号。

LOOKUP函数在进行多条件查询中的应用可以套用如下公式：

LOOKUP(1,0/(条件1*条件2*...*条件n),返回结果的区域)

LOOKUP函数有一个特点，就是如果找不到查询值，则返回查询区域中小于或等于查询值的最大值。正是利用LOOKUP函数的这个特点，才能实现多条件查询。

1. 多对多查询

多对多查询可以看做是“一对多查询”和“多对一查询”的组合，这里用前面介绍的INDEX+SMALL+IF函数组合来做。

如下图所示，在J3单元格输入如下公式：

=IFERROR(INDEX($A$1:$A$17,SMALL(IF(($B$3:$B$17=$H$3)*($E$3:$E$17=$I$3),ROW($A$3:$A$17)),ROW(A1))),"")

然后，还是用CTRL+SHIT+ENTER三键确定输入。

其实参考前文介绍的“用INDEX+SMALL+IF函数组合查询多个结果”，这个公式唯一的变化就是把条件由$E$3:$E$17=$H$3变成了($B$3:$B$17=$H$3)*($E$3:$E$17=$I$3)，即两个条件相乘的形式。现在如果让你根据三个条件查询，也是轻而易举的，是需要稍微修改上述公式即可。

六、总结

Excel中的数据查询是一个相对比较大的话题，文章中介绍的LOOKUP系列函数查询应用示例，仅占其中的一小部分，更多的相关内容，以后再慢慢给大家分享。也欢迎大家分享自己在日常办公中的一些Excel使用经验。

关于文中的一些相对复杂的公式并没有做太过深入的解读，因为任何一个公式单独拿出来讲，其都足以写出一篇长文。但如果有朋友对其感兴趣，想深究其原理，也可以私信交流。