时间：2020-05-24 00:14作者：admin来源：未知人气：2530我要评论(0)
本文介绍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<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为结果区域。公式详解看下图
图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有哪些常用的数学函数？Excel取消表格中虚线的两种方法Excel最常见的「错误值」，这些含义你都知道吗？实现快速找出Excel表格中两列数据不同内容的3种方法！如何利用Excel一键提取身份证的这些重要信息，公式直接套用！Excel如何制作动态红绿灯，工作可不要亮红灯哦Excel身份证号大探索excel如何根据日期按月汇总计算公式Excel浪漫表白公式，发给心仪的她/他Excel表格如何自动求和
热门文章
excel 两表数据快速对比，高手都是这样做，四种方法随你选
.xlsm是什么文件格式，以及xlsm文件怎么打开的方法
excel if函数如何多个条件并列
excel中计算加权平均数的公式：用SUMPRODUCT和SUM函数计算加权平均
最新文章
Excel有哪些常用的数学函数？
Excel取消表格中虚线的两种方法
Excel最常见的「错误值」，这些含义你都知道吗？实现快速找出Excel表格中两列数据不同内容的3种方法！如何利用Excel一键提取身份证的这些重要信息，公式直接套用！Excel如何制作动态红绿灯，工作可不要亮红灯哦
人气排行 excel 两表数据快速对比，高手都是这样做，四种方法随你选.xlsm是什么文件格式，以及xlsm文件怎么打开的方法excel if函数如何多个条件并列excel中计算加权平均数的公式：用SUMPRODUCT和SUM函数计算加权平均excel中IF条件函数10大用法完整版,全会是高手,配合SUMIF,VLOOKUPexcel中COUNTIFS函数9种高级用法详解，条件统计重复值，告别加班涨工如何解除Excel VBA工程密码excel 如何根据身份证号码提取户籍所在省份地区函数公式
盖楼回复X
(您的评论需要经过审核才能显示)
查看所有0条评论>>