VLOOKUPは非常に便利な検索関数ですが、「検索する列より左側の値を取り出せない」「表の途中に列を挿入すると数式が壊れる」という2つの弱点があります。この弱点を克服できるのがINDEX関数とMATCH関数を組み合わせた検索方法です。少し複雑に見えますが、仕組みを理解すれば実務で強力な武器になります。
VLOOKUPの弱点とINDEX+MATCHが解決すること
VLOOKUPは検索値を表の一番左の列で探し、指定した列数だけ右にずれた値を返す仕組みです。そのため「社員名から社員番号（左の列）を取得する」といった、検索列より左にある情報を取り出す操作ができません。また、列番号を指定する仕様上、表の途中に新しい列を挿入すると列番号がずれ、意図しない列の値が返ってきてしまいます。INDEX+MATCHはこの2つの弱点をどちらも克服できます。
基本の書き方
INDEXは「範囲の中で、指定した行番号・列番号にある値」を返す関数、MATCHは「検索値が範囲の中で何番目にあるか」を返す関数です。この2つを組み合わせ、MATCHで探し出した順番をINDEXの行番号として使います。
例：社員名簿でB列の社員名からA列の社員番号を取得する（社員名が検索列より左にある社員番号を取得）
=INDEX(A:A, MATCH(D1, B:B, 0))
MATCHの3つ目の引数「照合の種類」には基本的に0（完全一致）を指定します。INDEXの範囲とMATCHの検索範囲は、必ず同じ行数で対応させることがポイントです。範囲の行数がずれていると、意図した行とは違う値が返ってきます。
VLOOKUP
- 検索列は表の一番左固定
- 列挿入で列番号がずれる
- 数式はシンプル
INDEX+MATCH
- 検索列はどこでもよい
- 列挿入に強い
- 数式はやや複雑
XLOOKUP
- 検索列はどこでもよい
- 列挿入に強い
- 新しいExcelのみ対応
実務でのよくある使用シーン
人事・総務担当者であれば、社員名や部署名から検索列より左にある社員番号や入社日を逆引きする場面でINDEX+MATCHが役立ちます。経理担当者であれば、勘定科目マスタの途中に新しい科目列を追加する可能性がある表で、列挿入に強いINDEX+MATCHを採用しておくと事故を防げます。営業事務担当者であれば、商品コード表が頻繁に更新され列の並びが変わる場合に、列番号に依存しないINDEX+MATCHの方が数式のメンテナンス性に優れます。特に「他の担当者も編集する共有ファイル」では、列挿入によるVLOOKUPの事故を防ぐ意味でもINDEX+MATCHへの切り替えを検討する価値があります。
INDEX+MATCHでよくあるエラー・トラブルと対処法
MATCH関数が検索値を検索範囲内で見つけられない場合に発生します。
- 検索値と検索範囲の値の表記が完全一致していない（全角・半角、余分な空白など）
- 検索範囲の指定がずれている
対処法として、検索値と検索範囲双方の表記を統一することが第一です。データの出どころが異なる場合はTRIMやASC関数などで表記を揃えてから照合しましょう。
INDEXの範囲指定に問題があるか、参照しているセルが削除された場合に発生します。
この場合はもとの列を復元するか、INDEXの範囲を再設定する必要があります。VLOOKUPと違い、INDEX+MATCHは列を挿入してもズレませんが、参照している列自体を削除すればさすがにエラーになる点は変わりません。
INDEXの範囲とMATCHの検索範囲の開始行がずれているケースでよく起こります。
INDEXの範囲とMATCHの検索範囲は、必ず同じ開始行・終了行で揃える必要があります。片方だけ見出し行を含めてしまうなどのズレがあると、1行分ずれた値が返ってきます。
初心者がつまずきやすいポイントとコツ
- MATCHの結果は「値」ではなく「順番（行番号）」。MATCH単体の結果を見て「思っていた値と違う」と驚くことがありますが、MATCHが返すのはあくまで検索範囲内の順番です。
- 照合の種類は必ず0を指定する。省略すると近似一致になり、意図しない値がヒットする原因になります。
- 列全体(A:A)で指定すると範囲がずれる心配が減る。行数が変動する表では、A2:A100のように行を固定するよりA:Aと列全体で指定する方が安全な場合があります。
- 複雑に感じたら2段階に分けて確認する。いきなりINDEX+MATCHを1つの数式で書かず、まずMATCH部分だけを別セルで試して正しい行番号が出ているか確認すると、原因の切り分けがしやすくなります。
まとめ
INDEX+MATCHは、VLOOKUPの「検索列より左が取れない」「列挿入に弱い」という弱点を克服できる、実務で信頼性の高い検索方法です。最初は数式が長く感じますが、MATCHが「順番」を、INDEXが「その順番にある値」を返すという役割分担さえ理解すれば、応用が利く強力な武器になります。共有ファイルなど列構成が変わりやすい表では、積極的にINDEX+MATCHへの切り替えを検討しましょう。数式を一から組み立てるのが不安な方は、下のツールで日本語の空欄を埋めるだけで数式を作成できます。