未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
vlookup常用套路合集(10.1加餐)

小伙伴们好啊,今天就是国庆节了。在这个特殊的日子里,我总结了vlookup函数的各种常见用法,就当作加餐,值得学习和收藏。

-vlookup-

常用套路合集

vlookup函数是一个查找引用函数,工作中也会经常使用,它的函数结构为:

vlookup(查找的值,查找的区域,返回的列数,查找的方式)

1.常规用法(精确查找)

下图是一个员工信息表,现在要根据G3单元格的编号在员工表中查询出对应的姓名,在H3单元格输入公式=VLOOKUP(G3,A2:E11,2,0)

2.查询整行数据(column作为第3参数)

现在要根据G3单元格的编号查询出整行数据,结果如右表所示。在H3单元格输入公式=VLOOKUP($G3,$A2:$E11,COLUMN(B1),0),向右填充。第3参数用的是column函数,它是返回单元格的列号,B1单元格的列号是2,右拉时列号递增1。

3.区间查询取值(模糊查找)

现在要根据工龄查询奖金,而工龄在一个区间范围内,如右表所示。比如工龄大于等于1年,小于2年的奖金是100,其他以此类推。在F列添加个辅助列,写入起始工龄,如图所示。然后在D3单元格输入公式=VLOOKUP(C3,F$3:H$6,3,1),向下填充。第4参数为1是模糊查找,也可以直接省略不写。

4.逆向查询(用到if或choose构建二维数组)

现在要根据G3单元格的姓名来逆向查找编号,用vlookup的普通用法肯定是做不到的。在H3单元格输入公式=VLOOKUP(G3,IF({1,0},B3:B11,A3:A11),2,)。这里的if函数是为了将A列和B列的位置对调。将B3:B11作为二维区域的第1列,A3:A11作为二维区域的第2列,这样就可以根据姓名查找编号了。也可以用choose函数,公式为=VLOOKUP(G3,CHOOSE({1,2},B3:B11,A3:A11),2,)

5.多条件查询(用到if和连接符&)

现在要查询出学历是高中且部门是生产部所对应的姓名,这是个多条件查询问题。在I3单元格输入公式=VLOOKUP(G3&H3,IF({1,0},C3:C11&D3:D11,B3:B11),2,),按ctrl+shift+enter。首先将C3:C11和D3:D11连接起来作为查询区域的第1列,这样将多条件合并为一个条件。然后将B3:B11作为查询区域的第2列,这2列要用if函数组合在一起,成为1个二维数组。查询的值也要连接起来,为G3&H3,返回第2列,精确查找。

6.一对多查询(这里用到辅助列)

一对多查询就是满足一个条件的有多个结果,要把多个结果分别提取出来。现在要查询出销售部所对应的所有姓名。首先在F列添加一个辅助列,在F3单元格输入公式=COUNTIF(D$3:D3,H$3),向下填充,这样销售部第1次出现对应1,第2次出现对应2......然后在I3单元格输入公式=IFNA(VLOOKUP(ROW(A1),IF({1,0},F$3:F$11,B$3:B$11),2,),""),向下填充。当然也可以不用辅助列,就是公式比较长。

7.多值查询(第1参数是数组,要用到t/n(if({1},区域))结构)

之前都是查询一个值,现在要查询多个值,也就是第1参数是数组。看下面的题目,求编号B,D,H所对应的工龄之和。你可能会想到直接在vlookup第1参数用数组,查询出编号B,D,H所对应的工龄,然后sum求和。但是vlookup第1参数是不支持数组的,想要用数组,就要用到t或n(if({1},区域))结构。在H3单元格输入公式=SUM(VLOOKUP(T(IF({1},G3:G5)),A2:E11,5,))

8.按指定次数重复内容

如下图所示,A列是要重复的内容,B列是重复的次数,最后的效果如E列所示。首先在C列添加一个辅助列,在C3单元格输入公式=SUM(B$3:B3),向下填充。然后在E2单元格输入公式=IFNA(VLOOKUP(ROW(A1),IF({1,0},C$3:C$6,A$3:A$6),2,),E3)&"",向下填充。

练习文件链接:

https://pan.baidu.com/s/12ZyGXgTXxmWkqt-tQWC8tA

提取码:u6sj

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP阅读全文并永久保存 更多类似文章
猜你喜欢
类似文章
使用VLOOKUP函数返回查找到的多个值的方法
关于VLOOKUP的第一个参数,我有话要说……
“完成率”在excel中这样显示才漂亮!
你还不会逆向查找?这三个小技巧分享给你!
IF条件函数10大用法完整版,全会是高手,配合SUMIF,VLOOKUP更逆天
COUNTIFS函数9种高级用法详解,条件统计重复值,告别加班涨工资
更多类似文章 >>
生活服务
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!