Excel公式技巧66：获取第n个匹配的值（使用INDEX函数）

excelperfect

在《Excel公式技巧65：获取第n个匹配的值（使用VLOOKUP函数）》中，我们构造了一个没有重复值的辅助列，从而可以使用VLOOKUP函数来查找指定的重复值。本文中仍然以此为例，使用INDEX函数来获取重复值中指定的值，但是不需要构造辅助列。

如下图1所示的工作表，在“商品”列中，存在一些重复的商品，现在我们要找出第2次出现的“笔记本”的销售量。

图1

在单元格G6中输入数组公式：

=INDEX($D$3:$D$14,SMALL(IF($C$3:$C$14=$G$2,ROW($C$3:$C$14)-ROW($C$3)+1),F6))

结果如下图2所示。

图2

公式中：

$C$3:$C$14=$G$2

将单元格区域C3:C14中的值与单元格G2中的值相比较，得到由布尔值组成的数组：

{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE}

公式中：

ROW($C$3:$C$14)-ROW($C$3)+1

生成一系列由1至单元格区域的最大个数的连续整数组成的数组：

{1;2;3;4;5;6;7;8;9;10;11;12}

这样，公式中的：

IF($C$3:$C$14=$G$2,ROW($C$3:$C$14)-ROW($C$3)+1)

解析为：

IF({TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE},{1;2;3;4;5;6;7;8;9;10;11;12})

得到数组：

{1;FALSE;FALSE;FALSE;FALSE;6;FALSE;FALSE;FALSE;FALSE;11;12}

即由所查找的商品对应的销售量和FALSE组成的数组。

代入SMALL函数中：

SMALL(IF($C$3:$C$14=$G$2,ROW($C$3:$C$14)-ROW($C$3)+1),F6)

解析为：

SMALL({1;FALSE;FALSE;FALSE;FALSE;6;FALSE;FALSE;FALSE;FALSE;11;12},2)

得到结果：

6

即要查找的商品第2次出现的位置。

代入INDEX函数中，得到：

=INDEX($D$3:$D$14,6)

结果为单元格D8中的值10。

如果使用定义的名称，那么公式将更灵活，如下图3所示。

图3