理解VLOOKUP函数第4个参数的真实含义
时间:2023-02-27 23:24:48
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所示。
图12
在单元格F3中的公式为:
=VLOOKUP(D3,$A$3:$B$6,2,FALSE)
现在,我们已经可以清楚地看到哪些编号对应的价格是“库存”表中没有的,因为它们会显示#N/A。
下面,我们稍作改进,让表格更加清楚地呈现结果,如图13所示。
图13
在单元格F3中的公式为:
=IFERROR(VLOOKUP(D3,$A$3:$B$6,2,FALSE),”没有库存”)
结语
在使用VLOOKUP函数时,你不能忽视其第4个参数,如果使用不恰当,会误导得出不正确的结果。然而,如果理解了第4个参数的真实含义并恰当运用,不仅能够更合理地使用VLOOKUP函数,而且可以有趣地使用VLOOKUP函数。
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
这些年苹果与IBM合作创造的这些成绩...
![](https://img.aspxhome.com/file/2023/3/a298675_0s.png)
Win7系统电脑提高开机速度的5个技巧
![](https://img.aspxhome.com/file/2023/3/a294709_0s.jpg)
skynetave.exe - skynetave是什么进程 有什么用
word怎么修改图片并保持形状格式不变
Win10专业版玩CSGO游戏总是闪退如何解决?
![](https://img.aspxhome.com/file/2023/26/a222645_0s.jpg)
完美关闭win7主题屏保让平台一直处于正常显示状态
![](https://img.aspxhome.com/file/2023/5/a316709_0s.jpg)
如何在Word2007中插入漂亮别致的页码
![](https://img.aspxhome.com/file/2023/3/24573_0s.jpg)
MAC重启Finder的技巧
![](https://img.aspxhome.com/file/2023/6/a216146_0s.jpg)
WinXP系统怎么修改注册表 注册表编辑器的两种打开方法
![](https://img.aspxhome.com/file/2023/4/a304641_0s.jpg)
怎么恢复wps ppt
Win10专业版玩游戏总是闪退怎么办?Win10专业版玩游戏闪退解决办法
![](https://img.aspxhome.com/file/2023/26/a223195_0s.png)
如何将word改为 utf-8编码?
![](https://img.aspxhome.com/file/2023/7/18137_0s.jpg)
仅需三步,WPS文字轻松插入结构图
![](https://img.aspxhome.com/file/2023/1/a164761_0s.jpg)
卡巴斯基免费版怎么防止授权文件被封?
![](https://img.aspxhome.com/file/2023/7/a331997_0s.jpg)
XP系统提示系统管理员设置了系统策略,禁止进行此项安装怎么办?
![](https://img.aspxhome.com/file/2023/4/a305586_0s.png)
Word标题前面的圆点项目符号怎么设置去除?
![](https://img.aspxhome.com/file/2023/5/31045_0s.png)
win7/8怎么查看电脑配置 win10电脑硬件配置查看方法
![](https://img.aspxhome.com/file/2023/28/a249453_0s.png)
Win10笔记本如何关闭NumLock?Win10笔记本关闭NumLock的方法
![](https://img.aspxhome.com/file/2023/26/a224967_0s.jpg)
win8系统任务栏怎么添加触屏键盘?win8任务栏添加触屏键盘的方法
![](https://img.aspxhome.com/file/2023/28/a245788_0s.png)
win11怎么删除任务栏系统托盘的图标缓存?
![](https://img.aspxhome.com/file/2023/30/a271457_0s.jpg)