理解VLOOKUP函数第4个参数的真实含义

时间: 2023-01-26

VLOOKUP函数是大家最喜欢使用的函数之一，能够帮助我们实现很多任务。这里，让我们回过头来从细节处着手，再次深入探究VLOOKUP函数，进一步熟练掌握这个常用的函数。

VLOOKUP函数有4个参数，其中第4个参数是可选参数，如果我们在公式中省略这个参数，有时会得到错误的结果。如图1所示，在单元格区域A2:B6中查找编号为“2010”的名称，结果是“洗衣机”，很显然是错误的，应该是“冰箱”。

图1

如果将编号排序，就会得到正确的结果“冰箱”，如图2所示。

图2

在VLOOKUP函数中，虽然第4个参数可省略，但省略后其默认为TRUE，即近似匹配，要求所查找的数据必须按升序排列，否则就会得出意想不到的结果，如图1所示。

如果查找的数据列没有按升序排列，则应显式指定第4个参数值为FALSE，即精确匹配，才能得到正确的结果，如图3所示。

图3

因此，当VLOOKUP函数的第4个参数被忽略或者为TRUE时，将执行近似查找，所查找的数据列必须要按升序排列，以获得正确的结果。反之，如果所查找的数据列没有排序，那么VLOOKUP函数的第4个参数应设置为FALSE，执行精确查找。

下面再进一步探究。

如图4是一个收入—税率表。

图4

从图4中可以看出，如果某员工的收入是5000元，那么他要交税的税率为5%。如果另一位员工的收入是18000，他就要交税率为15%的税。

就上述两种情形来说，图4的表中没有一个精确的数字和实际收入相匹配，只能查找到实际收入介于的区间，从而找到相应的税率。这就是VLOOKUP函数第4个参数的真实含义，这也是为什么该参数的英文名为“range_lookup”。

因此，当VLOOKUP函数的第4个参数是TRUE或者被忽略时，告诉Excel去执行一个区间查找。进一步说，就是查找图4列A中的值，该值要等于或者小于但最接近所查找的值。因此，图4中的查找表可以简化为下图5.

图5

此时，使用第4个参数为TRUE或者忽略的VLOOKUP函数查找时，当查找的值大于等于0且小于3000时，返回税率为0；当查找的值大于等于3000且小于8000时，返回税率5%；依此类推。

这也是为什么查找列的数据必须要按照升序排列的原因。

示例1：基本的查找

如图6所示，要求查找收入为10000元的员工要交税的税率，公式为：

=VLOOKUP(A10,A2:B6,2,TRUE)

图6

示例2：查找日期

VLOOKUP函数能够查找最接近的日期，如图7所示。在单元格E2中输入公式：

=VLOOKUP(D2,$A$2:$B$4,2,TRUE)

向下拉至单元格E6。

图7

示例3：单列查找

查找表可以仅有1列，从这1列中查找并返回区间开始点的值。如图8所示，为2018年2月份每周一所在的日期。

图8

现在，在图8所示的查找表中查找指定的日期并返回该日期所在周一的日期，结果如图9所示。

图9

在单元格D2中的公式为：

=VLOOKUP(C2,$A$2:$A$6,1,TRUE)

向下拖至单元格D7。

示例4：比较两个列表

有时，我们需要比较两个列表，确定一个列表中包含另一个列表中的哪些项目。如图10所示，有“库存”表和“用户需求”表，要求根据“用户需求”表中的编号来查找“库存”表中相应编号的价格并将找到的结果输入到“用户需求”表中。

图10

可以使用VLOOKUP函数来完成，如图11所示。

图11

在单元格F3中输入公式：

=VLOOKUP(D3,$A$3:$B$6,2)

下拉至单元格F8。

咋一看，似乎任务完成！但仔细看一下，发现“用户需求”表中的编号1003和1005在“库存”表中不存在，但仍然得到了结果。阅读到这里的读者应该知道，公式中的VLOOKUP函数的第4个参数忽略，告诉Excel执行近似（区间）查找，此时Excel会找到小于但最接近查找值的值并返回相应的结果。但我们的这个示例中，并不需要这样。我们想要的是，如果找到的数据，就返回相应的值，没有找到就算了。

此时，应该将VLOOKUP函数的第4个参数明确设置为FALSE，当没有找到数据时，返回#N/A。如下图12所示。