複数列にわたるINDEXとMATCH

複数列にわたって値を照合して検索するには、MMULT、TRANSPOSE, およびCOLUMNを組み合わせた配列数式を使用した INDEXおよびMATCH関数が役立ちます。

複数列にわたって値を照合して検索するにはどうすればよいですか？

上記の表に示されているように、各生徒に対応するクラスを入力する場合、情報が複数列にわたってリストされている場合、まずMMULT、TRANSPOSE、COLUMN関数のトリックを使用して行列配列を生成できます。その後、MATCH関数は検索値の位置を提供し、INDEXに渡されて配列内の目的の値を取得します。

汎用構文

=INDEX(返却範囲,(MATCH(1,MMULT(--(検索範囲=検索値),TRANSPOSE(COLUMN(検索範囲)^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(TRANSPOSE(COLUMN($C$5:$E$7)^0)^0) = TRANSPOSE(TRANSPOSE({3,4,5}^0)^0): 0乗すると、配列{3,4,5}のすべての数字が1に変換されます：{1,1,1}。TRANSPOSE関数は、列配列を次のように行配列に変換します：{1;1;1}。
• MMULT(MMULT(--($C$5:$E$7=G5),,TRANSPOSE(COLUMN($C$5:$E$7)^0))) = MMULT(MMULT({1,0,0;0,0,0;0,0,0},,{1;1;1})): MMULT関数は、2つの配列の行列積を次のように返します：{1;0;0}。
• MATCH(1,MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0),0) = MATCH(1,MATCH(1,{1;0;0},0),0): 一致タイプ 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$7,1): INDEX関数は、クラス範囲$B$5:$B$7の最初の値を返します。それはAです。

複数列にわたって値を簡単に検索するには、当社の専門的なExcelアドインであるKutools For Excelも使用できます。　ここで指示をご覧ください。

関連する関数

関連する数式