Excel纵向查找函数使用详解（VLOOPUP 附图， 17个高级用法）

CDSY,CDSY.XYZ

背景

已知员工部门编码，需要关联上对应的部门名称；

简介

VLOOKUP函数是Excel中的一个纵向查找函数，它与LOOKUP函数和HLOOKUP函数属于一类函数，在工作中都有广泛应用，例如可以用来核对数据，多个表格之间快速导入数据等函数功能。

功能是按列查找，最终返回该列所需查询序列所对应的值；与之对应的HLOOKUP是按行查找的。

员工表

员工编码	员工姓名	部门编码	部门名称
E001	刘备	D001	-
E002	关羽	D002	-
E003	张飞	D003	-
E004	赵云	D004	-
E005	诸葛亮	D005	-

部门表

部门编码	部门名称
D001	技术部
D002	销售部
D003	人事部
D004	安保部
D005	行政部

公式

=VLOOKUP([@部门编码], 部门表, 2, )

输出结果

员工编码	员工姓名	部门编码	部门名称
E001	刘备	D001	技术部
E002	关羽	D002	销售部
E003	张飞	D003	人事部
E004	赵云	D004	安保部
E005	诸葛亮	D005	行政部

完美！！！

大纲

一、基本语法

1、参数详解

二、入门篇

1、单条件查找

2、屏蔽查找返回的错误值

三、进阶篇

1、反向查找

2、包含查找

3、区间查找

4、含通配符查找

5、多列查找

6、多区域查找

四、高级篇

1、多条件查找

2、合并单元格查找

3、带合并单元格的多条件查找

4、一对多查找

5、查找所有值放在一个单元格里

6、查找最后一个

7、跨多表查找

五、新版本中的最新用法

1、批量查找

2、多列批量查找

一、基本语法

=Vlookup(查找的值，查找区域或数组,返回值所在的列数,精确or匹配查找)

语法说明：

• 查找的值：要查找的值
• 查找区域或数组：包含查找值字段和返回值的单元格区域或数组
• 返回值的在列数：返回值在查找区域的列数
• 精确or匹配查找：值为0或False为精确查找，值为1或true时匹配查找。

二、入门篇

1、单条件查找

【例1】根据姓名查找基本工资

=VLOOKUP(G2,B:E,4,0)

注：

• G2：是要查找的值
• B:E：是查找区域。因为要查找的姓名在第2列，所以区域也要从B列开始。
• 4：是基本工资在B:E区域中的第4列
• 0：是精确查找

2、查找不到时返回空

【例1】根据姓名查找基本工资

=IFERROR(VLOOKUP(G2,B:E,4,0),"")

注：IFERROR函数可以把错误值转换为指定的值，本例公式中转换为空

三、进阶篇

1、反向查找

【例】根据姓名查部门

=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)

注：当最后一个参数为1或省略时，可以实现匹配或区间查找。规则是查找比被查找值小且最接近的值，并返回对应N列（第3个参数）的结果。如下图所示查找180，在A列查找比180小且最接近的值是100，返回100对应的提成3%。

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、多区域查找

【例9】根据不同的表从不同的区域查询

=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)

注：用Match查找出部门所在行数，然后用offset函数向下偏移B1，进尔和C99构成一个动态的区域。更简单的说就是部门在哪一行，我就用Vlookup从哪一行开始向下找。

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、查找所有值放在一个单元格

【例】在G列设置公式，根据F列产品从左表中查找所有符合条件的价格并用逗号隔开。

公式：

• E2=D2&","&IFERROR(VLOOKUP(C2,C3:E$12,3,),"")
• G2=VLOOKUP(F2,C:E,3,)'

6、查找最后一个

【例】查找A产品最后一次进货价格

=VLOOKUP(1,IF({100,0},0/(B2:B10="A"),C2:C10),2)

注：Vlookup最后一个参数省略时，可以象lookup进行二分法查找，用0/（条件）把不符合条件的变成错误值，符合条件的变成0，然后用一个足够大的数查找。IF后兰色故意把常见的1写成100，想让大家知道这个只要是非0的数字都可以。

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),"无此人信息")))))

方法2：

=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"销售";"服务";"人事";"综合";"财务"}&"!a:a"),A2),{"销售";"服务";"人事";"综合";"财务"})&"!a:g"),7,0)

五、office365中的新用法

1、批量查找

在最新的office365版本，查找再多行只需要设置一个公式的

E2单元格

=Vlookup(d2:d12,A:B,2,0）

2、多列查找

多查查找也可以只设置一个公式

=VLOOKUP(A11,A1:E7,{2,3,5},0）

CDSY,CDSY.XYZ