说起查找引用类函数,很多小伙伴们会先想到VLOOKUP函数,但在实际应用中,很多时候VLOOKUP却是力不从心:比如说从指定位置查找、多条件查找、逆向查找等等。这些VLOOKUP函数实现起来颇有难度的功能,有一个函数却可以轻易实现,这就是下面咱们要说的主角——LOOKUP。这个函数主要用于在查找范围中查询指定的查找值,并返回另一个范围中对应位置的值。该函数支持忽略空值、逻辑值和错误值来进行数据查询,几乎可以完成VLOOKUP函数和HLOOKUP函数的所有查找任务,接下来咱们就一起看看LOOKUP函数的常用套路。 如下图所示,B列姓名使用了合并单元格,使用以下公式可以得到完整的填充:=LOOKUP(1,0/(A:A<>''),A:A)先使用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(1,0/(条件区域=条件),查询区域)如下图,要根据F3单元格的商品名称和G3单元格的部门,查询对应的销售经理。公式为:=LOOKUP(1,0/((D2:D10=F3)*(B2:B10=G3)),A2:A10)=LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),查询区域)如下图,要根据B列销售业绩返回对应的评定标准,E~F列为标准对照表。这种方法可以取代IF函数完成多个区间的判断查询,前提是对照表的首列必须是升序处理。=-LOOKUP(1,-RIGHT(B2,ROW($1:$9)))本例中,数值都位于右侧,因此先用RIGHT函数从B2单元格右起第一个字符开始,依次提取长度为1至99的字符串。 添加负号后,数值转换为负数,含有文本字符的字符串则变成错误值。 LOOKUP函数使用1作为查询值,在由负数、0和错误值构成的数组中,忽略错误值提取最后一个等于或小于1的数值。最后再使用负号,将提取出的负数转为正数。=LOOKUP('做',INDIRECT('A1:A'&MATCH(D2,B1:B10,0)))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('做',引用区域)返回该区域中最后一个文本的内容。 返回A1:A7单元格区域中最后一个文本,也就是江北公司,得到“苏明哲”所在的部门。好了,咱们今天的内容就是这些吧,祝小伙伴们一天好心情~
|