使用 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:C12C6:C12C6:C12=G5,ROW(C6:C12C6:C12C6:C12-ROW(INDEX(C6:C12C6:C12C6:C12,1,1))+1),G6))
時間（單元格 G9）
=INDEX(D6:D12,SMALL(IF(C6:C12C6:C12C6:C12=G5,ROW(C6:C12C6:C12C6:C12-ROW(INDEX(C6:C12C6:C12C6: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 在此 Excel 工作表中的第一行號，即 6。
• IF(C6:C12=G5,IF(C6:C12=G5,ROW(C6:C12)--ROW(INDEX(C6:C12,1,1))+1)+1) = IF(C6:C12=G5,IF(C6:C12=G5,{6;7;8;9;10;11;12}--6+1)+1): IF 函數檢查範圍 C6:C12 中的每個值是否與單元格 G5 中的值 Usain Bolt 匹配。如果匹配，函數將返回“其對應行號 - 6 + 1”的結果；否則返回 FALSE。因此，結果會形成如下陣列：{FALSE;2;3;4;FALSE;FALSE;FALSE}。
• SMALL(SMALL(IF(C6:C12=G5,ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6),G6) = SMALL(SMALL({FALSE;2;3;4;FALSE;FALSE;FALSE},G6),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 函數返回年份範圍 B6:B12 中的第 4 個值，即 北京 2008。

相關函數

相關公式