二维码 购物车
部落窝在线教育欢迎您!

关于LOOKUP查找出错的那些事儿

 

作者:逍遥来源:部落窝教育发布时间:2023-05-06 17:10:58点击:2983

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

有些人怕用LOOKUP函数,部分原因在于好像LOOKUP查找容易莫名其妙的出错。今天就来给大家归纳总结一下LOOKUP函数查找出错的所有原因,包括看着像升序排列但实际不是升序、没有用精确查找套路公式、漏写部分参数、用错数组、没有区分文本大小写。

 

上期内容,我们给大家讲解了LOOKUP的底层逻辑——二分法。

今天来给大家讲一讲“关于LOOKUP查找出错的那些事儿”。

 

VLOOKUP是情绪派,如果参数写错,或者查找不到,大多数时候他会直接甩脸色,来个#N/A错误,你马上就知道有错,利于甄别、修正。

LOOKUP则是心机派,就算是查不到或者查找错误,大多数时候他也不会返回错误值,而是返回具体的一个值,这就增强了迷惑性,让一些人栽了跟头吃了亏。

 

今天咱们就来釜底抽薪,归纳整理,方便大家以后能无忧使用LOOKUP

 

LOOKUP查找出错原因1:看着像升序排列实际不是升序排列

 

在上篇二分法原理的文章中,我们讲述了LOOKUP在进行常规查询时,查找区域的值必须是升序排列,否则很可能得到错误的结果,如下图所示。

 

 

正确的操作是将A列进行升序处理,此处不再赘述。

重点来了,对于A列这类字母+数字的组合,需格外注意:

你以为的升序排列和Excel以为的升序排列,是不一样的。

如下图所示,我们看工号这一列,从A1A17,字母不变,数字从小到大,看着像是升序排列,但是用LOOKUP函数来查找时,结果却出错了。

 

通常情况下,字母和数字混合的数据,Excel是根据字符逐位比较的结果来排序的。

A1A10A101A102A11A111A112A12,这才是一组升序排列的数据。

下面,我们只需要选中这一列,点击Excel里的排序,选择升序,方能得到正确的结果。

 

 

搞不太明白的同学,可以打开Excel表,生成一组数据,自己试着去排序一下看看。

 

LOOKUP查找出错原因2:精确查找时没有使用精确查找套路公式

 

下面要查人员的销售金额。数据已经按人员进行了升序排列。但很显然最后一个查找是错误的,因为名单中根本没有辜鸿渐!

 

 

为何出现这种查找错误?原因是用错了LOOKUP的查找公式。

LOOKUP函数公式常用的有两种方式。

第一种就是其默认公式,如上面那样在升序排序下进行模糊匹配查找,可以查到小于等于查找值的值,再返回对应结果——最适合用于区间查找。

第二种就是精确查找套路公式,只查找等于查找值的值,再返回对应结果,没有的则显示为#N/A错误。

 

如果上面使用精确查找套路公式,则得到正确结果。

 

 

LOOKUP查找出错原因3:漏写参数

 

1.漏掉一对括号

使用LOOKUP函数进行单条件查找时,其函数结构是:

=LOOKUP(1,0/(条件区域=条件),返回区域)

按照这个函数结构来写公式,一般都没啥问题。

但是一涉及到多条件查找,很多人就又状况百出了!

如下图所示,当我们要查找销售一部,级别为A的业绩,很多同学的公式写成了这样:

=LOOKUP(1,0/(B2:B14=F2)*(C2:C14=G2),D2:D14)

 

 

给大家解释一下现在这个公式的含义,即先用0去除以(B2:B14=F2)的值,再去乘(C2:C14=G2)的值,作为LOOKUP的查找区域。1是查找值,D2D14是返回区域。

而正确的函数公式是先计算(B2:B14=F2)(C2:C14=G2)的乘积,然后再用0来除以他们的积,作为LOOKUP的查找区域。

差别在哪,差别在于少了一组括号,千万不要小瞧这对小小的括号,有不少人在这里栽过跟头。

正确公式:=LOOKUP(1,0/((B2:B14=F2)*(C2:C14=G2)),D2:D14)

表格
描述已自动生成

 

2.第三参数不完整

 

使用LOOKUP函数的时候,要注意查找区域和返回区域应该是一一匹配的。

不能一个是一列数据,一个是一个数据,如下图所示,查找的结果即为0

 

 

正确的写法是:查找区域,A2A14,是13个单元格;返回区域是D2D14,也是13个单元格。

如果只写一个D2,那么就会默认返回区域是从D2开始横向往右数的13个单元格。

 

TIPS

在返回区域写上D2:D3也能得到正确结果。第三参数为D2:D3,也就是在告诉LOOKUP函数,查找的方向是纵向,他会自动以查找区域为参照,自动扩展到相同的返回区域,D2D14

 

 

LOOKUP查找出错原因4:数组使用错误

 

1.查找值不在数据区域的第一列或第一行

 

LOOKUP在微软官方,还介绍了一种使用方式:数组形式。

其函数结构为=Lookup(查找值,数据范围)

如下图所示,根据对照表来查找各个员工的绩效,就可以输入公式=LOOKUP(C2,$F$2:$G$5)

 

 

如果数据区域是多行多列的情况,LOOKUP需要在数据区域的第一行或者第一列查找指定的值,并返回数据区域的最后一行或最后一列的同位置的值。

比如下图所示,如果查找值不在数据区域的第一列,就会导致查找错误。

 

 

正确的公式写法是=LOOKUP(C2,$G$2:$H$5)

 

2.查找方向错误

 

如下图所示,当我们的数据区域有多列内容,用LOOKUP函数进行查找,又出错了?!

 

 

公式中的数据区域F2J5,有45列数据,列数多于行数,那么LOOKUP函数会在第一行中查找“3045”,查找到第一行的最末值10%,然后返回最末行的100%

 

TIPS

LOOKUP函数的数组形式,当数据区域是多行多列,查找方向根据行列数而定。

①如果数组区域列数多于行数,LOOKUP 会在第一行中进行横向查找,然后返回末行值。

②如果数组区域行数等于或多于列数,LOOKUP 会在第一列中进行纵向查找,然后返回末列值。

 

LOOKUP查找出错原因5:文本不分大小写

 

VLOOKUP一样,LOOKUP在进行查找的时候,文本不分大小写。

如下图所示,当我们输入公式 =LOOKUP(E2,A2:A13,B2:B13)

得到的结果就是张冠李戴,错到离谱。

 

 

咋办?请EXACT函数来帮帮忙,即可立马搞定。

F2输入公式:=LOOKUP(1,0/EXACT(E2,A2:A13),B2:B13)

 

 

EXACT函数用于比较文本是否绝对相等。

TIPS

或许有朋友看到过使用FIND函数来解决查找中的大小写区分的教程。的确可以用FIND函数区分大小写,但是在使用中需要特别谨慎——FIND检查的是否包含有查找值给出其位置数,并不检查数据是否绝对等于查找值。因此,假设上述的A列编号存在a103a1031a1032等,那么使用FIND函数得到的结果可能就是错误的。如下。

 

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

如何提取品牌信息?LOOKUP函数有绝招!

如何在交叉查询中使用VLOOKUP?看完就懂!

Excel教程:如何制作带有层次和透视感的图表?

八大查找函数公式,轻松搞定数据中的多条件查找

版权申明:

本文作者逍遥;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。