Vlookup函数的使用方法(入门 进阶 高级 最高级 最新用法)
Vlookup函数—Excel中使用频率最高的函数,没有之一。
Vlookup函数,这个工作中最常用的函数,当之无愧的Excel查找函数之王。
其实很少有人能够敢说自己精通Vlookup函数。
本文整理了Vlookup函数从入门到高级的全系列教程,并且加入了许多最新用法,强烈建议收藏备用!
一、基本语法
二、入门篇
三、进阶篇
四、高级篇
五、新版本中的最新用法
一、基本语法
查找的值:要查找的值 查找区域或数组:包含查找值字段和返回值的单元格区域或数组 返回值的在列数:返回值在查找区域的列数 精确or匹配查找:值为0或False为精确查找,值为1或true时匹配查找。
二、入门篇
=VLOOKUP(G2,B:E,4,0)
注:
G2:是要查找的值 B:E:是查找区域。因为要查找的姓名在第2列,所以区域也要从B列开始。 4:是基本工资在B:E区域中的第4列 0:是精确查找
2、查找不到时返回空
=IFERROR(VLOOKUP(G2,B:E,4,0),'')
注:IFERROR函数可以把错误值转换为指定的值,本例公式中转换为空
三、进阶篇
=VLOOKUP(G2,IF({1,0},B1:B8,A1:A8),2,0)
注:公式中用IF({1,0} 把B列和A列组合在一起,并把 B列放在A列前面。
2、包含查找
=VLOOKUP('*'&G2&'*',B:E,4,0)
注:查找值两边连接通配符*号可以实现包含查找
3、区间查找
【例】根据销量查找对应区间的提成:
=VLOOKUP(D2,A:B,2,1)
4、含通配符的查找
=VLOOKUP(D2,A:B,2,0)
=VLOOKUP(SUBSTITUTE(D2,'*','~*'),A:B,2,0)
注:把*用函数替换为~*后就可以正常查找了
5、横向多列查找
=VLOOKUP($G2,$B:$E,COLUMN(B1),0)
注:用Column()函数生成动态数字,作为Vlookup第3个参数,一个公式向右复制即可查找全部
6、多区域查找
=VLOOKUP(B2,IF(A2='销售一部',A5:B9,D5:E9),2,0)
四、高级篇
1、多条件查找
【例】根据部门和姓名查工资
=VLOOKUP(E2&F2,IF({1,0},A2:A8&B2:B8,C2:C8),2,0)
注:先把A列和B列连接在一起,再用IF({1,0} 把它和C列组合在一起构成8行2列的数组,作为Vlookup的第2个参数
2、合并单元格查找
=VLOOKUP(VLOOKUP('座',D$2:D2,1),A:B,2,0)
注:VLOOKUP('座',D$2:D2,1)可以返回D列截止本行的最后一个非空值。
3、合并单元格查找
=VLOOKUP(F2,OFFSET(B$1,MATCH(E2,A:A,)-1,):C99,2,0)
4、一对多查找
【例】查找出人事部所有员工
数组公式输入完成后按Ctrl+Shift+Enter结束后自动添加大括号
{=VLOOKUP(E$2&ROW(A1),IF({1,0},A$2:A$8&COUNTIF(INDIRECT('a2:a'&ROW($2:$8)),E$2),B$2:B$8),2,0)}
ROW($2:$8)) :生成2,3,4,5,6,7,8 INDIRECT('a2:a'&row : 生成行数逐渐增多的7个区域 COUNTIF(INDIRECT : 在7个区域中分别计算部门的个数,相当于给人事部生成编号 IF({1,0} : 把带编号的部门和B列构成7行两列的新数组
5、查找所有值放在一个单元格
E2=D2&','&IFERROR(VLOOKUP(C2,C3:E$12,3,),'')
G2=VLOOKUP(F2,C:E,3,)
6、查找最后一个
=VLOOKUP(1,IF({100,0},0/(B2:B10='A'),C2:C10),2)
7、跨多表查找
【例】从各部门中查找员工的基本工资,在哪一个表中不一定。
方法1
=IFERROR(VLOOKUP(A2,服务!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,综合!A:G,7,0),IFERROR(VLOOKUP(A2,财务!A:G,7,0),IFERROR(VLOOKUP(A2,销售!A:G,7,0),'无此人信息')))))
=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({'销售';'服务';'人事';'综合';'财务'}&'!a:a'),A2),{'销售';'服务';'人事';'综合';'财务'})&'!a:g'),7,0)
五、Office365中的新用法
1、批量查找
=Vlookup(D2:D12,A:B,2,0)
2、多列查找
=VLOOKUP(A11,A1:E7,{2,3,5},0)