1、解析 lookup 的经典查找方式包括两部分内容：第一，lookup 函数用法介绍;第二，通过实例讲解 lookup 函数经典的条件查找解法，通用公式基本可以写为：LOOKUP(2,1/(条件),查找数组或区域)或LOOKUP(1,0/(条件),查找数组或区域)。主讲老师：卢子老师讲座主题：解析 lookup 函数的经典查找方式学习是需要技巧和经验的。感谢卢子老师为大家分享和交流他的实战经验。下面是本期讲座的全部内容。本期讲座包括两部分内容：第一，lookup 函数用法介绍；第二，通过实例讲解 lookup函数经典的条件查找解法，通用公式基本可以写为：LOOKUP(2,1/(条件),查找数组或
2、区域)或 LOOKUP(1,0/(条件),查找数组或区域)。第一部分：lookup 函数用法介绍lookup 函数和 vlookup 函数是 excel 中最常用的两个查找函数。vlookup 函数能做到的 lookup 函数同样可以做到，而且可以做得更好。LOOKUP 函数有两种语法形式：向量和数组。本期就向量形式的展开交流和探讨。向量形式的语法为：LOOKUP(lookup_value,lookup_vector,result_vector)其中的参数意义如下：Lookup_value：为所要查找的数值。Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。Lookup
3、_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),其中 C2:C11 的尺寸要与 B1:B10 相同，且如果 A1 对应 B 列中的位置是 B2 的话，那么返回的将是 C3 的值。LOOKUP 函数说明：第一，如果函数
4、LOOKUP 找不到 lookup_value，则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。这就是为何返回最后一个满足条件的值的原理。第二，如果 lookup_value 小于 lookup_vector 中的最小值，函数 LOOKUP 返回错误值 #N/A。利用这个特性，我们可以用=LOOKUP(1,0/(条件),引用区域)这样一个通用公式来作查找引用。第二部分：lookup 函数实例运用运用一：模糊查找模糊查找的核心是第二个参数排序必须是升序，否则会导致查找值错误。下图所示的表 1 是按升序排序的，表 2 没有排序。分别在表 1 和表 2 下面对
5、应的单元格输入公式。表 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 单元格得到的结果是H126，显然不对。通过表 2 的源数据可以看到 I25 单元格对应的值应该为 J8 单元格的值H142。为什么会出错呢?这就印证了第一部分的用法介绍中所讲到的：Lookup_v
6、ector 的数值必须按升序排序：.、-2、-1、0、1、2、.、A-Z、FALSE、TRUE,否则， LOOKUP 不能返回正确的结果。文本不区分大小写。模糊查找，数据源一定要以升序先进行排序，否则就会出错。在数据源没有排序的情况下，如何才能查找到正确结果?LOOKUP 函数有一个经典的条件查找解法，可以很好的解决此问题。在第一部分有提到，通用公式基本可以写为：LOOKUP(2,1/(条件),查找数组或区域) 或 LOOKUP(1,0/(条件),查找数组或区域)。公式中的 2、1、0 等数字的含义是什么? 首先，条件是一组逻辑判断的值或逻辑运算得到的由 TRUE 和 FALSE 组成或者 0
7、 与非 0 组成的数组，因而：0/(条件)的作用是用于构建一个由 0 或者#DIV!0 错误组成的值。比如数据源中能查找到对应值就是 ture，没有就是false。形式如：0/True=0，0/false=#DIV0!，查找到就 0，没有就是错误值。如果 LOOKUP 函数找不到 lookup_value (即：1)，则它与 lookup_vector 中小于或等于 lookup_value 的最大值(即：0)匹配。也就是说，要在一个由 0 和#DIV!0 组成的数组中查找 1，肯定找不到 1，因而将返回小于或等于 1 的最大值(也就是 0)匹配。用大于 0 的数来查找 0，肯定能查到最后一个
8、满足条件的。以上的原理，被俗称为“以大欺小法”。这种技巧在 LOOKUP 函数上的运用是很常见的。利用上面的原理，不管有没有排序，只要使用上面的“以大欺小法”都能得到正确结果。比如上面实例中，在 J25 单元格输入公式：=LOOKUP(1,0/(I25=$I$5:$I$17),$J$5:$J$17)，就可以了。运用二：精确查找第一，查找的数据没有对应值，可以利用 ISNA(ISERROR)函数屏蔽错误值。如上图所示，表 3 是数据源，在下面左边根据“番号”查找“俗称”。单击 C51 单元格，输入公式=LOOKUP(1,0/(B51=$B$42:$B$45),$C$42:$C$45)，然后下拉可
9、以看到下面的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),$C$42:$C$45)，下拉，就可以屏蔽错误值了。将错误值屏蔽了，表格就好看多了。上面公式中，“，是显示空的意思，错误就显示空，没有就查找。第二，借助错误值来判定产品是否存在。下图
10、所示根据左边的数据源，来判定右边对应的数据是否在番号列中。只需要嵌套一个 isna 函数就可以做到，如果没有存在就错误，有存在就这样的形式。在 H62 单元格输入公式：=IF(ISNA(LOOKUP(1,0/(G62=$B$62:$B$74),“否“,“是“)，下拉就即可得出结果。“图啥”网友问：iserror 与 isna 函数的区别。ISNA 只屏蔽#N/A 错误，ISERROR 屏蔽所有错误。第三，LOOKUP 函数多条件查找。如上图所示，根据“俗称”和“订单号”来查找“订单数”和“尾数”，可以套用这样的公式：=LOOKUP(1,0/(条件(1)*(2)*(3).。),引用区域)，用*或
11、&将各个条件连接起来，*就是和的意思。此题有两种方法：第一，在 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)第四，含某个字符查找。按照上图所示，根据左边的数据源，来对含有某个字符进行查找。单击 G128 单元格，输入公式：=LOOKUP(1,0/(FIND($F128,$B$128:$B$13
12、1),B$128:B$131)，就可以得到结果。VLOOKUP 函数与 lookup 函数对比：第一，在多条件查找方面，就能看出 lookup 函数好用。用 vlookup 多条件查找，最简单的方法就是借用辅助列。第二， VLOOKUP 函数对于反向查找是需要嵌套其余函数才能实现，而 LOOKUP 函数没有正反之分，因此在这方面 LOOKUP 函数会更加容易实现。第三， vlookup 在查找字符方面，可以使用*号类通配符。LOOKUP 是不支持通配符的，但可以使用 FIND (查找字符，数据源区域)的形式代替。在前面的话：不要迷恋二分法，二分法只是个传说。因对 LOOKUP 二分法的研究，可
13、能让人看起来貌似 gouweicao78 水平很高深。但本人一方面尽力让大家能看懂它，另一方面并没有提倡过大家一定要看懂二分法，因为升序查找，不需动用“二分法”，仅函数帮助也能说明白。对于这个原理的探究，仅仅是函数发烧友们的乐趣。怎样看待 LOOKUP 的“高效函数”之称第一，LOOKUP 使用二分法原理，因此具有极高效率的运算方式；但是只推荐升序查找用它，升序的时候。第二，LOOKUP(2,1/(条件),，尽管是因为“二分法”让 LOOKUP 能找到最后一个满足条件的记录，但是，“条件”，比如(A1:A10=“张三“)首先是一个数组运算，然后 1/条件又来一次数组运算，最终才用 LOOKUP
14、 二分法。这么一个“普通公式”中暗藏数组运算的东西，让“高效函数”背上了黑锅。【正文】LOOKUP 函数有一个经典的条件查找解法，通用公式基本可以写为：1.LOOKUP(2,1/(条件),查找数组或区域) 2.或 LOOKUP(1,0/(条件),查找数组或区域)很多初学者对此感觉非常诧异就，主要疑惑有：1、公式中的 2、1、0 等数字有什么含义，明明在查找条件与这 3 个数字根本毫无联系，怎么能得到正确结果？2、明明 LOOKUP 函数说明需要“升序”查找，否则可能无法返回正确的值，上面这种解法又是如何得改变这一说法呢？3、据说 LOOKUP 函数的查找顺序是“二分法”，并且有流程图可循，是否
15、可以结合此例进行讲解？【函数帮助信息摘录】语法：LOOKUP(lookup_value, lookup_vector, result_vector)1、要点 lookup_vector 中的值必须以升序排列：.,-2, -1, 0, 1, 2, ., A-Z, FALSE, TRUE。否则，LOOKUP 可能无法返回正确的值。大写文本和小写文本是等同的。2、如果 LOOKUP 函数找不到 lookup_value，则它与 lookup_vector 中小于或等于 lookup_value 的最大值匹配。3、如果 lookup_value 小于 lookup_vector 中的最小值，则 LOO
16、KUP 会返回 #N/A 错误值。【释疑】简要地说，从逻辑推理来看：1、首先，条件是一组逻辑判断的值或逻辑运算得到的由 TRUE 和 FALSE 组成或者 0 与非 0组成的数组，因而：1/(条件)的作用是用于构建一个由 1 或者#DIV!0 错误组成的值。2、根据 LOOKUP 函数说明中的这一条：如果 LOOKUP 函数找不到 lookup_value （即：2），则它与 lookup_vector 中小于或等于 lookup_value 的最大值（即：1）匹配。也就是说，要在一个由 1 和#DIV!0 组成的数组中查找 2，肯定找不到 2，因而将返回小于或等于 2 的最大值（也就是 1）匹配。为什么要用 2 来查找 1 或用 1 来查找 0 呢？因为如果有多个与第 1 参数相等的值，则Lookup 就不一定返回“最后一个”所对应的记录，所以必须养成一个良好习惯，而不要用：LOOKUP(1,1/(条件),，或 LOOKUP(,0/(条件),3、如果有多个满足条件的纪录，为何只返回最后一个，而不是第一个或其他呢？这个解释就需要二分法流程图的模拟了。而对于一般使用者来说，只需要记住“查找满足条件的最后一个记录”可以使用通用公式1.LOOKUP(2,1/(条件),查找数组或区域) 2.或 LOOKUP(1,0/(条件),查找数组或区域)
