複数列にわたる INDEX および MATCH の使用

複数列にわたって値の一致を検索するには、INDEX 関数とMATCH 関数をベースにした配列数式に、さらにMMULT 関数、TRANSPOSE 関数、およびCOLUMN 関数を組み合わせるのが効果的です。

複数列にわたって値を検索する方法は？

上記の表に示されているように、各学生に対応するクラスを入力する必要があり、かつ情報が複数列にわたって配置されている場合は、まず MMULT 関数、TRANSPOSE 関数、および COLUMN 関数を組み合わせて行列配列を作成します。その後、MATCH 関数で検索値の位置を特定し、その位置を INDEX 関数に渡して目的の値を取得します。

一般的な構文

=INDEX()return_range,(MATCH(1,MMULT(--())))lookup_array=lookup_value),TRANSPOSE(COLUMN()lookup_array)^0)),0)))

√ 注意：これは配列数式であり、Ctrl+Shift+Enterキーを押して入力してください。

ジミーのクラスを検索するには、セル H5 に以下の数式をコピーまたは入力し、Ctrl+Shift+Enterキーを押して、結果を取得してください。

=INDEX()$B$5:$B$7,(MATCH(1,MMULT(--())))$C$5:$E$7=G5),TRANSPOSE(COLUMN()$C$5:$E$7)^0)),0)))

√ 注意：上記のドル記号（$）は絶対参照を示しており、数式を他のセルに移動またはコピーしても、数式内の名前およびクラスの範囲が変更されないことを意味します。ただし、検索値を表すセル参照にはドル記号を追加しないでください。他のセルにコピーした際に相対参照として動作させる必要があるためです。数式を入力したら、フィルハンドルを下方向にドラッグして、下のセルにもその数式を適用してください。

数式の説明

=INDEX()$B$5:$B$7,(MATCH(1,))MMULT()--($C$5:$E$7=G5),TRANSPOSE()COLUMN($C$5:$E$7)^0)),0)))

• --($C$5:$E$7=G5):このセグメントは、範囲 $C$5:$E$7 内の各値がセル G5 の値と一致するかどうかをチェックし、次のような TRUE/FALSE の配列を生成します：
  {TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE}。
  先頭の二重マイナス（--）により、TRUE と FALSE はそれぞれ1 と0 に変換され、次のような数値配列になります：
  {1,0,0;0,0,0;0,0,0}。
• COLUMN($C$5:$E$7):COLUMN 関数は、範囲 $C$5:$E$7の列番号を次のような配列で返します：{3,4,5}。
• TRANSPOSE()COLUMN($C$5:$E$7)^0)=TRANSPOSE(){3,4,5}^0):0 乗した後、配列{3,4,5}内のすべての数値は1 に変換され、{1,1,1}となります。その後、TRANSPOSE 関数により列配列が次のような行配列に変換されます：{1;1;1}。
• MMULT()--($C$5:$E$7=G5),TRANSPOSE()COLUMN($C$5:$E$7)^0))=MMULT(){1,0,0;0,0,0;0,0,0},{1;1;1}):MMULT 関数は、次のように2 つの配列の行列積を返します：{1;0;0}。
• MATCH(1,)MMULT()--($C$5:$E$7=G5),TRANSPOSE()COLUMN($C$5:$E$7)^0)),0)=MATCH(1,){1;0;0},0):match_type 0により、MATCH 関数は配列 {1;0;0} 内で1が最初に一致する位置を返します。その位置は1です。
• INDEX()$B$5:$B$7,(MATCH(1,))MMULT()--($C$5:$E$7=G5),TRANSPOSE()COLUMN($C$5:$E$7)^0)),0))) = INDEX($B$5:$B$7INDEX 関数は、クラス範囲 1の$B$5:$B$7からA 番目の値を返します。

複数の列にまたがって値を簡単に検索したい場合は、当社のプロフェッショナルなExcel アドインKutools For Excelもぜひご活用ください。このタスクの実行手順については、こちらをご確認ください。。

関連する関数

関連する数式