你所烦恼的LOOKUP函数，原来是这么回事

你所烦恼的LOOKUP函数，原来是这么回事

话说十年之前，Excel函数方兴未艾，大家对函数世界的探索还很热情，LOOKUP是那时公认的超级函数之一。超级有两方面的意思，一方面是LOOKUP非常强大，在数据查询的问题上，基本没有什么是它解决不掉的事儿；另一方面是这家伙非常复杂，甚至有人根据它的语法，猜想出该函数使用了编程上的经典算法二分法（不过后来证明这猜想在逻辑上完全不能自洽，如果你感兴趣，这点我们最后再聊）。

——这是十年之前。如今十年过去了，再复杂的函数也都被总结出简单的套路了。

LOOKUP函数的官方语法有两个——但都没有太大实用价值。这个函数已经被玩到官方定义的语法成为废纸、民间的套路成为事实上的语法的地步；所以所谓官方语法看一眼就好。

语法1 向量形式▼

=LOOKUP(lookup_value,lookup_vector,result_vector)

语法2 数组形式▼

= LOOKUP(lookup_value,array)

……自古深情留不住，总是套路得人心。网上流传的LOOKUP套路有很多，但看破表相之后，核心法则也就只有两个：区间查询和条件查询。

此外，虽然是老生常谈，但依然要说的是，如果您是函数新手，本章内容有看不懂的部分，又很想看懂，请重读函数基础之要，也就是函数系列教程的2、3、7章，特别是第7章：为什么说0和1是Excel函数逻辑运算的核心要义。

1

区间查询

LOOKUP函数的区间查询套路语法格式如下：

=LOOKUP(查找值，首列升序排列的查找区域，结果区域)

需要说明的是，使用此套路时，查找范围的首列必须升序排列。

举个例子。

如下图所示，需要根据F:G的评分标准，对A:C数据区域的得分作评级。大于等于0小于60为及格，大于等于60小于80为及格，大于等于80小于90为优良，大于等于90小于95为优秀，大于等于95为优异……也就是将查询区域划分为了多个区间，并升序排列。

C2单元格输入以下公式并向下复制填充即可：

=LOOKUP(B2,F:F,G:G)

F列是升序排列的查找区域，G列是其对应的结果区域。LOOKUP从查找区域中寻找小于等于查找值的最大值，比如寻找81，查询区域中（F列）小于该值的最大值是80，于是先获取结果80，然后再返回80所对应的G列结果”优良”。

……可能需要再次强调的是，这种区间查询方式，要求查找区域的首列必须升级排列！有朋友可能会说F列没有升序排列啊？F2单元格的0比F1单元格的”分数”小。这没事，LOOKUP聪明的很，你不说他都知道F1是不是标题行。

本例也可以使用以下公式：

=LOOKUP(B2,F:G)

这是由于当查找区域是多列，同时又省略结果区域时，LOOKUP会默认查找区域的首列（本例为F列）为查找区域，同时默认其最后一列（本例为G列）为结果区域。

注意，咱们说的是最后一列，而不是第2列。

例如，在D2单元格输入以下公式可以返回H列的评级

=LOOKUP(B2,F:H)

LOOKUP默认首列F列为查找区域，末列H列为结果区域。

看个广告放松一下

毕竟没有广告的推文是不真诚的..▼

2

条件查询

LOOKUP函数的条件查询套路语法格式如下：

=LOOKUP(一个比查找范围内所有同类型值都大的值，查找区域，结果区域)

该套路固定返回查找区域最后一个同类型数据所对应的结果。

比如，查找A列最后出现的文本：

=LOOKUP(“座”,A:A,A:A)

“座”是文本型数据里极大的值，比绝大部分常见的文本值均大，因而该公式返回A列最后出现的文本值。

由于该公式的查找区域和结果区域是相同的，也可以写为以下形式：

=LOOKUP(“座”,A:A)

当省略结果区域时，LOOKUP默认查找区域即为结果区域。

再比如，查找A列最后出现的数值：

=LOOKUP(9^9,A:A)

9^9是9的9次方，一个极大的数值，比绝大部分数值均大，因而该公式返回A列最后一个数值。

……

觉得这个套路没有什么实用价值？

打个响指，那咱们就把这个套路换个形式延伸一下……

如下图所示，需要根据A:B列的数据源，查询D2单元格指定人名的考试成绩。

这是一个单条件的查询问题，LOOKUP公式如下：

=LOOKUP(1,0/(A1:A10=D2),B1:B10)

(A1:A10=D2)部分判断A1：A10区域的值是否等于D2，返回由逻辑值TRUE和FALSE组成的内存数组:

{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

使用0除以该数组，0/TRUE结果为0，0/FALSE结果为错误值#DIV/0!，也就返回一个由0和错误值构成的内存数组：

{#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

LOOKUP自带天赋忽略错误值，而查找值1又比查找范围内所有的0都大，因而该公式可以直接返回最后一个符合条件的值所对应的结果。

该公式总结一下，也就成了经典的LOOKUP单条件查询套路：

=LOOKUP(1,0/(条件区域1=条件值),结果区域)

……

把这个套路再延伸一下，就可以成为”并且”关系的多条件查询经典套路（注意括号的个数和位置）：

代码看不全可以左右拖动…▼

=LOOKUP(1,0/((条件区域1=条件值1)*(条件区域2=条件值2)*…(条件区域n=条件值n)),结果区域)

举个例子。

如下图所示，需要根据A:C列数据源，计算同时满足E列期次和F列姓名的数据。

G2公式如下：

=LOOKUP(1,0/((A$1:A$10=E2)*(B$1:B$10=F2)),C$1:C$10)

(A$1:A$10=E2)*(B$1:B$10=F2)部分，使用乘法运算，判断两个条件是否同时成立，返回由逻辑值TRUE和FALSE组成的内存数组。

然后还是使用0除以该数组，得到一个由0和错误值构成的单列内存数组。

1作为LOOKUP的查找值比查找范围内所有的数值均大，因而该公式直接返回最后一个符合条件的查询结果。

此外，和INDEX+MATCH函数不同的是，由于LOOKUP天生支持数组运算，所以尽管该公式执行了多项运算（一次性计算了多个值），也并不需要使用数组三键来结束公式的输入——这就对公式的编写和维护带来了极大的便利。

……

继续延伸，把上述套路的乘法换做加法，就可以成为”或”关系的多条件查询套路：

代码看不全可以左右拖动…▼

=LOOKUP(1,0/((条件区域1=条件值1)+(条件区域2=条件值2)+…(条件区域n=条件值n)),结果)

同样举个例子。

如下图所示，需要根据A:C列数据源，查询E列的姓名满足A列别称或B列全称的成绩。

F2公式如下：

代码看不全可以左右拖动…▼

=LOOKUP(1,0/((A$1:A$10=E2)+(B$1:B$10=E2)),C$1:C$10)

(A$1:A$10=E2)+(B$1:B$10=E2)部分，使用加法的形式，判断两个条件是否至少有一条是成立的，返回一个有逻辑值TRUE和FALSE构成的内存数组，剩下的计算步骤就又回到了原来的轨道。

……

依然延伸，搭配FIND函数，就可以成为模糊匹配的查询套路：

=LOOKUP(1,0/FIND(条件区,条件值),结果区域)

依然举个例子：

如下图所示，A列是简称，B列是成绩，需要根据D列的全称查找相应的成绩。

公式如下：

=LOOKUP(1,0/FIND(A$1:A$8,D2),B$1:B$8)

FIND(A$1:A$8,D2)部分，判断A1:A8的值在D2单元格是否存在，如果存在，返回数字序号，如不存在，返回错误值，比如此例返回内存数组：

{#VALUE!;3;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

用0除以该数组，生成一个由0和错误值构成的内存数组……后面的计算步骤还是又回到了原来的轨道。

放飞你的思维，继续延伸，你就可以得出”并且”关系下的多条件模糊匹配套路：

=LOOKUP(1,0/(FIND(条件区1,条件值1)*FIND(条件区2,条件值2)),结果区域)

换成加法，也就成了或关系多条件模糊匹配套路：

=LOOKUP(1,0/(FIND(条件区1,条件值1)+FIND(条件区2,条件值2)),结果区域)

……

3

结束语

分享了这么多栗子，其实只是LOOKUP在比较规整的单元格区域内的查询用法，而没有涉及到数据结构转换以及字符串处理，也就是说，这一切还只是LOOKUP强大用法的冰山一角……

所以，当INDEX+MATCH说他们比VLOOKUP强大百倍时，作为VLOOKUP的大哥LOOKUP只会冷笑一声，拢一拢衣袖，心想，呵呵，我比你俩的组合强大的又何止百倍……

当然，所谓强大，也并不是实用的意思。

这就好比大炮固然强大，用来打蚊子那就成了笑话。INDEX+MATCH固然比VLOOKUP强大，但很多情况下，它并不比VLOOKUP书写简便，LOOKUP固然比INDEX+MATCH强大，但很多情况下，它的计算效率又比较低下

这三者之间到底有哪些优劣之处？我们下一章再聊。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。