INDEXとMATCHを使用した複数条件の検索

Excelスプレッドシートで多数の列や行見出しを持つ大規模なデータベースを扱う場合、複数の条件を満たす項目を見つけるのは常に難しいものです。このような場合、配列数式としてINDEX関数とMATCH関数を使用できます。

複数の条件で検索を行うにはどうすればよいですか？

上記の画像に示されているように、白で中サイズ、価格が$18の製品を見つけるために、ブール論理を利用して、条件を満たす行を示す1と0の配列を生成することができます。その後、MATCH関数はすべての条件を満たす最初の行の位置を見つけます。その後、INDEX関数は同じ行に対応する製品IDを見つけます。

一般的な構文

=INDEX(返却範囲,MATCH(1,(条件値1=条件範囲1*条件値2=条件範囲2*(…),0))

√ 注: これは配列数式であり、Ctrl + Shift + Enterで入力する必要があります。

白で中サイズ、価格が$18の製品を見つけるには、以下の数式をセルH8にコピーまたは入力し、Ctrl + Shift + Enterを押して結果を得てください：

=INDEX(B5:B10,MATCH(1,("White"=C5:C10)*("Medium"=D5:D10)*(18=E5:E10),0))

または、セル参照を使用して数式を動的にすることもできます：

=INDEX(B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))

数式の説明

=INDEX(B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))

• (h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10): この数式は、セル内の色を比較します。 h3 範囲内のすべての色に対して C5:C10; サイズを比較します H5 すべてのサイズに対して D5:D10; 価格を比較します H6 すべての価格に対して E5:E10初期の結果は次のようになります：
  {TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}*{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE}*{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}.
  乗算により、TRUEとFALSEは1と0に変換されます：
  {1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
  乗算後、次のような単一の配列が得られます：
  {0;0;0;0;1;0}.
• MATCH(1,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0),0) = MATCH(1,MATCH(1,{0;0;0;0;1;0},0),0): 一致タイプ0はMATCH関数に完全一致を見つけるように指示します。関数は配列{0;0;0;0;1;0}の中の1の位置を返します。それは 5です。
• INDEX(B5:B10B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)) = INDEX(B5:B10B5:B10,5): INDEX関数は製品ID範囲B5:B10の5番目の値を返します。それは30005です。

関連する関数

関連する数式