分享

工作必备vlookup函数3大用法,让你效率再翻倍!

 北洋智能 2018-03-01

这么多的用法,你经常碰到哪几种?据说那个查找日期的很少有人注意:

一、基本用法

1、根据货号精确查找信息

=VLOOKUP($C45,$C$1:$G$8,COLUMN(B1),0)

注:需要注意的是查找值的引用,Colum(b1)的结果是2,当公式向右复制时可以生成3,4,5,..

2、模糊查找

模糊查找产品名称数量

=VLOOKUP('*'&E2&'*',$A$2:$C$10,3,)

注:*是通配符,代表任意多个字符。可参考VLOOKUP常见错误解决办法

3、逆向查找

=VLOOKUP(A11,IF(,B2:B7,A2:A7),2,0)

注:个人觉得使用Index+Match函数组合更方便

=INDEX(A2:A7,MATCH(A11,B2:B7,0))

二、进阶用法

1、多条件查询

多对一查找

=VLOOKUP(E2&F2,IF(,A2:A11&B2:B11,C2:C11),2,0)

注:数组公式输完后需要按CTRL+SHIFT+ENTER三键结束。

一对多查询

先创建一个辅助列,在B2单元格中输入公式=C2&COUNTIF($C$2:C2,C2),统计该区域要查找值个数并做标记

在G2单元格中输入公式

=IFERROR(VLOOKUP($G$1&ROW(1:1),$B$2:$E$14,COLUMN(B:B),0),''),回车

2、区间模糊查找

查找税率

在A11中输入公式=VLOOKUP(SUBSTITUTE(A11,'~','~~'),A2:D8,3,0)

注:~用于查找通配符,如果在vlookup公式中出现,会被认为特定用途,非真正的~

计算个人所得税先创建辅助列,在G2中输入公式

=(F2-3500)*VLOOKUP(F2-3500,$B$2:$D$8,2)-VLOOKUP(F2-3500,$B$2:$D$8,3)

3、求和

在D57中输入公式

=SUM(VLOOKUP(T(IF(,$B$53:$B$55)),$F$53:$G$55,2,0)*D53:D55)

注:数组公式输完后需要按CTRL+SHIFT+ENTER三键结束。

4、盘点对账(带通配符)

在D15中输入公式

=TEXT(VLOOKUP(SUBSTITUTE(A15,'*','~*'),$E$15:$F$17,2,)-C15,'少0;多0;')

注:TEXT可以设置数字显示的格式

5、查找符合条件的值

在F10中输入公式

=VLOOKUP(1=1,IF(,$B$3:$B$7>500,$B$3:$B$7),2,0)

在F3中输入公式

=VLOOKUP(MAX(($A$3:$A$7=$E$3)*ROW(1:5)),IF(,ROW(1:5),$B$3:$B$7),2,)

输完以上公式按CTRL+SHIFT+ENTER三键结束。

三、特殊用法

1、提取电话号码公式=VLOOKUP(,MID(A2,ROW($1:$14),11)*,2,)

提取 / 间隔的最后一段内容

=VLOOKUP('/*',RIGHT(A8,ROW($1:$12)-),2,)

提取金额

=VLOOKUP(9^9,MID(A14,MIN(IF(ISNUMBER(-MID(A14,ROW($1:$14),1)),ROW($1:$14))),ROW($1:$14))*1,1)

输完以上公式按CTRL+SHIFT+ENTER三键结束。

2、排列成相同的顺序

输入公式= IFERROR(VLOOKUP($B2,$B$11:$D$13,COLUMN(A1),0),'')

3、制作工资条

输入公式=VLOOKUP($A10,$A$3:$M$6,COLUMN(B1),0),向右填充。对于双行表头制作工资条,拿走,不谢!

4、日期查找

输入公式

=TEXT(IFERROR(VLOOKUP($F30,$A$30:$D$35,4,0),0),'yyyy-m-d;;')

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多