【Excel】INDEX/MATCH関数で複数条件の検索をする!Excelの2列キーによるルックアップ技

Excelで複数の条件を指定してデータを検索したい場面に遭遇するでしょう。例えば、商品コードと色で在庫数を調べたい場合などです。しかし、VLOOKUP関数では単一の列しか指定できないため、このような検索は困難です。INDEX関数とMATCH関数を組み合わせることで、2列以上のキーを指定した高度な検索が可能になります。この記事では、INDEX/MATCH関数を使って複数条件の検索を実現する具体的な手順と、その仕組みについて解説します。

INDEX/MATCH関数は、VLOOKUP関数では対応できない複雑な検索条件に対応できる強力な組み合わせです。この組み合わせを習得すれば、Excelでのデータ分析能力が格段に向上するでしょう。

INDEX/MATCH関数による複数条件検索の仕組み

INDEX関数とMATCH関数を組み合わせることで、Excelのデータの中から特定の条件に合致する値を見つけ出すことができます。VLOOKUP関数が検索対象の列を1つしか指定できないのに対し、この組み合わせは複数の列を検索条件として利用できる点が最大の特徴です。これにより、より正確で複雑なデータ検索が可能になります。

MATCH関数は、指定した範囲内で特定の項目が何番目にあるかを行番号または列番号で返します。INDEX関数は、指定した範囲の中から、MATCH関数で得られた行番号と列番号に対応する値を返します。この2つの関数を連携させることで、複数列を検索条件として、目的のデータを抽出する仕組みです。

INDEX/MATCH関数で複数条件の検索を行う手順

1. 検索したい値と検索条件を整理する
   まず、どのようなデータを検索したいのか、そしてその検索にどのような条件が必要なのかを明確にします。例えば、商品リストから「商品名」と「色」が一致する「単価」を検索したい場合、検索したい値は「単価」であり、検索条件は「商品名」と「色」になります。
2. MATCH関数で検索条件の行番号を特定する
   MATCH関数を使い、指定した複数の条件がすべて一致する行の番号を特定します。複数条件の検索には、MATCH関数を直接複数使うのではなく、条件を組み合わせた仮想的な列を作成し、その列内で検索する方法が一般的です。具体的には、条件となる各列の値を「&」で連結した文字列を検索値とし、データ範囲の対応する連結文字列の範囲でMATCH関数を実行します。
3. INDEX関数で目的の値を抽出する
   INDEX関数は、指定した範囲の中から、MATCH関数で特定された行番号に対応する値を返します。検索したい値が含まれる列全体をINDEX関数の範囲として指定し、MATCH関数で得られた行番号を適用することで、目的の値を抽出します。

具体的な数式例

例えば、A列に商品名、B列に色、C列に単価が入力されているシートで、商品名「りんご」かつ色「赤」の単価を検索する場合の数式は以下のようになります。

検索したい値(単価)を抽出する数式:

=INDEX(C:C,MATCH(Sheet1!$E$1&Sheet1!$F$1,A:A&B:B,0))

この数式では、E1セルに「りんご」、F1セルに「赤」と入力されていると仮定しています。

数式の解説:

• C:C: 単価が入力されている列全体を指定します。ここにINDEX関数の返り値として単価が返されます。
• MATCH(Sheet1!$E$1&Sheet1!$F$1,A:A&B:B,0): ここが複数条件検索の核となる部分です。
• Sheet1!$E$1&Sheet1!$F$1: 検索したい条件(E1セルの商品名とF1セルの色)を「&」で連結しています。この例では「りんご赤」という文字列が生成されます。
• A:A&B:B: データ範囲のA列(商品名)とB列(色)をそれぞれ連結した仮想的な列を作成します。MATCH関数はこの仮想的な列の中から「りんご赤」を検索します。
• 0: 完全一致を指定します。

この数式は配列数式として入力する必要があるため、数式バーで入力後にCtrl+Shift+Enterキーを押して確定してください。Excel 2019以降では、動的配列機能によりCtrl+Shift+Enterなしで入力できる場合もあります。

INDEX/MATCH関数で複数条件検索を行う際の注意点と失敗例

条件を連結する際の注意点

MATCH関数で複数条件を連結して検索する場合、連結する際に使用する区切り文字には注意が必要です。上記の例では「&」を使用しましたが、検索対象のデータに「&」が含まれている場合、意図しない結果になる可能性があります。例えば、商品名が「りんご&ふじ」で、検索値が「りんご」だけだった場合、一致しないはずなのに一致と判定されてしまうリスクがあります。

このような問題を避けるためには、データに存在しない、または使用頻度の低い文字を区切り文字として使用することが推奨されます。例えば、「|」や「@@」などの記号が考えられます。

大文字・小文字、全角・半角の区別

MATCH関数は、デフォルトでは大文字・小文字、全角・半角を区別して検索します。もし、これらの区別をせずに検索したい場合は、EXACT関数やUPPER関数、LOWER関数などを組み合わせて、検索値と検索対象のデータを統一する必要があります。しかし、INDEX/MATCH関数とこれらの関数を組み合わせると数式が複雑になりがちです。

そのような場合、あらかじめデータソース側で表記揺れを統一しておくか、Power Queryなどのデータ整形ツールを利用する方が効率的です。

配列数式としての入力

前述の通り、MATCH関数で複数列を連結して検索する数式は、配列数式として入力する必要がある場合があります。Excelのバージョンによっては、Ctrl+Shift+Enterで確定しないと正しく計算されません。この操作を忘れると、期待通りの結果が得られず、「#VALUE!」エラーや意図しない値が表示される原因となります。

Excel 2021やMicrosoft 365のバージョンでは、動的配列機能により、通常のEnterキーで配列数式が自動的に展開されるため、この手間は省かれます。しかし、古いバージョンを使用している場合は、配列数式としての入力方法を必ず確認してください。

検索対象範囲の指定方法

INDEX関数とMATCH関数の検索範囲を指定する際、列全体(例: A:A)を指定する方法と、特定の範囲(例: A1:A100)を指定する方法があります。列全体を指定すると、後からデータが増えても自動的に範囲に含まれるため便利です。

しかし、データ量が膨大な場合や、非表示の行・列が多く存在する場合には、処理速度が低下する可能性があります。また、意図しないデータまで検索対象に含まれてしまうリスクも考慮する必要があります。そのため、データ量やシートの構成に応じて、適切な範囲指定を検討することが重要です。

INDEX/MATCH関数とVLOOKUP関数の比較

項目	INDEX/MATCH関数	VLOOKUP関数
検索列の指定	複数列を指定可能	単一列のみ指定可能
検索値の列の位置	検索値の列が検索対象列の左側にあっても良い	検索値の列は、検索対象列の左側にある必要がある
数式の複雑さ	やや複雑	比較的シンプル
処理速度	一般的にVLOOKUPより高速	データ量が多いと遅くなる傾向がある
配列数式	複数条件検索で配列数式になる場合がある	通常は配列数式にならない

INDEX/MATCH関数は、VLOOKUP関数では実現できない柔軟な検索が可能です。特に、検索したい値が検索条件の左側にある場合や、複数条件で検索したい場合に強みを発揮します。一方で、数式が複雑になりがちなため、VLOOKUP関数で対応できる場合はそちらの方が簡潔に記述できます。

INDEX/MATCH関数と他の関数を組み合わせる応用

IFERROR関数との組み合わせ

INDEX/MATCH関数で検索した結果、条件に合致するデータが見つからなかった場合、通常は「#N/A」エラーが表示されます。このエラー表示を回避し、代わりに「該当なし」などのメッセージを表示したい場合は、IFERROR関数と組み合わせます。

数式は以下のようになります。

=IFERROR(INDEX(C:C,MATCH(Sheet1!$E$1&Sheet1!$F$1,A:A&B:B,0)),”該当なし”)

これにより、エラーが発生した場合に「該当なし」と表示され、より見やすい結果が得られます。

SUMIFS関数やCOUNTIFS関数との連携

複数条件で検索するだけでなく、複数条件に合致する値の合計を求めたい場合はSUMIFS関数、件数を求めたい場合はCOUNTIFS関数が適しています。これらの関数は、INDEX/MATCH関数のように検索結果を返すのではなく、直接条件に合致するデータの集計を行います。

例えば、商品名と色で単価の合計を求めたい場合はSUMIFS関数を使用します。INDEX/MATCH関数は特定の1つの値を検索するのに長けていますが、集計処理にはSUMIFS関数などがより適しています。状況に応じてこれらの関数を使い分けることが重要です。

INDEX/MATCH関数は、Excelでのデータ検索能力を大きく拡張する強力なテクニックです。特に、VLOOKUP関数では対応できない複雑な検索条件や、検索対象列の配置に制約がある場合に有効です。今回解説した手順と注意点を理解し、実際の業務で活用することで、データ分析の効率と精度を向上させることができるでしょう。

まずは、簡単なデータセットでINDEX/MATCH関数の複数条件検索を試してみてください。慣れてきたら、IFERROR関数と組み合わせたり、より複雑な条件設定に挑戦したりすることで、さらに高度なデータ操作が可能になります。Excelの機能を深く理解し、日々の業務に活かしていきましょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】