スプレッドシートのVLOOKUP・XLOOKUP・INDEX/MATCH 使い分けガイド｜どの検索関数を選ぶか

スポンサーリンク

Googleスプレッドシートで「別の表から値を取り出したい」場面、よくありますよね。

VLOOKUPは定番の検索関数ですが、使っていると「左側の列が返せない」「列を挿入したら番号がズレた」という壁にぶつかります。そこでXLOOKUPやINDEX/MATCHが候補に挙がるのですが、「3つのうちどれを使えばいいのか」で迷う方も多いのではないでしょうか。

この記事では、スプレッドシートの3関数を「検索方向」「複数列返却」「Excel互換性」の3軸で比較しました。どれを選ぶべきかを3ステップで判断できるフローも用意しています。

さらに、VLOOKUPからXLOOKUPへの書き換え手順と#N/Aエラーの対処法も解説します。読み終わるころには「自分のケースはこれ」と迷いなく選べるようになりますよ。

VLOOKUP・XLOOKUP・INDEX/MATCHの違い【結論から】

各関数の特徴まとめ

Googleスプレッドシートの3つの検索関数には、それぞれ得意・不得意があります。

VLOOKUP は「範囲の先頭列を検索して右側の列を返す」関数です。構文がシンプルで、Excelとも互換性が高いのが強みです。ただし、検索キーは必ず先頭列（左端列）に置く必要があり、左方向の検索はできません。

XLOOKUP は2022年8月にGoogleスプレッドシートで使えるようになった、比較的新しい関数です。VLOOKUPの制限を解消し、左右どちらの方向にも検索できます。第4引数（missing_value）に値を指定すれば、IFERROR（エラー発生時に代替値を返す関数）なしで#N/A対策ができます。

INDEX/MATCH は2つの関数を組み合わせた検索パターンです。INDEX関数（指定した行・列の値を返す関数）とMATCH関数（値の相対位置を返す関数）を組み合わせて使います。検索列と返す列を独立して指定できるため、柔軟性が最も高い方法です。

どれが「上位互換」か

「最新のXLOOKUPを使えばいい」と思いがちですが、一概にはいえません。

Excelとのファイル共有が必要な場合は、VLOOKUPかINDEX/MATCHのほうが安全です。XLOOKUPはExcel 2021またはMicrosoft 365以降でしか動かないからです。また、行と列の両方でキーを指定する2次元検索を1つの数式で完結させたい場合は、INDEX/MATCHが最も扱いやすい選択肢になります。

使い分けの基準を次の比較表でまとめました。ぜひ参考にしてみてください。

3関数の機能比較表（検索方向・複数列返却・Excel互換性）

機能VLOOKUPXLOOKUPINDEX/MATCH
左側の列を検索できるか不可可可
複数列を一度に返せるか不可可可（要工夫）
Excel 2016/2019との互換性対応非対応（#NAME?エラー）対応
#N/Aエラー処理の簡潔さIFERROR/IFNAが必要第4引数で直接指定可IFERROR/IFNAが必要
2次元検索（行・列キー指定）不可単独では不可可
数式の複雑さ低（単関数）低（単関数）高（2関数の組み合わせ）

検索方向の違い（左側も検索できるか）

VLOOKUPで最も多く遭遇する制限が「左方向検索ができない」問題です。

たとえば、A列に商品コード・B列に商品名・C列に単価という表があるとします。「商品名から商品コードを調べたい」場合、VLOOKUPでは対応できません。返したい列（A列のコード）が、検索したい列（B列の商品名）より左にあるからです。

XLOOKUPとINDEX/MATCHはこの制限がなく、どの方向にも検索できますよ。

複数列を一度に返せるか

XLOOKUPはresult_range（返す範囲）に複数列を指定すれば、該当行の複数値を一度に返せます。VLOOKUPは1数式で1列しか返せないため、列の数だけ数式を書く必要があります。

Excelファイルで開いたときの互換性

XLOOKUPはExcel 2021またはMicrosoft 365以降でのみ動作します。Excel 2016・2019（永続ライセンス版）は非対応です。

スプレッドシートで作成したXLOOKUP数式を古いExcelで開くと、#NAME?エラーが表示されます。#NAME?は「関数名を認識できない」ときに出るエラーです。

Excelファイルを社外に共有する機会がある場合や、古いバージョンを使う方と協働する場合は、VLOOKUPかINDEX/MATCHを選ぶほうが安心です。

どの関数を使うべきか？3ステップ選択フロー

3つの質問に答えるだけで、使うべき関数を決められます。

STEP1：左側の列を検索する必要があるか

「検索キーより左にある列の値を返したい」場合は、VLOOKUPは使えません。XLOOKUPかINDEX/MATCHに進んでください。

検索キーが左端列にあり、返す値は右側にある場合は、VLOOKUPも選択肢になります。STEP2へ進みましょう。

STEP2：Excelで開くことがあるか

スプレッドシートだけで完結するなら、XLOOKUPがおすすめです。数式が短く、エラー処理も簡単です。

Excel 2016・2019などの古いバージョンでファイルを開く可能性があるなら、VLOOKUPかINDEX/MATCHを選んでください。

STEP3：2次元検索が必要か

行と列の両方でキーを指定したい場合（例：「4月×東京支店の売上」を取り出す）は、INDEX/MATCHが最適です。

STEP1・2でVLOOKUPやXLOOKUPを選んだ場合でも、2次元検索が必要な場面ではINDEX/MATCHを使いましょう。

3ステップをまとめると次のようになります。

  • 左方向検索が不要 かつ Excelファイル共有が不要 → XLOOKUP
  • 左方向検索が不要 かつ Excelファイル共有が必要 → VLOOKUP
  • 左方向検索が必要 または 2次元検索が必要 → INDEX/MATCH

VLOOKUPからXLOOKUPへの書き換え手順

XLOOKUPは、スプレッドシート専用でExcel共有が不要なら、VLOOKUPよりシンプルに書けます。既存のVLOOKUP数式をXLOOKUPへ書き換える手順を見ていきましょう。

基本的な書き換えパターン（ビフォーアフター）

次のサンプルデータを使って説明します。

A列（商品コード）B列（商品名）C列（単価）
A001りんご120
A002バナナ80
A003みかん150

F2セルに入力した商品コードに対応する商品名を取り出す場合のビフォーアフターです。

VLOOKUP（ビフォー）

=VLOOKUP(F2, A:C, 2, FALSE)

A:Cの先頭列（A列）でF2を検索し、2列目（B列）の値を返します。「2」がB列を指しています。F2が「A002」なら「バナナ」が返ります。

XLOOKUP（アフター）

=XLOOKUP(F2, A:A, B:B)

A:AでF2を検索し、B:Bの値を返します。「何列目」ではなく、列を直接指定しています。F2が「A002」のとき、どちらの数式も「バナナ」を返します。

列追加・削除があっても崩れない書き方

VLOOKUPの「2」「3」という列番号指定は、列を追加・削除したときに崩れるリスクがあります。XLOOKUPは列を直接参照するため、列の追加・削除があっても返す列がズレません。

たとえば、A列とB列の間に「商品略称」列を挿入するとします。VLOOKUPの「2」はそのままでは1つズレ、意図しない列の値を返してしまいます。XLOOKUPの「B:B」は列の参照が自動で更新されるため、ズレが起きません。

この差がメンテナンスのしやすさに大きく影響しますよ。

#N/Aエラーの対処法（IFERROR方式 vs XLOOKUP第4引数方式）

検索関数でよく遭遇するのが#N/Aエラーです。#N/Aは「一致する値が見つからない」場合に表示されます。対処法を2種類比較します。

IFERRORをネストする方法

VLOOKUP・INDEX/MATCHを使う場合、IFERROR（全エラー種を処理する関数）またはIFNA（#N/Aのみを処理する関数）で数式全体を囲みます。

=IFERROR(VLOOKUP(F2, A:C, 2, FALSE), "該当なし")
=IFNA(VLOOKUP(F2, A:C, 2, FALSE), "該当なし")

IFERRORは#N/A以外のエラーも隠してしまいます。#REF!（参照が無効な場合）や#VALUE!（引数の型が不正な場合）も処理されるため、数式のバグを見逃すリスクがあります。

#N/Aだけを処理したい場合は、IFNAのほうが安全です。IFNAは#REF!などのエラーはそのまま表示するため、バグに気づきやすくなります。

XLOOKUPの第4引数を使う方法

XLOOKUPでは、第4引数（missing_value）に「見つからなかったときの値」を直接指定できます。

=XLOOKUP(F2, A:A, B:B, "該当なし")

IFERROR・IFNAで外側から囲む必要がなく、数式が1段短くなります。XLOOKUPの第4引数は「値が見つからない」場合のみに適用されます。#REF!などのエラーはそのまま表示されるため、デバッグもしやすいです。

どちらがシンプルか

比較項目IFERROR方式（VLOOKUP）XLOOKUP第4引数方式
数式の長さ長い（IFERRORを外側に追加）短い（第4引数に直接記述）
#N/A以外のエラーへの影響IFERRORはすべて隠すバグはエラーのまま表示
デバッグのしやすさIFERRORは注意が必要バグに気づきやすい

XLOOKUPが使える状況なら、第4引数方式がシンプルでおすすめですよ。

VLOOKUPを使う場合は、IFERRORよりIFNAを選ぶと数式のバグを見逃しにくくなります。IFERRORとIFNAの詳細な使い方は、それぞれの解説記事も参考にしてみてください。

まとめ：Sheetsでの検索関数ベストプラクティス

Googleスプレッドシートの3つの検索関数をまとめます。

  • XLOOKUP：Excelとのファイル共有が不要なら第一候補。シンプルな構文と第4引数によるエラー処理が強みです
  • VLOOKUP：Excelとの互換性を最優先する場面や、既存数式を大量に抱えているときに有効です
  • INDEX/MATCH：左方向検索・2次元検索が必要な場面での最強の組み合わせです

まず、Excelファイル共有が不要なケースではXLOOKUPを選びましょう。共有が必要になったタイミングで、VLOOKUPかINDEX/MATCHに切り替えると迷いにくいですよ。

各関数の詳細な使い方は以下の記事で解説しています。本記事と合わせてご覧ください。

ExcelでもVLOOKUP・XLOOKUP・INDEX/MATCHを比較したい方は、Excel版の使い分けガイドもあわせてご覧ください。

スポンサーリンク
タイトルとURLをコピーしました