INDEX＋MATCHで複数条件を検索するには、条件ごとの比較を掛け算でつなぎ、すべて一致した行の「1」をMATCHで探します。返すのは最初に一致した1件です。条件に合う行を全部取り出す場合はFILTERを使います。
無料Excelサンプルで一緒に試す
日付・商品・地域・売上の例題入りです。G2の日付とG3の商品を変更し、検索結果を確認します。3条件の式は本文を参考に追加できます。
Microsoft 365で利用できます。Excel 2019・2016向けの操作も本文で説明しています。
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
INDEX関数とMATCH関数の基本（1条件の検索）
INDEX＋MATCHの組み合わせは、2つの関数の役割を知ると読みやすくなります。MATCHは「探した値が範囲の何番目か」を返し、INDEXは「範囲の何番目の値か」を返します。
| 関数 | 役割 | 書式 | サンプルでの例 |
|---|---|---|---|
| MATCH | 探した値が、範囲の何番目にあるか（位置）を返す | MATCH(検索値, 検索範囲, 0) | =MATCH("C商品",$B$2:$B$10,0) → 3 |
| INDEX | 範囲の、何番目の値かを返す | INDEX(範囲, 位置) | =INDEX($D$2:$D$10,3) → 800 |
2つを組み合わせて、MATCHで見つけた位置をINDEXに渡すと、=INDEX($D$2:$D$10,MATCH("C商品",$B$2:$B$10,0))（800）のように値を取り出せます。ただし、条件が商品だけだと、最初に見つかった「C商品」（4/1）しか選べません。日付も合わせて指定するのが、次から説明する複数条件の検索です。
2条件で検索する基本式
サンプルはA2:A10が日付、B2:B10が商品、C2:C10が地域、D2:D10が売上です。G2に2024/4/1、G3にC商品を入力します。G2は文字列ではなくExcelの日付として入力してください。
掛け算で条件をつなぐ理由
TRUEとFALSEを掛け算すると1と0として計算されます。日付と商品の両方が一致する行だけ1になるため、MATCHで1を探せます。
| 日付一致 | 商品一致 | 積 |
|---|---|---|
| TRUE | TRUE | 1 |
| TRUE | FALSE | 0 |
| FALSE | TRUE | 0 |
3つ以上の条件に増やす方法（3条件の数式）
G4に地域を入力し、比較を1つ追加します。C商品・福岡・2024/4/1なら結果は800です。
見つからないときだけ表示を変える
商品名の一部で検索する（部分一致を組み合わせる）
商品名を「C」のように一部だけで探したいときは、商品の比較を ISNUMBER(SEARCH()) に置き換えます。日付との掛け算はそのままです。
XLOOKUPで部分一致を行う方法は、XLOOKUPの部分一致｜ワイルドカードで前方・後方・含む検索で解説しています。
＆で連結して検索する場合の注意（補助列とVLOOKUP）
結果が合わない・エラーになるときの確認順
- 日付が数値、商品名が同じ文字列になっているか確認する。
- 重複する条件がないか確認する。全件抽出と1件検索を分ける。
- A:Aのような列全体の配列計算を避け、必要な行までに絞る。
INDEX＋MATCHの複数条件でよくある質問
条件が3つ以上でも使えますか？
使えます。比較を * で1つ足すごとに条件が増えます。検索範囲と戻り範囲は、すべて同じ行の範囲にそろえてください。詳しくは「3つ以上の条件に増やす方法」をご覧ください。
一致する行が複数あるときは、どうなりますか？
先頭の1件だけが返ります。合計はされません。条件に合う行をすべて取り出したいときは、FILTER関数による複数条件の抽出を使います。Excel 2019・2016ではINDEX＋SMALLで複数一致を順に抽出する方法があります。
VLOOKUPでは複数条件を扱えませんか？
補助列で検索キーを作れば、VLOOKUPでも複数条件の検索ができます。ただし、連結する文字には、元データに含まれない区切り文字を使ってください。
Excel 2019・2016でも使えますか？
使えます。配列計算が必要な場合は、Enterではなく Ctrl＋Shift＋Enter で確定します。
日付を条件にするときの注意はありますか？
検索値のセル（G2）は、文字列ではなく、Excelの日付として入力してください。見た目が同じでも、文字列のままだと一致せず #N/A になります。
無料Excelサンプルで復習する
日付・商品・地域・売上の例題入りです。G2の日付とG3の商品を変更し、検索結果を確認します。3条件の式は本文を参考に追加できます。
Microsoft 365で利用できます。Excel 2019・2016向けの操作も本文で説明しています。
コメント