主页 > Excel专区 > Excel函数 >

excel中LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

Excel函数 2020-05-24

本文介绍Excel的万能函数LOOKUP的18种用法大全,配合VLOOKUP、INDIRECT、MATCH、IF、LEFT、RIGHT、MID等各种函数的嵌套使用,再加上函数公式逐条超细致解析,详细程度堪比教科书。

▍先了解LOOKUP的二分法查找原理,可以理解为一分为二,一直分到不能再分为止。三个案例图讲述二分法查找原理:图1、图2、图3

LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

图1


LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

图2


LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

图3

▍“二分法”的2个小规律,可以让我们快速口算出LOOKUP的结果。

① 规律1:当查找值足够大,比查找区域的数都大时,匹配的都是最后一个数。比如查找值是20,查找区域是{10,8,16,17,19},LOOKUP匹配的是最后一个数19;当查找值是100,查找区域是{20,30,50,88,66,32},匹配的是最后一个数32。

② 规律2:当查找区域是升序排列时,LOOKUP会从下往上找,第一个等于或小于查找值的数就是最终匹配的数。比如当查找值是100,查找区域是{20,30,50,80,100,100},最终匹配的是最后一个数100;当查找值是100,查找区域是{20,30,50,98,99,101},最终匹配的数是99。

▶所以下面的案例会用到查找值“座”和“9E+307”来匹配最后一个文本和数字。“座”这个字代表超级大的文本,找最后一个文本就用“座”;“9E+307”这个数字代表超级大的数字,找最后一个数字就用“9E+307”。

▍LOOKUP函数和VLOOKUP函数不一样,它不用区分逆向还是正向查询。

说了这么多,LOOKUP的二分法查找可以完成哪些功能?

▼一、利用模糊查找对多级区间快速判定结果,教 IF函数和VLOOKUP函数怎么低调做函数。

公式模板:=LOOKUP(查找值,1行或1列的查找区域,1行或1列的结果区域)。查找区域要升序排列。

◆如图4:求销售额的提成区间,对员工评级,LOOKUP信手捏来。

LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

图4:销售区间员工提成判定,等级评级

如果没有右边的比例表格,公式可以直接写成数组形式,比如求提成比例:D2单元格=LOOKUP(C3,{0;2000;5000;8000;10000},{0.01;0.03;0.05;0.1;0.12}),单元格格式设置成百分比格式就可以了。

◆如图5:对这些员工的身高进行判定,得出他们适合穿什么尺码的衣服,170到174归到170这一档,175到179归到175这一档,依次类推。在D3单元格里输入公式=LOOKUP(C3,$G$3:$M$3,$G$2:$M$2) 。

LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

图5:LOOKUP多区间判定

PK环节:

如果用VLOOKUP函数来完成,只能用VLOOKUP函数的模糊查找功能,而且右边的尺码表还得转置,而且查找区域也要升序排列,这里VLOOKUP完败。


如果是用IF函数来完成,就得输入这么长的公式,7个IF函数嵌套,=IF(C3<165,"XS",IF(C3<170,"S",IF(C3<175,"M",IF(C3<180,"L",IF(C3<185,"XL",IF(C3<190,"2XL",IF(C3>=190,"3XL",""))))))) 。IF函数完败。

▼二 、单条件精确查找

公式模板:=LOOKUP(1,0 / (查找值=1行或1列查找区域),1行或1列结果区域) 。

如图7:通过人名求部门和工号,G2单元格输入公式=LOOKUP(1,0/($B$2:$B$5=$F2),$A$2:$A$5) 。其中 0/($B$2:$B$5=$F2)为查找区域,$A$2:$A$5为结果区域。公式详解看下图

LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

图7:单条件精确查找

▼三 、多条件精确查找

公式模板:=LOOKUP(1,0 / ((查找值1=查找区域1)*(查找值2=查找区域2)*……),1行或1列结果区域) 。

如图8:求薪资,但是有同名的人,所以“部门*人名”就变成唯一性了。H2单元格输入公式 =LOOKUP(1,0/(($F2=$B$2:$B$5)*($G2=$A$2:$A$5)),$D$2:$D$5) 。

其中 ($F2=$B$2:$B$5)*($G2=$A$2:$A$5)乘号*相当于AND函数,要两条件同时满足。$D$2:$D$5为结果区域。公式详解看下图

LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

图8:多条件精确查找

▼四 、制作查询表,可以分类别查找最后一条记录

公式模板:=LOOKUP(1,0 / (查找值=1行或1列查找区域),1行或1列结果区域) 。

如图9:可以在E3单元格输入不同的“产品名称”,就会自动出现对应的“最后出库时间和出库人”。

LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

图9:按要求查最后一条记录

在日期列F2单元格输入公式=LOOKUP(1,0/($E3=$B:$B),$A:$A),公式解析:E3人名在B列出现一次就会显示一个TRUE,不出现就是FALSE;最后查找区域就只剩{0;0;0……},查找值1在一堆的0里找最终结果,因为一堆0可以理解为升序状态,从下往上找第一个小于或等于自身查找值的数就是要的值,所以1模糊匹配最后一个0,最后一个0对应的结果日期就是要的值。

在出库人列G3单元格输入公式=LOOKUP(1,0/($E3=$B:$B),$C:$C),也是同理。公式可以下拉,同时查多个产品的最后出库时间。

▼五 、合并单元格不用取消,正向查找引用,VLOOKUP配合LOOKUP就可做到

公式模板:=VLOOKUP(LOOKUP("座",1列或1行查找区域),多行多列查找区域,结果在第几列,精确查找0) 。

如图10:A列的合并单元格不取消,照样可以引用A10:B13的单价,C3单元格输入公式=VLOOKUP(LOOKUP("座",$A$3:A3),$A$11:$B$13,2,0),再鼠标下拉单元格。公式详解看下图

LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

图10:合并单元格不用取消,照样用公式引用成功

▼六 、合并单元格不用取消,逆向查找引用

公式模板:=LOOKUP("座",INDIRECT("$列或行$数:$列或行"&MATCH(查找值,$列或行$1:$列或行数,0))) 。

如图11:A列合并单元格不用取消,在B3单元格输入公式=LOOKUP("座",INDIRECT("$A$8:$A"&MATCH($A3,$B$1:$B$13,0))),引用成功。公式详解看下图

LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

图11:用LOOKUP和INDIRECT、MATCH函数组合

▼七 、拆分合并单元格并自动填充内容

① 拆分行方向合并单元格并自动填充内容

公式模板:=LOOKUP("座",查找区域) 。

如图12:第3行有合并单元格,是“一月”和“二月”。现在想插入一行,快速将第三行的内容复制下来,同时拆分合并单元格并自动填充原来合并单元格的内容。在A4单元格输入公式=LOOKUP("座",$A$3:A$3)。

LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

图12:LOOKUP行方向自动拆分单元格并复制内容

② 拆分列方向合并单元格并自动填充内容

公式模板:=LOOKUP("座",查找区域) 。

如图13:A列有合并单元格,是“地方名”和“合计”。现在想插入一列,快速将A列的内容复制下来,同时拆分合并单元格并自动填充原来合并单元格的内容。在A4单元格输入公式=LOOKUP("座",$B$4:$B4)。

LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

图13:LOOKUP列方向自动拆分单元格并复制内容

▼八 、通过全称查简称

公式模板:=LOOKUP(9E+307,FIND(简称的查找值,全称的查找区域),结果区域)。

FIND函数的第一参数必须是简称内容,第二参数必须是全称内容。不然会错误。

如图14:在B10单元格输入公式:=LOOKUP(9E+307,FIND(A10,$A$2:$A$6),$B$2:$B$6)。公式详解看下图

LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

图14:通过全称查简称

▼九 、通过简称查全称

公式模板:=LOOKUP(9E+307,FIND(简称的查找区域,全称的查找值),结果区域)。

FIND函数的第一参数必须是简称内容,第二参数必须是全称内容。不然会错误。

如图15:在B3单元格输入公式=LOOKUP(9E+307,FIND($A$10:$A$14,A3),$B$10:$B$14)。公式详解看下图

LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

图15:通过简称查全称

▼十 、查找引用一行或一列的最后一个数字、最后一个文本、最后一个非空内容

公式模板:公式中的$A$1:$F$1可以换成任何需要的区域 。如图16

LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

图16:查最后一个数字、文本、非空内容

▼十一 、提取左边数字

公式模板:= -LOOKUP(1,-LEFT(查找值,ROW($1:$足够大的数字))),最后一定要按CTRL+Shift+回车,三键,不然会出错。公式详解看下图17:

LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

图17:提取左边数字

▼十二 、提取右边数字

公式模板:= -LOOKUP(1,-RIGHT(查找值,ROW($1:$足够大的数字))),最后一定要按CTRL+Shift+回车,三键,不然会出错。公式详解看下图18:

LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

图18:提取右边数字

▼十三 、提取中间数字

公式模板:= -LOOKUP(1,-MIDB(查找值,SEARCHB("?",查找值),ROW($1:$足够大的数字))),最后一定要按CTRL+Shift+回车,三键,不然会出错。公式详解看下图19:

LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

图20:提取中间数字

▼十四 、提取任意位置的数字

公式模板:= -LOOKUP(1,-MID(查找值,MIN(FIND(ROW($1:$10)-1,查找值&1/17)),ROW($1:$足够大的数字))),最后一定要按CTRL+Shift+回车,三键,不然会出错。公式详解看下图20:

LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

图20:提取任意位置的数字

▼十五 、提取排名前几的人员信息

假如要提取排名前3的人员信息,公式模板:=IF(ROW($A1)>3,"",LOOKUP(1,0/(LARGE($B$3:$B$8,ROW($A1))=$B$3:$B$8),A$3:A$8))。$B$3:$B$8是查找区域,A$3:A$8是结果区域,这两个区域以实际要求的内容来定。如图21:详见公式解析图

LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏

图21:提取排名前3的人员信息

函数LARGE(数据区域,第几大值),比如第1大值,返回“数据区域中最大的数值”;比如第3大值,返回“数据区域中第3大的数值”。ROW($A1)下拉单元格会变成ROW($A2)、ROW($A3)。


标签: vlookup函数LOOKUP函数

Office教程网 Copyright © 2016-2020 www.office26.com. Some Rights Reserved. 备案号:滇ICP备16002327号