Excel 之 LOOKUP

其实讲道理，我平时是不用 Excel 的，但是偶尔帮别人解决一些问题也被迫学习了一下。

背景

LOOKUP 顾名思义，就是去一个集合中查找一个满足条件的值，先上官方文档。

LOOKUP 函数有三个参数：1. lookup_value，即要查找的值；2. lookup_vector，查找范围；3. 可选的 result_vector，表示对应的返回结果。

看上去很简单不是吗？然而很多时候，我们的需求并不会那么简单。比如查找一列数字中的最后一个，或者查找最后一个在某个范围中的数字，但是却不知道具体的值。

分析

那么问题来了，我们真的可以用 LOOKUP 实现这么复杂的功能吗？这里要牵涉到 LOOKUP 的几个特性：

1. 如文档中所说， lookup_vector 应该是升序的，所以 Excel 才能在查找的时候能方便地找到最后一个不超过 lookup_value 的值。但实际上，如果 lookup_vector 中的值全都比 lookup_value 小，这个顺序就不重要了， LOOKUP 一定会返回最后一个。

2. LOOKUP 会自动忽略查找范围中的非法值。这一点文档中我没找到，却是各种复杂查找公式中至关重要的一环，我也是各种尝试之后才意外发现的。

根据这些特性我们可以制定一下接下来的查询方案了：

1. 将整个数列转成一个新的数列。

   这个新数列将是我们的查找范围，文档中要求其是升序的，但实际上可以根据需要适当放宽要求。需要注意的是，升序数列中是可以出现前后两个数值相等的，所以如果只是单纯查找最后一个数，我们可以简单地将数值全都转成 0 。

2. 将其中需要忽略的值转成非法值。

   比如忽略空值： 0/(A:A<>"") 。A:A<>"" 会将 A 列转成一个 {TRUE, FALSE, ...} 的集合，其中原值为空的是FALSE ，然后去除一个数（这里是 0 ），则TRUE 会自动转成 1 ，FALSE 会自动转成 0 ，然后0 / 0 就成了一个非法值，即所有空值都成了非法值，将在 LOOKUP 的查询中被自动跳过。

3. 选择合适的查找值。

   如果我们就是单纯地找最后一个数，只需要把查找值定为一个永远无法达到的值即可。比如前一步中我们把合法值都转成了 0 ，那么只需要把查找值定为 1 ，就可以让 LOOKUP 因为找不到查找值，而返回最后一个合法值。

4. 指定返回结果。

   如果不指定返回结果，返回值就变成了我们转换后的值（这里是 0 ），就没有查找的意义了，因此要显式地指定查找到的项对应的结果，比如这里就是对应到原数列A:A ，如果要返回行的序号，则对应到ROW(A:A) 。

综上所述，可得公式LOOKUP(1,0/(A:A<>""),A:A) 。

进阶

一切都豁然开朗了有木有！那么再考虑一个比较复杂的情况：查找数列中不等于 5 且不等于 8 的最后一个数。

其实有了上面的基础也就不复杂了，只需要把 5 和 8 都转成非法值即可： 0/(A:A<>"")/(A:A<>5)/(A:A<>8) ，多除几次就把不要的数字除掉了，就是这么简单！