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

最近推送的五篇文章：

· 正 · 文 · 来 · 啦 ·

说明

这是2016年写的一篇旧文，搬到公众号上来。

引言

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

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。

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

本文示例文件下载地址：

http://vdisk.weibo.com/s/zm7DlBPwfe7sL

致谢：

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

增补答疑

前二天的介绍LOOKUP的文章出来后，有读者朋友反映看不懂多条件查找公式。现将《偷懒的技术》答疑汇集中的此部分答疑摘录如下：

多条件查找公式模型：

**=LOOKUP（1，0/（条件1*条件2），结果区域）**

公式解释：

“条件判断”计算结果只有二个：1和0；
“0/条件”的结果也只有二个：0或错误值#DIV/0!。公式0/（条件1*条件2）的结果是0或错误值#DIV/0!组成的数组。
LOOKUP特点：自动忽略错误值，是按二分法进行查找的，要求数据按升序排列。
LOOKUP在全是0或错误值#DIV/0!组成的数组中查找1，是找不到1的，根据二分法只会返回最后一个0。
所以：
公式“=lookup（1，0/（条件），返回结果区域）”会返回符合条件的最后一个值所对应的结果区域的值。

如果本文对你有帮助，走时别忘了点一下文章底部的广告↓和右下角的大拇指↘

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。