应用技巧九：妙用LOOKUP函数升序与乱序查找

应用技巧九：妙用LOOKUP函数升序与乱序查找

LOOKUP函数具有向量和数组两种语法形式。

LOOKUP(lookup_value,lookup_vector,result_vector)

LOOKUP(lookup_value,array)

向量语法是在由单行或单列（也就是“向量”）构成的第二参数中查找第一参数，并返回第三参数中对应位置的值。数组语法的第二参数可以使单行或单列，也可以是多行或多列，此时LOOKUP函数会根据第二参数的尺寸执行类似VLOOKUP函数或HLOOKUP函数升序查找的功能。

LOOKUP函数要求第二参数（如为数组语法，则是第二参数的首行或首列）按升序排列，并与小于或等于查找值的最大值匹配。

一、升序查找多个值

例：如图1所示，“表1”中加油站名称按升序排列，要求计算“表2”中3个加油站的销售业绩之和。

F7单元格的计算公式如下：

{=SUM(LOOKUP(E3:E5,A3:C8))}

思路解析：

该公式要求A列加油站名称已按升序排序，LOOKUP函数公式查找出E3:E5数据区域中3个加油站的业绩为{1295;1110;1355}，再进行求和，得到结果为3760。

二、多区间判断数值等级

例：如图2所示，要求根据《加油站等级标准》，按年销售量对D2:G10单元格区域中的加油站进行等级评定。

根据年销售量与加油站等级的对应关系，使用常量数组{0;2000;4000;6000;8000;10000}分别表示从0至2000吨（不含2000吨）、2000吨至4000吨（不含4000吨）……10000吨以上，并以此对应B3:B8的加油站等级。

在G2单元格中输入如下公式，并将公式向下复制填充至G10单元格：

=LOOKUP(F2,{0;2000;4000;6000;8000;10000},$B$3:$B$B8)

如果判断等级的数据区间为向上包含，例如(0,2000）、(2000,4000)等，则可以将第二参数加上一个小于F列数值最小当量的值（比如F列均为整数，则0.1不会影响其标准大小）来作为等级划分，例如如下公式：

=LOOKUP(F2,{0;2000;4000;6000;8000;10000}+0.1,$B$3:$B$B8)

思路解析：

通过两个公式对比可以看出：利用LOOKUP函数升序查找并返回小于等于查找值对应结果的原理，当查找值为4000时，第1个公式的常量数值中小于等于查找值的是第3个元素4000，因此返回第三参数的第3个元素即B5的“三级站”；而第2个公式由于加了0.1，则小于等于查找值的最大值为2000，因此返回结果为B4的“四级站”。

此类利用LOOKUP函数进行多个连续数值区间判断的方法，可以替代IF函数的复杂多层嵌套解法。

三、乱序查找最后一个满足条件的记录

按照LOOKUP函数的要求，第二参数的首列（行）必须升序排列，并且具有“如果LOOKUP找不到lookup_value，则它与lookup_vector中小于或等于lookup_value的最大值匹配”的特性。当所要查找的值大于被查找区域的所有同类型数据时，LOOKUP函数将返回最后一个与第一参数类型相匹配的值。

例：如图3所示，A列中由数值、空单元格、错误值、文本等多种数据组成，要求取出最后一个文本、最后一个数值和最后一条记录。

分别在C1:C3单元格中输入以下公式：

公式1  =LOOKUP(“々”,A:A)

公式2  =LOOKUP(9E+307,A:A)

公式3  =LOOKUP(1,0/(A2:A10<>””),A2:A10)

思路解析：

公式1使用符号” 々”最为查找值，一般可以满足查找最后一个文本记录的需求，但当数据中有以” 々”字开头的字符串时，可以使用如下公式：

=LOOKUP(PEPT(“々”,9),A:A)

也就是可以查找以9个连续的“々”字开头的文本（一般不会有这样的记录）。

公式2使用一个接近Excel规范与限制允许键入最大数值的数，即9*10^307作为查找值，可满足查找最后一个数值（含日期、时间）记录的需要。

公式3则以0/(A2:A10<>””)构建一个0、#DIV/0!组成的数组，再用大于第二参数中所有数值的1（已经足够大）最为查找值，即可满足查找最后一个满足A2:A10不为空单元格条件的记录。

可以归纳为如下内容：

=LOOKUP(1,0/(条件),目标区域或数组)

其中，条件可以是多个逻辑判断相乘组成的多条件数组。

四、填补合并单元格的空缺

例：如图4所示，A列的片区采用合并单元格方式，要求统计“东区”的加油站个数。

在A13单元格中输入片区名称，在B14单元格中输入数组公式，即可得到加油站个数：

{=SUM(--(LOOKUP(ROW(2:10),IF(A2:A10<>””,ROW(2:10)),A2:A10)=A13))}

思路解析：

在此例中，A列合并单元格实际只有A2、A6、A9这3个单元格有数据（示意如D列），其他单元格都是空单元格。因此不能直接用COUNTIF函数统计“东区”的个数，而需要将A3:A5、A7:A8、A10单元格分别填补数据后再统计。

在公式中，利用IF函数判断，返回A列非空单元格的行号与FALSE，再利用LOOKUP函数查找ROW(2:10)构成的行号数组{2;3;4;5;6;7;8;9;10}，根据“如果LOOKUP找不到lookup_value，则它与lookup_vector中小于或等于lookup_value的最大值匹配”的特性，例如查找行号4，则与之匹配的小于等于4的最大值为2，由此返回对应的A2单元格的“东区”，其他行同理类推。从而，LOOKUP函数构建了一个9行的数组，效果如图中F列所示。最后，再使用SUM函数统计该数组中等于A13单元格值的个数。