INDEX で n 番目の一致を取得する

範囲からある値の n 番目に一致するものを検索し、その対応するデータを取得するには、INDEX 関数、ROW 関数、SMALL 関数、およびIF 関数を組み合わせた数式を使用できます。

INDEX を使ってある値の n 番目の一致に関連する情報を取得する方法は？

上記の表からウサイン・ボルトの3 番目の一致に関する情報を取得するには、ROW 関数と IF 関数を組み合わせて、「ウサイン・ボルト」のすべての該当行の行番号からなる配列を作成します。その後、SMALL 関数で n 番目に小さい行番号（＝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を押す必要があります。

ウサイン・ボルトの3 番目の一致に関する情報 を取得するには、以下の数式をセル G8 および G9 にコピーまたは入力し、Ctrl ＋Shift ＋Enter を押して結果を取得してください： to get the results:

年（セル 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,)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（つまり)ウサインボルト）と一致するかどうかをチェックします。一致した場合は「対応する行番号－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):この式は、配列からG6セルで指定された3 番目に小さい値として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から返します。その値は北京2008です。

関連関数

関連数式