Lookup函数的使用方法（vlookup函数用法示例）

LOOKUP函数非常强大，有引用函数之王的称号。要想完全掌握它，必须了解它的5种常见用法和它的二分法查找原理。由于相关知识点比较多，所以教程将分成上下两篇。今天我们首先通过五个例子来了解这个函数的5种常见用法。明天我们再来说二分法查找原理和之前文章中遗留的两个LOOKUP问题。

一、常规引用

格式：LOOKUP（查找值，查找区域）

例1：根据姓名查找语文成绩，公式为=LOOKUP(H2,C2:D19)

例2：根据姓名查找英语成绩，公式为：=LOOKUP(H2,C2:F19)

通过这两个例子我们可以发现，LOOKUP在进行查找时公式的结构非常简单，查找值和要找的结果分别位于查找区域的首列和末列。

但是仅仅了解这个用法是远远不够的，如果我们再试一个数据的话，有可能就会发现问题：

当查找姓名变成赵永福的时候，结果就不对了，这是因为LOOKUP函数使用的是二分法查找，也就是模糊匹配，关于这一点，我们将在明天的教程《LOOKUP函数用法全解（下）——LOOKUP的二分法查找原理》中详细解释。

因此，在使用LOOKUP进行常规查找的时候，有一个非常重要的步骤，就是按照查找内容（姓名所在的c列）升序排序。

当我们排序以后，公式的结果立刻变成了正确的，是不是很神奇！

这又引出了一个新的问题，如果数据不能排序的话，LOOKUP函数还能用吗？

肯定能用啊，下面来看看LOOKUP函数的第二种用法。

二、精确查找的套路

格式：=LOOKUP（1,0/(查找范围=查找值),结果范围）

在I2单元格输入公式：

=LOOKUP（1,0/（C2:C19=H2），D2:D19）,回车，可看到正确结果。

关于这个套路的1和0/到底是什么意思，也是提问率最高的问题之一，在未讲解二分法原理之前，简单来说一下公式的意思。1就是要查找的值，但是条件变了，不是直接查找姓名，而是根据姓名得到的一组逻辑值：

注意这里只有一个TRUE，也就是我们要找的姓名。

接下来用0除以这些逻辑值，在进行计算的时候逻辑值TRUE代表1，FALSE代表0，当分母为0也就是FALSE的时候，计算结果是错误值：

因此，LOOKUP的工作就变成了在一组数据中找1。由于这组数据只有一个0，其他都是错误值，二分法使得LOOKUP只能找到不大于查找值的最后一个数字，因此只能找到0，最后根据0的行位置（第2行）得到第三参数对应位置的数据，即D2就是我们需要的结果。

这部分内容算是函数学习中比较有难度的知识点了，初学者可能理解困难，这不要紧，随着学习的深入，当对数组和逻辑值这两大要点掌握比较熟练的时候，这些内容就很好理解了。目前如果不能完全理解，记住这个套路就行：=LOOKUP（1,0/(查找范围=查找值),结果范围）。同时这个套路还能延伸出多条件精确查找用法：

=LOOKUP（1,0/((查找范围1=查找值1)* (查找范围2=查找值2)* (查找范围3=查找值3)),结果范围）

就是在每个查找范围内找到要找的值，得到的逻辑值相乘后同时符合多个条件的位置就是1，原理与单条件的一样。

三、反向查找的套路

与我们熟知的VLOOKUP不同，使用LOOKUP函数进行反向查找时非常简单，公式结构为：

=LOOKUP（查找值，查找列，结果列），下面这个例子是按照姓名排序后再反向查找的效果：

如果数据不能排序的话，使用精确查找的套路：=LOOKUP(1,0/(C2:C19=H8),B2:B19)

四、按区间查找的套路

根据学生的总分给出相应的评语。50分以下的为“很差”，50-100分的为“差”，100-150分的为“一般”，150-200分的为“较好”，200-250分的为“优秀”，250分及以上的为“能手”。

这里用的公式为：

=LOOKUP(G2,{0,50,100,150,200,250;"很差","差","一般","较好","优秀","能手"})

按照评语的要求分成了六个等级，如果用if函数去做就很啰嗦，使用LOOKUP处理这类问题非常方便，公式结构也很简单：

=LOOKUP（分数值，{下限1, 下限2……;评语1,评语2……}）

在写这个公式的时候注意两点：

1.LOOKUP的第二参数使用了常量数组，这里的大括号是手动输入的，括号内用一个分号分开，左边是每个等级的下限，例如50分以下这个表述里下限就是0，50-100的下限就是50，以此类推，每个数字之间用逗号分开；分号右边是对应的评语，评语应当使用引号，同时用逗号分开（公式里的所有符号都是英文状态下的）；

2.数字区间应当遵循升序的排列顺序，否则结果就会错误。

五、关于数据排序的重要性

当我们按照学号查找姓名的时候，发现会出现错误，学号也是按升序排列的啊，怎么会错？

这是一种最常见的错误，这里的学号升序排列只是我们感觉如此而已，实际上升序的效果是这样的：

在使用LOOKUP的时候，如果不使用精确查找的套路，切记一定要排序才能保证公式结果的正确性。

小结

1、今天一共分享了五种LOOKUP的使用套路，分别是常规查找、精确查找、多条件查找，反向查找还有按区间查找，初学者掌握这些套路学会去套用解决问题就可以了；

2、LOOKUP函数的查找原理与我们之前学过的VLOOKUP不同，VLOOKUP函数的查找方式叫做遍历法，找到满足条件的第一个值就会停止查找，而LOOKUP函数使用的是二分法原理进行查找，要找到满足条件的最后一个值才会停止查找，这一点在函数的说明文档里也提到了；

我们可以通过一个简单的测试来验证这一点：

3、对数据源按升序排列这一点很重要：

如果不能排序，那么一定要使用这个套路：=LOOKUP（1,0/(查找范围=查找值),结果范围），这也是二分法的特性决定的；

4、LOOKUP这个函数很强大，同时也很难以理解，要想彻底弄清楚这个函数，必须了解二分法原理。明天的教程我们就来聊聊什么是二分法原理，顺便再把前一段时间遗留的两个问题（LOOKUP解决四舍五入的问题和进行数据提取的问题）做个解释。

Lookup函数二分法的原理

昨天咱们学习了LOOKUP函数的各种套路，也多次提到了LOOKUP函数的查找是根据二分法来进行的，那么到底什么是二分法，今天就来聊聊这个问题。

还是用昨天的例子：按序号查找成绩，序号是升序排列的，公式=LOOKUP(J2,A2:D19)的结果正确。

   一、二分法查找原理

二分法查找是把查找范围中的数据按照个数一分为二找到位于中间位置的一个数据，中间值，然后用我们的查找值和中间值做比较。当中间值等于查找值时，直接去获取结果；当中间值小于查找值时，则向下继续进行二分法查找比较（也就是在不含中间值在内的下方的那一半数据中继续进行二分法查找）；当中间值大于查找值时，则向上继续进行二分法查找比较（也就是在不含中间值在内的上方的那一半数据中继续进行二分法查找）。如果如此二分到最后一个数据都未找到等于查找值的数据：最后一个数据小于查找值的，那就以最后一个数据的位置去获取结果值；最后一个数据大于查找值的，那就再向上找一个位置最靠近最后一个数据的小于或等于查找值的数据，再以这个数据的位置去获取结果。

只是这样解释估计很多朋友都会犯迷糊，我们结合上面的例子，具体看看如何通过序号5，找到语文成绩79的。

第一次比较：查找范围A2~A19一共18个数据，中间位置是18÷2=9，即中间值是A10单元格的9。显然查找值5小于9，所以继续向上在A2~A9中查找；

提示：如果查找范围数据个数是单数，中间位置就是（个数 1）÷2，例如11行的话，中间位置就是（11 1）÷2=6；如果数据个数是偶数，中间位置就是（个数）÷2。

第二次比较：只有8个数据，中间位置是8÷2=4，中间值是A5单元格的4，查找值5大于4，所以继续向下在A6~A9中找。注意此时下面只有四个数，第一次查找时直接将9下面的数据都排除了。

第三次比较：4个数据，中间值是A7的6，查找值5小于6，所以向上找。此时只剩下一个数据A6单元格的5，与查找值一致，因此就得到5所对应的D列数据79。

仅仅通过这样一个例子要想明白二分法是很困难的，我们再看一个例子。将上表中的数据按照成绩降序排列，还是按序号5查找语文成绩，公式不做修改。因为序号这一列的顺序乱了，不是升序排列，结果就出现了错误，实际是79，公式得到的是94，这是怎么回事呢？还是通过二分法来看看吧。

第一次查找：中间值（第9个数据）是18，查找值5小于18，因此向上在A2~A9中找；

第二次查找：上面的8个数据，中间值（第4个数据）是8，查找值5小于8，继续向上在A2~A4中找；

第三次查找：上面的3个数据，中间值是1，查找值5大于1，向下找：

第四次查找：现在只有A4单元格一个数据7，查找值5小于7，因此以7为参考，向上找一个位置最靠近7，同时数值小于5或者等于5的数据，即A3单元格的1，由此获得对应的语文值就是94。

通过这两个例子，我想大家对于二分法应该有了一定的认识，关于这个原理，在函数说明里只有一句话的介绍：

在实际应用中，我们可以不用去纠结二分法到底是怎么回事，中间位置是什么，该往下还是往上找，这都是函数的工作，我们只需要记住一点：数据一定要升序排列，如果不能升序排列，那么就按照LOOKUP的精确查找套路去设计公式。

   二、LOOKUP实现数据的四舍五入

二分法原理就介绍这么多，接下来需要解决之前遗留的两个问题。

在5月12日的文章中，我们用LOOKUP解决了一个四舍五入的问题，结果大家纷纷留言要个解释：

那么引起大家热议的这个公式到底是什么呢？看下图：

原来这个公式是用LOOKUP函数将一个数字百位以下全部舍去，实现了百分位取整。

在了解过二分法原理以后，是时候让LOOKUP还大家一个解释了。首先说明ROW（A:A）*100这部分。它其实就是得到了一组数字。为了让大家看明白，把A:A范围给小一点，我们用=ROW(A1:A31)*100作说明：

虽然单元格中只能看到一个100，实际上是31个数字，我们可以用f9功能键来看看具体内容：

ROW函数用于获取单元格的行号，ROW(A1:A31)*100就是用A1到A31单元格的行号分别乘以100，得到一组百位取整的数据{100;200;300;……3000;3100}。

然后LOOKUP上场了。它要在上述得到的已经百位取整的数组中查找A1。因为数组是按升序排列的，所以查找A1的实质就是在数组中找小于等于A1的最大值。

以2517.32为例，唯有2500是小于它的最大值，因此结果就是2500。有兴趣的朋友可以自己用二分法原理去试试，看看对不对。

其他数字的查找方式都是如此。这个公式之所以巧妙，就是把一个四舍五入的问题变成了查找引用的问题，真是妙！

   三、LOOKUP进行数据提取

在5月31日的文章中，我们又利用LOOKUP进行数据的提取，因此有了一个5000字的约会：

这次又是什么问题呢？原题如图所示提取学号：

咱们用上图的第一个数据来解释。在N1单元格中输入“10张勇a”，然后在O1单元格中输入公式=-LOOKUP(1,-LEFT(N1,ROW(1:9)))提取学号。

LOOKUP不是引用函数吗，怎么又可以提取数字了？

LEFT函数的第二个参数使用了一个数组，ROW(1:9)相当于{1;2;3;4;5;6;7;8;9}。LEFT是把第一参数指定的数据从左边开始提取，提取的长度由第二参数来确定。 LEFT按照数组{1;2;3;4;5;6;7;8;9}提取，得到9个结果：

也就是从左边开始提取1位，2位……一直到9位。因为LEFT提取的结果都是文本类型，在LEFT前面加上负号，就可以把其中的文本型数字转为数值，文字变成错误值：

错误值被LOOKUP忽略，现在就变成了在{-1; -10}中找1。1比这组数据都大，根据二分法查找原理，二分后只能向下找，直到最后一位小于1的数据。因此，我们可以简单理解成：当查找值大于查找范围中所有数据（不管是否是升序排列）时，LOOKUP的实质就是在找最后一个数据。

其实将公式中的1变成0也是可以的，因为0也比所有的负数大：

当前最后一个数是-10，所以我们在LOOKUP前面再加个负号，就变成10了，也就是我们需要提取的数字。

对于初学者来说，以上两个案例中LOOKUP的用法过于高级，即便是通过这些介绍，估计也是一知半解，其实学习函数是有一个过程的，从不认识到了解，从了解再到掌握，这里需要大量的练习和思考。只要大家保持积极乐观的心态，能够体会到学习函数的乐趣的时候，就离成功不远了。

转载：部落窝教育 如有侵权，联系作者，会删除