又来了！找到最后一个非空值

excelperfect

这里纯粹是一次练习，或者你把它当作一次游戏也可以，当然，如果你在实际应用过程中碰到过类似的问题，那再好不过了，正好有了解决方案。

如下图1所示，在单元格区域A3:E12中查找单元格K2中的值，返回该值所在行最后一个非空值。

图1

你能够写出至少10个不同的公式，解决上述问题吗？

很显然，要返回某个单元格中的值，就要知道该单元格的地址，也就是行号列标，这是我们的基本思路。

好了，公式开始上场了。

行号很容易找到，使用MATCH函数：

MATCH($K$2,$A$3:$A$12,0)

列呢？知道行号后，可以使用INDEX函数获取整行，然后再使用COUNTA统计该行文本值个数，就是要获取的值所在的列了：

COUNTA(INDEX($A$3:$E$12,MATCH($K$2,$A$3:$A$12,0),))

将上述行列位置代入INDEX函数，得到相对应的值：

=INDEX($A$3:$E$12,MATCH($K$2,$A$3:$A$12,0),COUNTA(INDEX($A$3:$E$12,MATCH($K$2,$A$3:$A$12,0),)))

既然列所在的位置知道了，我们当然也可以使用VLOOKUP函数：

=VLOOKUP(K2,A3:E12,COUNTA(INDEX($A$3:$E$12,MATCH($K$2,$A$3:$A$12,0),)))

进一步，也可以使用ADDRESS函数得到单元格地址，然后使用INDIRECT函数取出该单元格中的值：

=INDIRECT(ADDRESS(ROW(A2)+MATCH(K2,A3:A12,0),COUNTA(INDEX(A3:E12,MATCH(K2,A3:A12,0),))))

换一下思路，可以使用其它的方式获取列所在的位置。由上文我们知道：

INDEX(A3:E12,MATCH(K2,A3:A12,0),)

获取了所查找的值所在的行。再判断该行中的单元格是否存在文本，返回对应的列标，取其最大值，即为所要查找的列的位置：

MAX(IF(ISTEXT(INDEX(A3:E12,MATCH(K2,A3:A12,0),)),COLUMN(A3:E3)))

代入INDEX函数，即可得到公式：

=INDEX(A3:E12,MATCH(K2,A3:A12,0),MAX(IF(ISTEXT(INDEX(A3:E12,MATCH(K2,A3:A12,0),)),COLUMN(A3:E3))))

这是一个数组公式，输入完成后要按Ctrl+Shift+Enter组合键。

当然，还有不同的方法来获取列的位置，下面是另一个公式：

=INDEX(B3:E12,MATCH(K2,A3:A12,0),SUM(MMULT(--TRANSPOSE(B3:E12<>''),IF(A3:A12=K2,1,0))))

这是一个数组公式，输入完成后要按Ctrl+Shift+Enter组合键。

换一种查找行列位置的形式，得到公式：

=INDEX(OFFSET($A$2,MATCH(K2,A3:A12,0),1,,4),MAX(ISTEXT(OFFSET($A$2,MATCH(K2,A3:A12,0),1,,4))*{1,2,3,4}))

这是一个数组公式，输入完成后要按Ctrl+Shift+Enter组合键。

还有吗？非得要先知道位置才能得到所要的值吗？既然是查找，当然要考虑LOOKUP函数了。

下面是一组使用LOOKUP函数的公式：

=LOOKUP(REPT('z',5),INDEX(B3:E12,MATCH(K2,A3:A12,0),))

或者：

=LOOKUP('Ω',INDEX(A3:E12,MATCH(K2,A3:A12,0),0))

或者：

=LOOKUP('zzz',OFFSET(A1,MATCH(K2,A:A,0)-1,,,1000))

或者：

=LOOKUP(2,1/LEN(INDEX(A3:E12,MATCH(K2,A3:A12,0),0)),INDEX(A3:E12,MATCH(K2,A3:A12,0),0))

这种情形下，LOOKUP函数真是简洁实用。

好了！你还有其它有趣的公式吗？

这只是一场游戏，不必当真！空闲时，动动脑，熟悉Excel公式与函数，还是很有意思的。

提示：你可以到知识星球完美Excel社群获取本文的配套示例工作簿，方便研习。