Excelのデータ検索において、従来のVLOOKUPやHLOOKUP関数では対応しきれない複雑な条件が求められる場面は少なくありません。
そんな時に頼りになるのが、INDEX関数とMATCH関数の組み合わせです。
この組み合わせは、検索の柔軟性と正確性を大幅に向上させ、データ構造の変更にも強いという特徴があります。
さらに、同時にHLOOKUP関数との違いやメリット・デメリットを理解することで、目的に応じた最適な関数選択が可能になります。
本記事では、ExcelでのINDEX/MATCH関数の応用方法と、HLOOKUPとの徹底比較について、具体的な例を交えて詳しく解説します。
結論：なぜINDEX/MATCHが最適なのか？
Excelで大量のデータを管理する際、単一の関数では柔軟な検索や抽出が困難になることがよくあります。
INDEX/MATCHの組み合わせは、行や列の位置に依存せず、動的にデータを取得できるため、
データの追加・削除が頻繁に行われる環境でも、数式が崩れにくいのが大きな強みです。
また、HLOOKUP関数と異なり、横方向だけでなく縦方向の検索も自在に行えるため、
複雑なレイアウトのシートでも正確な値を返すことができます。
結果として、業務効率の向上とともに、データ分析の正確性が大幅にアップします。
INDEX/MATCH関数の基本と構文
INDEX関数は、指定した範囲内で特定の行と列の交差点にある値を返します。
一方、MATCH関数は、検索値が指定した範囲内で何番目にあるかを返します。
この2つを組み合わせることで、データの位置に左右されず、柔軟な検索が可能になります。
INDEX関数
=INDEX(配列, 行番号, [列番号])
- 配列：検索対象のセル範囲
- 行番号：返したい値が存在する行番号
- 列番号（任意）：返したい値が存在する列番号（省略可能）
MATCH関数
=MATCH(検索値, 検索範囲, [一致の種類])
- 検索値：探したい値
- 検索範囲：値を探す範囲
- 一致の種類（任意）：0（完全一致）、1（昇順）、-1（降順）
INDEX/MATCH関数の具体例
例えば、以下のようなデータ例があるとします。
「みかん」の価格を自動表示したい場合の式は以下の通りです。
=INDEX(C3:C5, MATCH(“みかん”, B3:B5, 0))
MATCH(“みかん”, B3:B5, 0)
→ 「みかん」が B3:B5 の何行目にあるかを調べます。結果：2
INDEX(C3:C5, 2)
→ C3:C5 の2行目のデータを取得します。結果：150
HLOOKUP関数の基本と具体例
HLOOKUP関数は、指定した値を基に横方向に検索し、同じ列番号の別の行にある値を返します。シンプルな横方向の検索に特化しており、表が水平に展開されている場合に非常に有効です。
例えば、次のようなデータ表を考えます。
ここで、「商品コードA002に対応する売上」を抽出する具体的な数式は次の通りです。
=HLOOKUP(“A002”, A1:D3, 3, FALSE)
この数式は、検索値 “A002” が見つかった列の、3行目の値（売上）を返します。
他LOOKUP関数との比較については以下記事を参考にどうぞ！
両者のメリット・デメリットの比較
INDEX/MATCH関数のメリット
- 柔軟性が高く、行・列の位置に左右されずに検索できる
- 列や行の追加・削除に強く、メンテナンスが容易
- 複数条件を組み合わせることで、複雑な検索ロジックを実現できる
INDEX/MATCH関数のデメリット
- 初心者には数式がやや複雑に感じられる
- 設定に時間がかかる場合がある
HLOOKUP関数のメリット
- シンプルな横方向の検索に特化しており、設定が容易
- 短い数式で簡単に値を抽出できる
HLOOKUP関数のデメリット
- 検索対象が常に最上段に存在する必要があり、柔軟性に欠ける
- データ構造が変更された場合、数式の再設定が必要になる可能性がある
下記の表は、主要な評価項目ごとに両関数を〇、△、×で評価したものです。
| 評価項目 | INDEX/MATCH | HLOOKUP |
|---|---|---|
| 柔軟性 | 〇 | △ |
| 使いやすさ | △ | 〇 |
| メンテナンス性 | 〇 | 〇 |
| 複雑な条件対応 | 〇 | × |
実践例：複雑な条件設定によるデータ抽出
実際の業務シーンでは、売上データや在庫管理など、複数の条件を満たすデータを抽出する必要があります。以下は具体的な例です。
売上データが以下のように入力されているとします。
例えば、社員の売上データから「売上が100,000円以上かつ利益率が20%以上の製品」を抽出する場合、以下の数式が利用できます。
=INDEX(B2:B4, MATCH(1, (C2:C4>=100000)*(D2:D4>=0.2), 0))
この数式は、(C2:C4>=100000) と (D2:D4>=0.2) の条件を同時に満たす行番号をMATCH関数で取得し、その行のB列（商品名）の値をINDEX関数で返します。
複数条件を柔軟に組み合わせることができるため、データが頻繁に更新される環境でも安心です。
※この数式はExcel 365以降ではそのまま「Enter」で、旧バージョンでは「Ctrl+Shift+Enter」で配列数式として入力してください。
旧バージョンで「Enter」で入力すると、#VALUEとなります。
まとめ
ExcelのINDEX/MATCH関数とHLOOKUP関数は、それぞれの特徴を理解し、適切に使い分けることで、複雑なデータ抽出や条件検索を効率化する強力なツールです。
INDEX/MATCHは柔軟性と堅牢性に優れ、データ構造の変更にも強いのが魅力です。
一方、HLOOKUPはシンプルな横方向の検索に特化しており、短い数式で手軽に利用できるため、用途に応じた選択が求められます。
これからもExcelの活用方法は進化し続けるため、最新の技術やテクニックを取り入れ、業務効率を最大限に高めていくことが重要です。
