VLOOKUP经典用法12例
作者:小窝来源:部落窝教育发布时间:2023-09-27 17:11:58点击:1117
VLOOKUP是几乎所有Excel用户都会的一个函数,本教程总结了它的12例经典用法,看看你会多少。
凡是求职简历中写Excel技能的,80%的人都写了熟练操作VLOOKUP函数。VLOOKUP函数似乎成了求职的一个敲门砖或者试金石了。
但是你真的熟练使用VLOOKUP吗?看看下面的用法你知道有多少。
第1例:查找同一产品的多个列值
1)各值顺序与查找区域保持一致,搭配COLUMN
譬如下方,不需要笨些些地分别去修改第3参数获取各值,搭配COLUMN函数自动搞定。
低版本
=VLOOKUP($A21,$C$2:$G$17,COLUMN(B1),0),右拉填充
高版本(2021及365版本,下同)
=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,"相同","籍贯不同"),"姓名不同")
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)
为何用“咗”查?点此了解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:
相关推荐:
版权申明:
本文作者小窝;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。