INDEX および MATCH による複数条件検索

Excel スプレッドシートで多数の列と行見出しを持つ大規模なデータベースを扱う際、複数の条件を満たすデータを検索するのは常に難しいものです。そんなときは、INDEX 関数とMATCH 関数を組み合わせた配列数式が活用できます。

複数条件による検索を実行するには？

上記の画像に示されているように、「白色」で「ミディアムサイズ」、価格が「$18」の製品を検索するには、ブール論理を使って条件を満たす行を示す1 と0 の配列を生成できます。その後、MATCH 関数がすべての条件を満たす最初の行の位置を特定し、INDEX 関数がその行にある対応する製品 ID を取得します。製品白ミディアム18

一般的な構文

=INDEX()return_range,MATCH(1,())criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),0))

※注：これは配列数式です。Ctrl＋Shift＋Enterキーを押して入力してください。

価格が$18 の白色でミディアムサイズの製品を検索するには、以下の数式をセル H8 にコピーまたは入力し、Ctrl + Shift + Enter キーを押して結果を取得してください。白ミディアム18CtrlShiftEnter

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

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

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

数式の説明

=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}. The initial result is like this:
  {TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}*{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE}*{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}.
  The multiplication will transform the TRUEs and FALSEs to 1s and 0s:
  {1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
  After multiplication, we will have a single array like this:
  {0;0;0;0;1;0}.
• MATCH(1,)(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)=MATCH(1,)match_type が 0 のとき、MATCH 関数は完全一致を検索します。これにより、関数は配列 {0;0;0;0;1;0} 内で値が 1 となる位置を返します。その位置は 5 です。01{0;0;0;0;1;0}5
• INDEX()B5:B10,MATCH(1,)(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)) = INDEX(B5:B10INDEX 関数は、製品 ID の範囲 内の5 番目の値を返します。その値はです。5B5:B1030005

関連関数

関連数式