用lookup填充满合并单元格

小伙伴们，大家好。今天要分享的内容是lookup在合并单元格中的应用。先来看下数据源和我们要实现的效果。下图左表是数据源，部门列是合并单元格；右表是实现后的效果。我们的要求是查找出每个部门最后一个人的姓名。比如销售部最后一个人的姓名是丁波山，已用浅色标出。

我们先把右表的结构弄好，对于右表部门那一列，我们可以手动输入或用基础操作得到，也可以用函数得到。这里我就用函数来完成，在E3单元格输入公式=INDEX(A:A,SMALL(IF(A$2:A$11<>"",ROW($2:$11),4^8),ROW(A1)))&""，按ctrl+shift+enter三键结束，向下填充。这个公式已经说过好多次了，就不再说明了。

右表结构构造好了，想想怎么查询出每个部门的最后一个姓名。查询最后一次可以想到用lookup，但是A列的部门是合并单元格。我们知道合并单元格中的内容是在左上角的单元格中，其他单元格是空值。所以我们要想办法把合并单元格的空值也要填充满内容。那么我们可以把合并单元格取消合并，然后再填充满。

你说我的合并单元格不能取消，领导就要合并单元格。那么我们还可以用辅助列的方法。在C2单元格输入公式=LOOKUP("做",A$2:A2)，向下填充，就得到填充后的部门。做的拼音是zuo，在汉字的排序中靠后，相当于数字中9e307的用法。而lookup的第2参数是动态区域，下拉会扩展，所以会找到最后一个非空的文本。

接下来就可以用辅助列来查找出每个部门的最后一个姓名，在F3单元格输入公式=LOOKUP(1,0/(E3=C$2:C$11),B$2:B$11)，向下填充。这就是lookup的经典用法=lookup(1,0/(条件=条件区域),返回的区域)。

如果不让用辅助列，能不能做出来呢？答案是可以的，我们只需要用内存数组生成辅助列的结果就可以了。那么怎么样用内存数组生成辅助列的内容才是关键，也是我今天主要想分享的内容。内存数组的公式为=LOOKUP(ROW($2:$11),0/(A$2:A$11<>"")+ROW($2:$11),A$2:A$11)。还是用的lookup，不过用的是数组的用法。

lookup的第1参数ROW($2:$11)是一个序号数组，也就是部门区域对应的行号，如下图C列所示。第2参数0/(A$2:A$11<>"")+ROW($2:$11)，是为了将非空的返回对应的行号，将空值返回错误值，如下图D列所示，而lookup可以忽略错误值。第3参数A$2:A$11还是部门列的区域。返回的对应结果如下图所示，比如第1参数的2在第2参数中找到2，返回对应的销售部，第1参数的3在第2参数中找到2，还是返回对应的销售部。

既然内存数组可以返回辅助列的结果，那就可以用内存数组代替辅助列。完整的公式为：在F3单元格输入公式=LOOKUP(1,0/(E3=LOOKUP(ROW($2:$11),0/(A$2:A$11<>"")+ROW($2:$11),A$2:A$11)),B$2:B$11)，向下填充，完成。

除了我说的这种方法，大家也可以想想其他的方法，我现在相信Excel的每个问题都会有多种解法。

文件链接：

https://pan.baidu.com/s/16I0Z1jCo5ACjz3z87ow1oA

提取码：weh1