找对比,你会首先想到Vlookup函数。但在Excel中只会Vlookup函数是远远不够的。今天小编对查找公式进行一次全面的整理。(注:一题多解的只选取最优公式) 1、普通查找 查找李晓峰的应发工资 =VLOOKUP(H2,B:F,5,0) 2、反向查找 查找吴刚的员工编号 =INDEX(A:A,MATCH(H2,B:B,0)) 3、交叉查找 查找3月办公费的金额 =VLOOKUP(H2,A:F,MATCH(I2,1:1,0),0) 4、多条件查找 查找上海产品B的销量 =LOOKUP(1,0/((A2:A7=E2)*(B2:B7=F2)),C2:C7) 5、区间查找 根据销量从右表中查找提成比率。 =LOOKUP(A2,$D$2:$E$5) 6、双区间查找 根据销量和比率完成情况,从表中查找返利。 =INDEX(B3:F7,MATCH(D11,A3:A7),MATCH(E11,B2:F2)) 7、线型插值 如下图所示,A列是数量,B列是数量对应的系数值。现要求出数字8所对应的系数值。 =TREND(OFFSET(B1,MATCH(D3,A2:A6,1),,2,1),OFFSET(A1,MATCH(D3,A2:A6,1),,2,1),D3) 8、查找最后一个符合条件记录 如下图所示,要求查找A产品的最后一次进价。 =LOOKUP(1,0/(B2:B9=A13),C2:C9) 9、模糊查找 如下图所示,要求根据提供的城市从上表中查找该市名的第2列的值。 =VLOOKUP('*'&A7&'*',A1:B4,2,0) 10、匹配查找 如下图所示,要求根据地址从上表中查找所在城市的提成。 =lookup(9^9.find(A$3:A$6,A10),B$3:B$6) 11、最后一个非空值查找 如下图所示,要求查找最后一次还款日期 =LOOKUP(1,0/(B2:B13<>''),$A2:$A13) 12、多工作表查找 【例10】从各部门中查找员工的基本工资,在哪一个表中不一定。 方法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) 13、一对多查找 【例】根据产品查找相对应的所有供应商 A2 =B2&COUNTIF(B$1:B2,B2) B11=IFERROR(VLOOKUP($A11&COLUMN(A1),$A:$C,3,0),'') 14、查找销量最大的城市 查找销量最大的城市(数组公式按ctrl+shift+enter三键输入) {=INDEX(A:A,MAX((MAX(B3:B7)=B3:B7)*ROW(B3:B7)))} 15、最接近值查找 根据D4的价格,在B列查找最接近的价格,并返回相对应的日期 (数组公式按ctrl+shift+enter三键输入) {=LOOKUP(1,0/(MIN(ABS(B3:B7-D4))=ABS(B3:B7-D4))*ROW(B3:B7),A3:A7)} 15、跨多文件查找 跨多个文件查找,估计你搜遍网络也找不到,这也是小编首次编写跨多文件查找公式。其实原理和跨多表查找一样,也是借助lookup等函数实现。 文件夹中有N个仓库产品表格,需要在“查询”文件完成查询 仓库表样式 在查询表中设置公式,根据产品名称从指定的文件中sheet1工作表查询入库单价 =VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT('['&{'仓库1';'仓库2';'仓库3'}&'.xlsx]sheet1!a:a'),A2),'['&{'仓库1';'仓库2';'仓库3'}&'.xlsx]sheet1')&'!a:b'),2,0)。 vlookup函数的多文件查找,同样可以用iferror+vlookup的模式,公式虽然长,但容易理解且公式不容易出错。另外,如果excel文件有几十个或更多,就需要使用宏表函数用Files获取所有excel文件名称,然后应用到公式中,具体的公式小编就不再写了。 小编说:能想到的查找公式差不多都列出来了,其中有不少公式需要有一定基础才能看懂。如果看不明白先学会套用以后再慢慢理解。 |
|