江门新佳教育
Vlookup函数的使用方法(入门+进阶+高级+最高级+最新用法)-江门电脑培训

Vlookup函数的使用方法(入门+进阶+高级+最高级+最新用法)

N年前,兰色曾写过一篇Vlookup从入门到高级的全系列教程,被无数网站转载和抄袭。过了这么多年,Vlookup函数的新用法又陆续发现了不少,所以兰色觉得有必要再次整理一下这个工作中最常用函数用法。包含以下内容:


一、基本语法


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:是精确查找

image.png

2、查找不到时返回空

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

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

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

image.png

三、进阶篇

1、反向查找

【例】根据姓名查部门

=VLOOKUP(G2,IF({1,0},B1:B8,A1:A8),2,0)

注:公式中用IF({1,0} 把B列和A列组合在一起,并把 B列放在A列前面。

image.png

2、包含查找

【例】查找含“一”的姓名对应的基本工资

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

注:查找值两边连接通配符*号可以实现包含查找

image.png

3、区间查找

【例】根据销量查找对应区间的提成

=VLOOKUP(D2,A:B,2,1)

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

image.png

4、含通配符的查找

【例】型号查找单价

错误公式:

=VLOOKUP(D2,A:B,2,0)

image.png

正确公式:

=VLOOKUP(SUBSTITUTE(D2,"*","~*"),A:B,2,0)

注:把*用函数替换为~*后就可以正常查找了

image.png

5、横向多列查找

【例】根据姓名查找性别、年龄和基本工资

=VLOOKUP($G2,$B:$E,COLUMN(B1),0)

image.png

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个参数


image.png

2、合并单元格查找

【例】查找所在部门的奖金

=VLOOKUP(VLOOKUP("座",D$2:D2,1),A:B,2,0)

注:VLOOKUP("座",D$2:D2,1)可以返回D列截止本行的最后一个非空值。

image.png

3、合并单元格查找

【例】根据公司、产品查找对应价格

=VLOOKUP(F2,OFFSET(B$1,MATCH(E2,A:A,)-1,):C99,2,0)

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

image.png

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行两列的新数组

image.png

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

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

公式:E2=D2&","&IFERROR(VLOOKUP(C2,C3:E$12,3,),"")

G2=VLOOKUP(F2,C:E,3,)'

image.png

6、查找最后一个

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

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

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

image.png

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)


image.png

电脑培训项目推荐

办公全能班
办公全能班
投资额:咨询客服
热度:
我要加盟
初级会计新锐班
初级会计新锐班
投资额:咨询客服
热度:
我要加盟
初级职称精品班
初级职称精品班
投资额:咨询客服
热度:
我要加盟
拼多多开店推广班
拼多多开店推广班
投资额:咨询客服
热度:
我要加盟
中山大学教育学院
中山大学教育学院
投资额:咨询客服
热度:
我要加盟
电脑培训排行榜
  • 1室内设计师(就业班)948
    室内设计师(就业班)
    投资额:咨询客服
    热度:
    查看详情>>
  • 23d Max效果图班942
    3d Max效果图班
    投资额:咨询客服
    热度:
    查看详情>>
  • 3Vray高级渲染培训913
    Vray高级渲染培训
    投资额:咨询客服
    热度:
    查看详情>>
  • 4CAD施工图实操班891
    CAD施工图实操班
    投资额:咨询客服
    热度:
    查看详情>>
  • 5助理室内设计师班887
    助理室内设计师班
    投资额:咨询客服
    热度:
    查看详情>>
  • 6办公全能班626
    办公全能班
    投资额:咨询客服
    热度:
    查看详情>>
  • 7广州美术学院城市学院525
    广州美术学院城市学院
    投资额:咨询客服
    热度:
    查看详情>>
  • 首页 |教学特色 |法律声明 |联系我们 |正在咨询 |公司动态