首页 excel笔记函数技巧正文

lookup函数的使用技巧及实例演示

九天 函数技巧 2020-02-19 432 0 lookup

函数主要用于在查找范围中查询指定的查找值,并返回另一个范围中对应位置的值。

语法:lookup(查找的值,查找的范围,返回值的范围),lookup函数在excel中是一种非常强大和灵活的运算函数,计算返回向量或数组中的数值,要求数值必须按升序排序,否则结果错误,该函数支持忽略空值、逻辑值和错误值来进行数据查询,几乎可以完成vlookup函数和hlookup函数的所有查找任务,日记坊将带给你lookup函数的使用技巧及精彩实例;

一、返回B列最后一个文本:

公式:=LOOKUP("々",B:B)或是=LOOKUP("做",B:B)

lookup函数1.jpg

二、返回B列最后一个数值

公式:=LOOKUP(9E+307,B:B)

lookup函数2.png

三、填充合并单元格

如下图所示,B列姓名使用了合并单元格,使用以下公式可以得到完整的填充:=LOOKUP("做",B$2:B2)

lookup函数3.gif

四、返回A列最后一个非空单元格内容

公式=LOOKUP(1,0/(A:A<>""),A:A)

lookup函数4.png

公式说明:

先使用A:A<>""判断A列是否不等于空单元格,得到一组有逻辑值TRUE和FALSE构成的内存数组。

然后用0除以这些逻辑值,在四则运算中,逻辑值TRUE相当于1,FALSE相当于0,相除之后,得到由错误值和0构成的新内存数组。其中的0,就是0/TRUE的结果,表示符合条件。

最后用1作为查找值,在这个内存数组中找到0的位置,并返回第三参数中对应位置的内容。如果有多个符合条件的记录,LOOKUP默认以最后一个进行匹配。

五、逆向查询

如下图,要根据E3单元格的商品名称,查询对应的销售经理。公式为:=LOOKUP(1,0/(C2:C10=E3),A2:A10)

lookup函数5.jpg

单条件查询的模式化写法为:=LOOKUP(1,0/(条件区域=条件),查询区域)

六、多条件查询

如下图,要根据F3单元格的商品名称和G3单元格的部门,查询对应的销售经理。公式为:=LOOKUP(1,0/((D2:D10=F3)*(B2:B10=G3)),A2:A10)

lookup函数6.jpg

多条件查询的模式化写法为:=LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),查询区域)

七、模糊查询等级

如下图,要根据B列销售业绩返回对应的评定标准,E~F列为标准对照表。

C2单元格公式为:=LOOKUP(B2,$E$3:$F$6)

lookup函数7.gif

这种方法可以取代if函数完成多个区间的判断查询,前提是对照表的首列必须是升序处理。

八、提取有规律的数字

如下图,要提取出B列混合内容中的数值。公式为:=-LOOKUP(1,-right(B2,row($1:$9)))

lookup函数8.gif

公式说明:

数值都位于右侧,因此先用RIGHT函数从B2单元格右起第一个字符开始,依次提取长度为1至99的字符串。

添加负号后,数值转换为负数,含有文本字符的字符串则变成错误值。

LOOKUP函数使用1作为查询值,在由负数、0和错误值构成的数组中,忽略错误值提取最后一个等于或小于1的数值。最后再使用负号,将提取出的负数转为正数。

九、带合并单元格的查询

如下图,根据D2单元格的姓名查询A列对应的部门。

公式为:=LOOKUP("做",indirect("A1:A"&match(D2,B1:B10,0)))

lookup函数9.jpg

公式说明:

MATCH(D2,B1:B10,0)部分,精确查找D2单元格的姓名在B列中的位置。返回结果为7。用字符串"A1:A"连接MATCH函数的计算结果7,变成新字符串"A1:A7"。

接下来,用InDIRECT函数返回文本字符串"A1:A7"的引用。如果MATCH函数的计算结果是5,这里就变成"A1:A5"。同理,如果MATCH函数的计算结果是10,这里就变成"A1:A10"。也就是这个引用区域会根据D2姓名在B列中的位置动态调整。

最后用=LOOKUP("做",引用区域)返回该区域中最后一个文本的内容。简化后的公式相当于:=LOOKUP("做",A1:A7)返回A1:A7单元格区域中最后一个文本,也就是江北公司,得到“苏明哲”所在的部门。


相关阅读:

经典LOOKUP函数的九种运用方法

巧妙使用vlookup+if进行从右向左反向引用

excel查找引用真的不难,1分钟教会你上、下、左、右、多条件查找

这些特殊字符是什么意思?"々"、"龠"、"座"、"做"、"吖"、"龥"、"咗"、"9E+307"、"-9E+307"

掌握这五类函数,别人加班,你逛街!

掌握这五种match函数用法,基本告别加班!

致爱学习的你:使用excel解锁高效学习的正确方法!

打赏
  • 文章发表:九天
  • 本文地址:https://rijifang.com/index.php/post/156.html
  • 声       明:转载请注明出处和附带本文链接!文章部份资料来自于网络,版权归原作者,尊重原创,注重分享;如涉版权问题,请联系本站删除!