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

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

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

图1

图2

图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信手捏来。

图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) 。

图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

▼二 、单条件精确查找

公式模板：=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为结果区域。公式详解看下图

图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为结果区域。公式详解看下图

图8：多条件精确查找

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

公式模板：=LOOKUP（1，0 / (查找值=1行或1列查找区域)，1行或1列结果区域） 。

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

图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)，再鼠标下拉单元格。公式详解看下图

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

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

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

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

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

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

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

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

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

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

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

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

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

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

▼八 、通过全称查简称

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

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

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

图14：通过全称查简称

▼九 、通过简称查全称

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

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

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

图15：通过简称查全称

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

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

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

▼十一 、提取左边数字

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

图17：提取左边数字

▼十二 、提取右边数字

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

图18：提取右边数字

▼十三 、提取中间数字

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

图20：提取中间数字

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

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

图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：详见公式解析图

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

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

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

• 本地下载