用 INDEX 检索第 n 个匹配项

若需在某一区域内查找某值第 n 次出现并返回对应信息，可使用基于 INDEX、ROW、SMALL 及 IF 等函数的公式轻松实现。

如何使用 INDEX 检索某个值第 n 次出现时的相关信息？

如需获取 Usain Bolt 第 3 次匹配对应的信息（如上表所示），可结合 ROW 和 IF 函数，先生成 Usain Bolt 所有值所在行数的数组，再通过 SMALL 函数提取第 n 次匹配的行号，最后将该结果传递给 INDEX 函数，即可获取相同行的对应信息。

通用语法

=INDEX(）return_range,SMALL(IF(））lookup_array=lookup_value,ROW(）lookup_array-ROW(INDEX(））lookup_array,1,1))+1),n））

√ 注意：这是一个数组公式，需同时按下 Ctrl+Shift+Enter 请输入内容。

要获取 Usain Bolt 第 3 次匹配对应的信息，请将下列公式复制或输入到 G8 和 G9 单元格，并按下 Ctrl+Shift+Enter，即可获得结果：

年份（单元格 G8）
=INDEX(）B6:B12,SMALL(IF(））C6:C12=G5,ROW(）C6:C12-ROW(INDEX(））C6:C12,1,1))+1),G6））
时间（单元格 G9）
=INDEX(）D6:D12,SMALL(IF(））C6:C12=G5,ROW(）C6:C12-ROW(INDEX(））C6:C12,1,1))+1),G6））

公式说明

以下以此公式为例说明：

=INDEX(B6:B12,）SMALL(）IF(C6:C12=G5,）ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6))

• ROW(C6:C12):ROW 函数会返回区域 C6:C12 中每个单元格所在的行号，并以如下数组形式呈现：{6;7;8;9;10;11;12}。
• ROW(INDEX(C6:C12,1,1)):INDEX 和 ROW 函数可返回查找区域 C6:C12 在当前工作表中的首行序号，即 6.
• IF(C6:C12=G5,）ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1)=IF(C6:C12=G5,）{6;7;8;9;10;11;12}-6+1):IF 函数会判断区域 C6:C12 中的每个值是否与单元格 G5（即 ）UsainBolt）相等。若相等，则返回“对应行号减去 6 再加 1”的结果，否则返回 FALSE。最终结果将生成如下数组：{FALSE;2;3;4;FALSE;FALSE;FALSE}。
• SMALL(）IF(C6:C12=G5,）ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6)=SMALL(）{FALSE;2;3;4;FALSE;FALSE;FALSE},G6): 此函数片段返回数组中第 3 个（即单元格 ）G6 中的值）最小值，也就是 4.
• INDEX(B6:B12,）SMALL(）IF(C6:C12=G5,）ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6)) = INDEX(B6:B12,4)INDEX 函数将返回年份区域中的第 4 个值（即 ）B6:B12 区域的第 4 行），该值为 北京 2008.

相关函数

相关公式