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関数は、このExcelワークシートでの検索範囲C6:C12の最初の行番号を返します。この場合、それは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です。

関連する機能

関連する数式