深入理解LOOKUP:LOOKUP函数的查找原理

一只小猴，坐在一堆按从小到大排好顺序的包谷前，想拿一根最大的包谷，但小手又太小了，比手大的包谷拿不了，怎么才能用最快的方法找到拿得下的最大的包谷呢？

Excel对LOOKUP函数的帮助写了这么一句话：“查找区域”中的值如果不按升序排列，LOOKUP函数可能无法返回正确的值。我们使用LOOKUP数据按升序的时候少，乱序的情况用得更多。所以，必须得弄清楚什么情况下无法准确地返回查找值？如果不弄清楚，公式结果是怎么错的都不知道，后患无穷。要弄清楚，就必须得弄清楚查找原理。

一、LOOKUP函数基本知识

语法：

LOOKUP（查找值，查找区域，结果区域）

示例：=LOOKUP("龙逸凡",A2:A7,B2:B7)

要点：

1、“查找区域”中的值必须按升序排列：...,-2,-1,0,1,2,...,A-Z,FALSE,TRUE；否则，LOOKUP 可能无法返回正确的值。文本不区分大小写。

2、如果LOOKUP 函数找不到“查找值”，则该函数会与“查找区域”中小于或等于“查找值”的最大值进行匹配（注：在满足要点1“数据升序排列”的前提下）。

3、Excel帮助文件上要求：“查找区域”与“结果区域”大小必须相同，实际上可以不必相同。

LOOKUP第三参数如果为一个单元格（比如输入B2或B2：B2），则取值范围默认为横向，等同于B2:H2、B2：M2等。

如果第三参数是包含二个单元格以上的纵向单元格区域，则单元格范围大小不影响公式的计算。比如下面的公式是等效的

=LOOKUP("龙逸凡",A2:A7,B2:B3)

=LOOKUP("龙逸凡",A2:A7,B2:B7)

=LOOKUP("龙逸凡",A2:A7,B2:B65535)

有点类似于SUMIF第三参数（SUMIF第三参数真正起作用的是第三参数单元格区域左上角那个单元格，起到坐标定位的作用，详见本人写的《深入理解SUMIF：如何多表多列多条件求和？》

二、LOOKUP函数的查找原理

查找原理和要点可总结为四条：

1、LOOKUP函数要求“查找区域”中的值必须按升序排列。如果“查找区域”没有按升序排列，Lookup函数也会很傻很天真地认为你是个按规则办事的人，你已将数据按升序排列。

注意：这一点对理解LOOKUP函数的查找逻辑非常重要。

2、由于“查找区域”已按要求按升序排列，为了提高查找效率，LOOKUP都是按二分法查找。

具体的查找方式：

假设要查找的值为X，将X与已升序排列的“查找区域”最中间位置的那个数（我们称之为“位中值”）进行比较：

若X等于或大于“位中值”，由于已按升序排列，前半段的数肯定比位中值更小，所以就在“查找区域”的后半段中，继续按二分法进行查找。

若X小于“位中值”，由于已按升序排列，后半段的数肯定比位中值更大，所以就在“查找区域”的前半段中，继续按二分法进行查找。

确定中间位置的计算方式：个数为奇数时取最中间那个，为偶然个时并列最中间的那两个数的左边那个。

3、如果LOOKUP 找不到与“查找值”相等的数，它会使用“查找区域”中小于或等于“查找值”的最大值。

要注意的是：“查找区域”乱序的时候，并不一定会返回小于或等于的最大值，而是一直按二分法进行查找，直到查找到相等的数或查找完“按规则应该查找的位置”（不是查找完所有的值），如果还找不到相等的数，就返回结束查找前最近一个符合条件的值。如果有多个符合条件，则返回最后一个符合条件的值。

4、LOOKUP在查找区域中的遇到空值和错误值时，会忽略空值和错误值，继续在后半段进行二分法查找。

下面举例解释一下LOOKUP的查找原理。

如下图，我们要在A2:K2单元格区域查找9，并返回A3:K3区域对应的值，公式计算结果为8。

如果将H2单元格8.1改为小于等于9的任何数，比如2，接前面第三步：公式均返回A3:K3对应的值8。

如果将H2单元格8.1改为大于9的任何数，比如100，接前面步骤三：由于100〉9，不符合条件，则公式返回上一个符合条件的7.1所对应的值7。

如果查找值是8，H2仍为8.1，则8.1>8，不符合条件，所以就返回符合条件的最后一条值7.1所对应的7。

为了帮助理解，多举几个例子，大家按照上面的方法一步步进行二分法查找，琢磨一下查找原理。

本文示例文件下载地址：

致谢：

本文参阅学习了微软最有价值专家MVP 方骥的文章《LOOKUP二分法查找策略图文详解》，也是在其文章的基础上进一步的细化讲解，在此向方骥老师表示致敬和感谢！！