Excel 函数学习06-LOOKUP函数

1 HLOOKUP函数

与VLOOKUP相对应的有一个LOOKUP函数，还有一个HLOOKUP……什么？

HLOOKUP常年处于隐居状态，据说非有缘不得见。VLOOKUP的V是vertical垂直纵向查询之意，HLOOKUP的H是Horizontal水平横向之意，其主要作用也就是横向查询。

语法结构如下：

=HLOOKUP(找谁？在哪里找？查找的结果在查找范围的第几行？零失误精确找还是随便找找？)

HLOOKUP的语法结构和VLOOKUP几乎一模一样。只是第3参数从VLOOKUP查找结果在查找范围的第几列，变成了第几行……：

举个栗子。

如上图所示，需要查询B8单元格指定姓名（比如“李清照”），在B9单元格指定月份（比如3月）的销售金额。

B10单元格公式如下：

=HLOOKUP(B8,A1:E5,MATCH(B9,A:A,0),0)

HLOOKUP默认查找范围的首行为匹配行，从中搜索B8单元格的值首次出现在查找范围的第几列，第3参数是一个MATCH函数，用于计算查找值在查找范围的第几行，行列交叉之处，即为目标值。

当然，该例我们也可以使用VLOOKUP函数，公式如下：

=VLOOKUP(B9,A1:E5,MATCH(B8,1:1,0),0)

还可以使用INDEX+MATCH函数，公式如下：

=INDEX(A1:E5,MATCH(B9,A:A,0),MATCH(B8,1:1,0))

提供另外两种函数的解法倒不是我想灌水，而是揭露一个问题。我们上面说HLOOKUP常年处于隐居状态，那是给她留面子，坦率而言，在VLOOKUP和INDEX的夹击之下，这家伙基本上是英雄无用武之地。

2 认识LOOKUP

话说十年之前，Excel函数方兴未艾，大家对函数世界的探索还很热情，LOOKUP是那时公认的超级函数之一。超级有两方面的意思，一方面是LOOKUP非常强大，在数据查询的问题上，基本没有什么是它解决不掉的事；另一方面是这家伙非常复杂，甚至有人根据它的语法，猜想出该函数使用了编程上的经典算法二分法（不过后来证明这猜想在逻辑上完全不能自洽）。
——这是十年之前。如今十年过去了，再复杂的函数也都被总结出简单的套路了。
LOOKUP函数的官方语法有两个——但都没有太大实用价值。这个函数已经被玩到官方定义的语法成为废纸、民间的套路成为事实上的语法的地步；所以所谓官方语法看一眼就好。

语法1，向量形式：
=LOOKUP(lookup_value,lookup_vector,result_vector)

语法2，数组形式：
= LOOKUP(lookup_value,array)

核心法则也就只有两个：区间查询和条件查询。

3 LOOKUP的区间查询

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

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

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

举个例子。

如下图所示，需要根据F:G的评分标准，对A:C数据区域的得分作评级。将查询区域划分为了多个区间，并升序排列。

C2公式如下：

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

F列是升序排列的查找区域，G列是其对应的结果区域。

……可能需要再次强调的是，这种区间查询方式，要求查找区域的首列必须升级排列！有朋友可能会说F列没有升序排列啊？F2单元格的0比F1单元格的"分数"小。这没事，LOOKUP聪明的很，你不说他都知道F1是不是标题行。还记得SUMIF吗？还是那句话，它们身为长子，不聪明是不行滴。

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

=LOOKUP(B2,F:G)

这是因为当查找区域是多列，同时又省略结果区域时，比如上述公式，LOOKUP默认查找区域的首列（F列）为查找区域，同时默认其末列（G列）为结果区域。

注意，我说的是末列，而不是第2列。

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

=LOOKUP(B2,F:H)

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

4 条件查询：

LOOKUP的条件查询套路格式如下：

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

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

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

=LOOKUP("座",A:A,A:A)

“座”是文本型数据里极大的值，比绝大部分常见的文本值均大，因而该公式返回A列最后出现的文本值。由于该公式的查找区域和结果区域是相同的，也可以写为以下形式：

=LOOKUP("座",A:A)

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

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

=LOOKUP(9^9,A:A)

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

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

那我们就把这个套路换个形式延伸一下……

如下图所示，需要查询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=条件值),结果)

……