二维码 购物车
部落窝在线教育欢迎您!

VLOOKUP经典用法12例

 

作者:小窝来源:部落窝教育发布时间:2023-09-27 17:11:58点击:1117

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。
编按:

VLOOKUP是几乎所有Excel用户都会的一个函数,本教程总结了它的12例经典用法,看看你会多少。

 

凡是求职简历中写Excel技能的,80%的人都写了熟练操作VLOOKUP函数。VLOOKUP函数似乎成了求职的一个敲门砖或者试金石了。

但是你真的熟练使用VLOOKUP吗?看看下面的用法你知道有多少。

1例:查找同一产品的多个列值

1)各值顺序与查找区域保持一致,搭配COLUMN

譬如下方,不需要笨些些地分别去修改第3参数获取各值,搭配COLUMN函数自动搞定。

低版本

=VLOOKUP($A21,$C$2:$G$17,COLUMN(B1),0),右拉填充

 

 

高版本(2021365版本,下同)

=VLOOKUP($A21,$C$2:$G$17,COLUMN(B1:E1),0)

 

 

2)各值顺序与查找区域不一致,搭配MATCH函数

低版本

=VLOOKUP($A21,$C$2:$G$17,MATCH(B20,$C$1:$G$1,0),0),右拉填充。

 

 

高版本

=VLOOKUP($A21,$C$2:$G$17,MATCH(B20:E20,$C$1:$G$1,0),0)

 

 

2例:多条件查找

直接把多个条件合并作为一个条件进行查找。

=VLOOKUP(A27&B27,IF({1,0},$A$2:$A$23&$C$2:$C$23,$F$2:$F$23),2,0)

 

 

3例:反向查找

可以搭配经典的IF函数{1,0}结构,也可以搭配CHOOSE函数进行选择。

=VLOOKUP(A27,IF({1,0},C2:C23,B2:B23),2,0)

或者

=VLOOKUP(A27,CHOOSE({1,2},C2:C23,B2:B23),2,0)

 

 

4例:包含查找(使用通配符查找)

查找包含了某某字符的数据,可以使用通配符进行查找。

通配符“*”,表示任意个数的任意字符;通配符“?”,表示一个任意字符。

譬如查找包含“鸡蛋”的品名。

=VLOOKUP("*"&A27&"*",C2:D23,2,0)

 

 

再譬如查找包含了“鸡蛋”并且“鸡蛋”前只有3个字符的品名:
=VLOOKUP("???"&B27,D2:D23,1,0)

 

 

5例:查找值包含了通配符的查找

1)查找值含有波浪号~

波浪号“~”是一种特殊通配符,作用是将其他通配符转化为普通符号。如果查找包含波浪号,必须在波浪号前再添加一个波浪号将其转化为普通符号使用,否则查找会出错。

正确的公式:

=VLOOKUP("86~~",A2:B8,2,0)

或者

=VLOOKUP(SUBSTITUTE(D2,"~","~~"),A2:B8,2,0)

 

图示描述已自动生成

 

2)查找值含有通配符*或者?

如果查找值含有通配符*或者?,同样需要在通配符前添加波浪号~将其转化为普通符号,否则可能出现错误。

 

图示, 表格, 示意图
描述已自动生成

 

6例:一对多查找

VLOOKUP默认只返回第一个符合条件的结果。如果需要返回所有符合条件的结果,可以添加辅助列为每个结果编上不同的序号,然后再用VLOOKUP查找序号返回结果。

譬如查所有含“鸡蛋”两字的品名。

Step 01 插入空列,输入公式=IF(IFERROR(FIND($B$27,D2),0)>0,A1+1,A1)并向下填充。

 

 

Step 02 C27中输入公式=IFERROR(VLOOKUP(ROW(A1),$A$2:$D$23,4,0),"")并向下拖动填充直到出现空单元格为止。

 

 

因为原始数据中品名存在重复,所以得到的品名也有重复。如果需要不重复,则修改序号公式即可。

=IF(COUNTIF($D$2:D2,D2)>1,"",IF(IFERROR(FIND($B$27,D2),0)>0,A1+1,A1))

 

 

7例:区间或等级查找

运用VLOOKUP的近似匹配功能可以实现区间或等级查找。两个条件:

1)省略第四参数,或者将其设置为1

2)查找区域首列升序排列。

譬如求销售业绩的等级。

=VLOOKUP(B2,$E$2:$F$5,2,1)或者=VLOOKUP(B2,$E$2:$F$5,2)

 

图片包含 表格
描述已自动生成

 

8例:提取15位以内统一位数的数字,如手机号

 

=VLOOKUP(0,MID(A2,ROW($1:$50),11)*{0,1},2,0)

 

表格
描述已自动生成

 

注:由于Excel的最大精度是15位,所以此法只能提取15位及以内的数字,不能用来提取银行卡号、身份证号等。

 

9例:核对数据

1)核对是否一致

譬如核对两份数据中籍贯是否一致。

=IFERROR(IF(VLOOKUP(A2,$E$2:$F$9,2,)=B2,"相同","籍贯不同"),"姓名不同")

 

图形用户界面, 应用程序, 表格, Excel
描述已自动生成

 

2)核对数字相差多少

=TEXT(VLOOKUP(A13,$E$13:$F$20,2,0)-B13,"0.0;0.0;相同")

 

表格描述已自动生成

 

10例:合并单元格查找

1)合并单元格内容作为查找结果

=VLOOKUP("",INDIRECT("a1:a"&MATCH(E2,$B$2:$B$14,0)),1)

 

 

点此了解INDIRECT函数

为何用“咗”查?点此了解Excel数据大小排序

 

2)合并单元格内容作为查找值

=VLOOKUP(VLOOKUP("",$A$1:A2,1),$E$6:$F$8,2,)

 

表格
描述已自动生成

 

11例:查找最后一次报价

低版本:

=VLOOKUP(1,IF({1,0},(MAX(IF($B$2:$B$11=E2,$A$2:$A$11))=$A$2:$A$11)*($B$2:$B$11=E2),$C$2:$C$11),2,0)

 

表格描述已自动生成

 

高版本:

=VLOOKUP(E2,SORTBY($B$2:$C$11,$A$2:$A$11,-1),2,0)

 

 

点此了解查找最新报价的其他方法

 

12例:跨表查找

可以跨单表、多表进行查找,具体见《VLOOKUP 的人生巅峰:跨多表多文件查找!》

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育Excel精品好课任你选择

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

VLOOKUP多表多文件查询

一文搞懂INDIRECT跨表查询

IF函数的{1,0}结构原理和更多用法

用超级透视表跨多表查找非常简单

版权申明:

本文作者小窝;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。