这么多的用法,你经常碰到哪几种?据说那个查找日期的很少有人注意: 一、基本用法 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;;') |
|