《Excel解析lookup的经典查找方式》由会员分享,可在线阅读,更多相关《Excel解析lookup的经典查找方式(8页珍藏版)》请在金锄头文库上搜索。
1、第一, lookup 函数用法介绍;第二, 第二,通过实例讲解 lookup 函数经典的条件查找解法,通用公式基本可以写为:LOOKUP(2,1/(条件),查找数组或区域)LOOKUP(1,0/(条件),查找数组或区域)。第一部分:lookup 函数用法介绍lookup 函数和 vlookup 函数是 excel 中最常用的两个查找函数。vlookup 函数能做到的 lookup 函数同样可以做到,而且可以做得更好。LOOKUP 函数有两种语法形式:向量和数组。本期就向量形式的展开交流和探讨。向量形式的语法为:LOOKUP(lookup_value,lookup_vector,result_v
2、ector)其中的参数意义如下:Lookup_value:为所要查找的数值。Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。Lookup_vector:为只包含一行或一列的区域。Lookup_vector 的数值可以为文本、数字或逻辑值。Lookup_vector 的数值必须按升序排序:.、-2、-1、0 、1 、2 、. 、A-Z、FALSE 、TRUE,否则, LOOKUP 不能返回正确的结果。文本不区分大小写。Result_vector:只包含一行或一列的区域,其大小必须与 lookup_vector 相同。比如 lookup(A1,B1:B10,C2:C11)
3、,其中 C2:C11 的尺寸要与 B1:B10 相同,且如果A1 对应 B 列中的位置是 B2 的话,那么返回的将是 C3 的值。LOOKUP 函数说明:第一,如果函数 LOOKUP 找不到 lookup_value,则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。这就是为何返回最后一个满足条件的值的原理。第二,如果 lookup_value 小于 lookup_vector 中的最小值,函数 LOOKUP 返回错误值 #N/A。利用这个特性,我们可以用=LOOKUP(1,0/(条件),引用区域)这样一个通用公式来作查找引用。第二部分:lookup 函数
4、实例运用运用一:模糊查找模糊查找的核心是第二个参数排序必须是升序,否则会导致查找值错误。下图所示的表 1 是按升序排序的,表 2 没有排序。分别在表 1 和表 2 下面对应的单元格输入公式。表 1 的数据源是按升序排序的,根据 lookup 函数用法:=LOOKUP( 要查找的数据,查找范围,结果),在 C24 单元格设置公式:=LOOKUP(B24,$B$5:$B$17,$C$5:$C$17),然后下拉得到正确结果。表 2 的数据源是没有排序的,在 J24 单元格输入公式:=LOOKUP(I24,$I$5:$I$17,$J$5:$J$17) ,然后下拉,发现 J25 单元格得到的结果是H12
5、6,显然不对。通过表 2 的源数据可以看到 I25 单元格对应的值应该为 J8 单元格的值 H142。为什么会出错呢?这就印证了第一部分的用法介绍中所讲到的:Lookup_vector 的数值必须按升序排序:.、-2、-1、0、1、2、.、A-Z、FALSE、TRUE,否则, LOOKUP 不能返回正确的结果。文本不区分大小写。模糊查找,数据源一定要以升序先进行排序,否则就会出错。在数据源没有排序的情况下,如何才能查找到正确结果?LOOKUP 函数有一个经典的条件查找解法,可以很好的解决此问题。在第一部分有提到,通用公式基本可以写为:LOOKUP(2,1/(条件),查找数组或区域) 或 LOO
6、KUP(1,0/(条件),查找数组或区域) 。公式中的 2、1 、 0 等数字的含义是什么? 首先,条件是一组逻辑判断的值或逻辑运算得到的由 TRUE 和 FALSE 组成或者 0 与非 0 组成的数组,因而:0/(条件)的作用是用于构建一个由 0 或者 #DIV!0 错误组成的值。比如数据源中能查找到对应值就是ture,没有就是 false。形式如:0/True=0,0/false=#DIV0!,查找到就 0,没有就是错误值。如果 LOOKUP 函数找不到 lookup_value (即:1),则它与 lookup_vector 中小于或等于 lookup_value 的最大值(即:0)匹配
7、。也就是说,要在一个由 0 和#DIV!0 组成的数组中查找 1,肯定找不到 1,因而将返回小于或等于 1 的最大值 (也就是 0)匹配。用大于 0 的数来查找 0,肯定能查到最后一个满足条件的。以上的原理,被俗称为“以大欺小法”。这种技巧在 LOOKUP 函数上的运用是很常见的。利用上面的原理,不管有没有排序,只要使用上面的“以大欺小法” 都能得到正确结果。比如上面实例中,在 J25 单元格输入公式: =LOOKUP(1,0/(I25=$I$5:$I$17),$J$5:$J$17),就可以了。运用二:精确查找第一,查找的数据没有对应值,可以利用 ISNA(ISERROR)函数屏蔽错误值。如上
8、图所示,表 3 是数据源,在下面左边根据“番号”查找“ 俗称”。单击 C51 单元格,输入公式=LOOKUP(1,0/(B51=$B$42:$B$45),$C$42:$C$45),然后下拉可以看到下面的 C52 和 C54 单元格出现错误值。这种情况可以利用 ISNA(ISERROR)函数屏蔽错误值。只要在公式外面嵌套个 if(isna(lookup(),lookup(),这样的形式就可以把错误值屏蔽。在 H51 单元格,输入这样的公式:=IF(ISNA(LOOKUP(1,0/(G51=$B$42:$B$45),$C$42:$C$45),LOOKUP(1,0/(G51=$B$42:$B$45)
9、,$C$42:$C$45),下拉,就可以屏蔽错误值了。将错误值屏蔽了,表格就好看多了。上面公式中,是显示空的意思,错误就显示空,没有就查找。第二,借助错误值来判定产品是否存在。下图所示根据左边的数据源,来判定右边对应的数据是否在番号列中。只需要嵌套一个 isna 函数就可以做到,如果没有存在就错误,有存在就 .这样的形式。在 H62 单元格输入公式:=IF(ISNA(LOOKUP(1,0/(G62=$B$62:$B$74), 否,是),下拉就即可得出结果。“图啥”网友问:iserror 与 isna 函数的区别。ISNA 只屏蔽#N/A 错误,ISERROR屏蔽所有错误。第三,LOOKUP 函
10、数多条件查找。如上图所示,根据“俗称”和“ 订单号”来查找“订单数”和“ 尾数”,可以套用这样的公式:=LOOKUP(1,0/(条件(1)*(2)*(3).。),引用区域),用*或&将各个条件连接起来,* 就是和的意思。此题有两种方法:第一,在 K112 单元格输入公式:=LOOKUP(1,0/($I112=$B$112:$B$120)*($J112=$C$112:$C$120),D$112:D$120),复制公式就可以得到结果。第二,另外也可以使用这个公式:=LOOKUP(1,0/($I112&$J112=$B$112:$B$120&$C$112:$C$120),D$112:D$120)第四
11、,含某个字符查找。按照上图所示,根据左边的数据源,来对含有某个字符进行查找。单击 G128 单元格,输入公式:=LOOKUP(1,0/(FIND($F128,$B$128:$B$131),B$128:B$131),就可以得到结果。VLOOKUP 函数与 lookup 函数对比:第一,在多条件查找方面,就能看出 lookup 函数好用。用 vlookup 多条件查找,最简单的方法就是借用辅助列。第二, VLOOKUP 函数对于反向查找是需要嵌套其余函数才能实现,而LOOKUP 函数没有正反之分,因此在这方面 LOOKUP 函数会更加容易实现。第三, vlookup 在查找字符方面,可以使用*号类通配符。LOOKUP 是不支持通配符的,但可以使用 FIND (查找字符,数据源区域) 的形式代替。