INDEX-MATCHで複数条件検索を行う

1つの列だけでは行を一意に特定できないことがあります。特定のサイズの商品の価格や、特定の部署の従業員の給与が必要になる場合などです。

このような場合は、複数条件検索を使います。2列以上を同時に照合し、目的の行を正確に特定します。

INDEX-MATCHでは、追加のヘルパー列を使わずに条件を1つの照合テストへ組み合わせることで、これを簡潔に実現できます。

最も簡単な考え方は、キーとなる列を1つに結合することです。商品とサイズを連結するヘルパー列を追加し、その列に対して通常の検索を行います。

たとえば、ヘルパーセルに=A2&"|"&B2と入力すると、「Shirt|Large」が生成されます。次に、結合した列から「Shirt|Large」をMATCHで検索します。

この方法は機能しますが、シートが煩雑になります。次のセクションでは、ヘルパー列を完全に省略する方法を紹介します。

基本となるテクニックは、MATCHの中で2つの条件テストを掛け合わせることです。

(A2:A10=G1)は、1つ目の条件に対するTRUE/FALSEの配列を生成します。(B2:B10=G2)も、2つ目の条件について同じ処理を行います。これらを(A2:A10=G1)*(B2:B10=G2)のように掛け合わせると、両方がTRUEの行だけが1になり、それ以外は0になります。

その後MATCHが1を検索し、両方の条件を満たす行を見つけます。

スプレッドシートでは、TRUEは1、FALSEは0として扱われます。これらを掛け合わせると、論理演算子ANDと同じ働きになります。

• 1 × 1 = 1（両方の条件を満たす）
• 1 × 0 = 0
• 0 × 1 = 0
• 0 × 0 = 0

そのため、両方の条件を満たす行だけが1になります。それ以外の行はすべて0になります。この1つの1が、目的の行を示します。

次に、掛け合わせた配列をMATCHで囲み、完全一致する値1を検索します。

MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0)は、両方の条件がTRUEになる最初の行の位置を返します。

一致する組み合わせがデータの4行目にある場合、MATCHは4を返します。この位置を使って、INDEXが答えを取得します。

そのMATCHの結果を、実際に取得したい列、この例ではC2:C10の価格に対するINDEXへ渡します。

完全な数式は、C2:C10から、商品がG1と一致し、かつサイズがG2と一致する行の値を返す、という意味になります。

これは、ヘルパー列もデータの並べ替えも必要としない、正真正銘の複数条件検索です。

この数式は、条件の配列を評価します。最新のExcelとGoogle Sheetsでは、Enterキーを押すだけで機能します。

古いExcel（動的配列より前のバージョン）では、Ctrl+Shift+Enterを押して配列数式として確定する必要があり、数式が中括弧で囲まれます。旧式のExcelで結果が正しくなかったりエラーが表示されたりする場合は、この確定操作が抜けていることがほとんどです。

3つの条件が必要ですか。その場合は、別のテストを掛け合わせるだけです。たとえば、D列の色を入力値G3と照合するとします。

(range=criterion)の因子を追加するたびに、結果はさらに絞り込まれます。すべての条件がTRUEである行だけが積として1を維持し、どれか1つでもFALSEになると全体が0になります。

このパターンは、必要な数の列まで拡張できます。

データは、A列が商品、B列がサイズ、C列が価格です。「Shirt」の「Large」の価格を取得したいとします。

• G1 = 「Shirt」、G2 = 「Large」です。
• 条件配列では、ShirtとLargeの両方に一致する行（ここでは4行目など）だけが1になります。
• MATCH(1, ..., 0)は4を返します。
• INDEX(C2:C10, 4)は、その行の価格を返します。

どちらかの入力を変更すると、数式は正しい行をすぐに検索し直します。

次の点を覚えておいてください。

• 範囲をそろえる：すべての条件範囲とINDEXの列は、同じ高さでなければなりません。
• 一致する行がない：すべての条件を満たす行がない場合、MATCHは#N/Aを返します。全体をIFERRORで囲んでください。
• 重複：複数の行が一致する場合、MATCHは最初の行だけを返します。条件を十分に具体的にして、一意になるようにしてください。

複数の行が一致する可能性があり、そのうち1つを取得するのではなく値を合計したい場合は、SUMPRODUCTが配列数式として入力するINDEX-MATCHの代替手段になります。

SUMPRODUCTは条件配列と値の列を掛け合わせて結果を加算するため、両方の条件を満たす行だけが計算に加わります。SUMPRODUCTは配列を標準で処理できるので、Ctrl+Shift+Enterは必要ありません。

一致する値を1つ取得する場合はINDEX-MATCHを、すべての一致行を集計する場合はSUMPRODUCTを使ってください。

複数条件検索についての知識を確認しましょう。

INDEX-MATCHで複数条件の検索を行うには、次のようにします。

• 条件配列を掛け合わせます。(A=G1)*(B=G2)は、すべての条件を満たす場所でのみ1になります（論理AND）。
• MATCH(1, ..., 0)で、その行の位置を検索します。
• INDEX(returnCol, position)で値を返します。

条件を追加する場合は、*(range=criterion)の要素を追加します。範囲の高さはそろえ、従来のExcelではCtrl+Shift+Enterで確定し、IFERRORでエラーに対処してください。