函数教程第24课：聊下常用查询类函数的差异和优劣

付费

Excel星球 原创 2019-08-25 已编辑投诉编辑记录

阅读数：73015

关于博主：

excel星球

扫码关注公众号Excel星球，后台回复关键字：新手、VBA、SQL、礼包、短视频等即可获取相关图文教程或资源~

各位好：

过去的几章，我们把工作中最常用的数据查询类函数都学了一遍，比如VLOOKUP/INDEX+MATCH/LOOKUP等。有朋友就问了，它们一个比一个强大，那我工作中到底应该用哪个函数呢？所以我们今天就来聊一下这三个函数的差异和优劣。

先看一个小例子：

假设我们需要根据A:B列的数据，在E列，求D列人员的考试成绩。。。

也就是工作中我们常常遇到的单条件查询的问题。

最常用的有以下三种解法：

=VLOOKUP(D2,A:B,2,0)

如果你还对VLOOKUP不熟悉，参考文章：

NO.14：从头细说条件查询必会函数VLOOKUP

=INDEX(B:B,MATCH(D2,A:A,0))

如果你还对INDEX+MATCH的函数组合不熟悉，参考文章：

NO.19：一个比VLOOKUP强大百倍的组合函数：INDEX+MATH

=LOOKUP(1,0/(A$2:A$6=D2),B$2:B$6)

如果对LOOKUP函数不熟悉，参考文章：

NO.21：LOOKUP函数原来是这么回事

偶尔也会使用SUMIF：

=SUMIF(A:A,D2,B:B)

如果你还不会SUMIF函数，参考文章：

NO.09：条件求和必会函数SUMIF(S)

但SUMIF要求查找结果必须为数值，而且结果唯一。因为是数值，才能用SUMIF求和，因为结果唯一，所以求和后方能不出错儿。

下面我们重点说下VLOOKUP/INDEX+MATCH/LOOKUP三种解法的异同和优劣。

1），先说下数据结构的适应性。

VLOOKUP默认查找范围的首列即为匹配列，这就要求查找值必须在查找范围的首列。这样的好处是，我们不需要再使用另外一个函数（比如MATCH)或者数组运算去指定匹配列，坏处是，如果匹配列不在查找范围的首列，VLOOKUP就没辙了。

有人说未必，可以用=vlookup(d2,if({1,0},b:b,a:a),2,0)去处理。

这个我们讲过了，该套路使用函数if({1,0}，把匹配列调到了查询范围的首列，也就是使用内存数组的方式，把两列数据调换（约等于遍历了两次数据源），然后才开始查询匹配，计算效率太差；另外，该公式还需要使用数组三键输入，编写维护并不方便，所以并不推荐使用。

与之相比，INDEX+MATCH的组合函数就自由多了。

它通过MATCH函数来指定匹配列或行，最后由INDEX取值。它不需要查找值一定在查找范围的首列，但它需要书写两个函数(INDEX+MATCH)，这大概就是他没有VLOOKUP辣么受欢迎的原因——人性本懒，谁不想计算机多做事，自己少做事？哪怕少敲一个函数那也是贼好的啊！

LOOKUP(1,0/(条件区域=条件),结果区域)的查询套路，也不需要查找值在查找范围的首列，它通过0/(条件区域=条件)的形式来指定并计算匹配列，但相比VLOOKUP和INDEX函数而言，这个套路运算效率并不高，为什么效率不高？下面解释。

2），再说下三个函数的计算过程和效率

正如我们在VLOOKUP教程里所讲的，VLOOKUP属于找到即止类型。什么是找到即止？以本文的示例为例，它首先在A列寻找查找值，比如搜索“看见星光”，一旦找到该值，它就不再向下查找了，直接按第3参数指定的数字，向右偏移列取出结果，盖木欧瓦。

INDEX+MATCH组合和VLOOKUP一样，也属于找到即止型，所以两个函数都属于函数世界中的高效类型。

这种类型的函数有一个巨大的好处：数据范围可以整列或整行引用。当你的数据源数据扩大时，函数公式并不需要重新编写。

有的朋友可能会认为整列引用会造成它们运算量虚大，如果你看过我之前的图文教程自然知道这是误解。

=LOOKUP(1,0/条件区域=条件值，结果区域)则不属于找到即止型。

它是对数据源彻底遍历，而且遍历计算了两遍。第一遍，判断A2:A6是不是等于D2，返回一个由逻辑值构成的内存数组，第二遍用0除以该内存数组，得到一个由0和错误值构成的新内存数组，最后才是取结果。

此外，从2016版开始，微软对VLOOKUP/HLOOKUP/MATCH的运算机制进行了强力优化，从相同表区域查找多个列时，将为所搜索的列范围创建内部缓存的索引，任何后续查找中，将重用这一缓存的索引——在Excel2016版中，VLOOKUP计算几十万行数据，也可以不费吹灰之力。

所以三个套路中，LOOKUP(1,0/条件区域=条件值,结果区域)的运算效率是最差的。

那么，为什么LOOKUP(1,0/条件区域=条件值,结果区域)这个套路还是有很多人在用呢？

原因在于，这个套路非常适合多条件数据查询。

例如并且关系的多条件查询套路：

=LOOKUP(1,0/((条件区域1=条件值1)*(条件区域2*条件值2)),结果区域)

虽然在多条件查询套路中，它的效率低于数组公式=INDEX(结果列，MATCH(查找值1&查找值2, 条件列1&条件列2,0)，但是，LOOKUP默认支持它的参数执行多项运算，在编写维护上也就不需要使用数组三键，这是非常方便的，另外，它也不需要特殊处理真空和假空的陷阱问题，所以在多条件查询的问题上，我们通常更推荐使用LOOKUP函数。

3），最后说下三者返回结果的不同

这点听起来有点绕，什么叫返回结果不同？不都是返回查询结果吗？

VLOOKUP和LOOKUP函数返回的是值。

例如本章示例，公式=VLOOKUP(D2,A:B,2,0)，返回的是值150.

但INDEX函数不是这样的，它返回的结果是单元格引用。

公式：=INDEX(B:B,MATCH(D2,A:A,0))，返回的结果是B4单元格的引用，只是由于值是单元格的默认显示属性，所以才显示为B4单元格的值150。

值和单元格引用有什么区别？

值只是单元格众多属性中的一个，除了值属性，单元格还有填充色、地址、格式、宽度、高度等各种属性。

那么，INDEX的这点特长有什么用处？

例如公式：=SUM(INDEX(B:B,MATCH(D2,A:A,)):B7)，可以返回指定位置开始和结束的数据总和。

此外更重要的，INDEX可以用于制作动态图片查询，参见另一篇推文：

如何使用Excel制作动态引用员工照片的档案查询表...