深入理解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的查找原理。

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

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