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

スポンサーリンク

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

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

この記事では、スプレッドシートの3つの検索関数を「検索方向」「複数列返却」「Excel互換性」など7項目で徹底比較します。3つの質問に答えるだけで使うべき関数が決まる選択フロー、VLOOKUPからXLOOKUPへの書き換え手順（4パターン）、#N/Aだけでなく#REF!や#VALUE!も含めたエラー対処、そして実務シーン別のおすすめパターン集まで一気通貫で解説します。

読み終わるころには「自分のケースはこれ」と迷いなく選べるようになりますよ。

  1. 3つの検索関数の違いを30秒で理解する【結論】
    1. 各関数の役割を一文で言うと
    2. 「最新のXLOOKUPだけ使えばいい」が成立しない3つの理由
  2. VLOOKUP・XLOOKUP・INDEX/MATCH 機能比較表（7項目）
    1. 検索方向の違い（左方向検索ができるか）
    2. 複数列を一度に返せるか（スピル機能）
    3. Excelファイルで開いたときの互換性
    4. 列挿入時のズレ耐性
  3. 3ステップ選択フロー｜どの検索関数を使うか3つの質問で決める
    1. STEP1：左側の列を検索する必要があるか
    2. STEP2：Excelで開くことがあるか
    3. STEP3：2次元検索が必要か
    4. 3ステップを1枚にまとめると
  4. VLOOKUPからXLOOKUPへの書き換え手順【4パターン】
    1. パターン1：基本の書き換え（1列を返す）
    2. パターン2：複数列を一度に返す（スピル活用）
    3. パターン3：エラー処理を組み込む
    4. パターン4：ワイルドカード検索
  5. INDEX/MATCHが真価を発揮する3つの場面
    1. 場面1：左方向検索（部署名から社員IDを引く）
    2. 場面2：2次元検索（行×列キーで値を特定）
    3. 場面3：Excel互換性が必要な複雑な検索
  6. 検索関数で頻出する#N/A・#REF!・#VALUE!エラーの対処法
    1. #N/Aエラー（一致する値が見つからない）
    2. #REF!エラー（参照範囲が無効）
    3. #VALUE!エラー（引数の型が不正）
  7. ケース別おすすめパターン集（実務シーン6選）
    1. シーン1：商品マスタから単価を引いてくる（請求書作成など）
    2. シーン2：社員名簿から氏名・部署を一度に取り出す（人事系資料）
    3. シーン3：部署名から代表者の社員IDを逆引きする
    4. シーン4：月×支店のクロス集計から値を取り出す
    5. シーン5：取引先名を「あいまい検索」で引く
    6. シーン6：Excel 2016と共有しつつ左方向検索が必要
  8. まとめ｜スプレッドシートでの検索関数ベストプラクティス

3つの検索関数の違いを30秒で理解する【結論】

まずは結論から押さえましょう。VLOOKUP・XLOOKUP・INDEX/MATCHの3つは、それぞれ得意な場面が違います。

各関数の役割を一文で言うと

3つを乱暴に一言でまとめると、次のようになります。

  • VLOOKUP: 表の先頭列で検索して、右側の列の値を返す。シンプルだけど制限が多い「定番」関数
  • XLOOKUP: VLOOKUPの制限を解消した後継。左右どちらにも検索でき、エラー処理も内蔵されている「最新型」
  • INDEX/MATCH: 2つの関数を組み合わせて使う「上級者向け万能ナイフ」。2次元検索や左方向検索に強い

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

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

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

「最新のXLOOKUPだけ使えばいい」が成立しない3つの理由

「いちばん新しいXLOOKUPを使えば全部解決では？」と思いがちですが、実務ではそう単純ではありません。理由は3つあります。

  1. Excel互換性の壁: Excel 2016/2019で開くと#NAME?エラーになる
  2. 2次元検索の壁: 行キー×列キーの検索はINDEX/MATCHのほうがシンプル
  3. 既存資産の壁: VLOOKUPで組まれた数百本の数式を一気に書き換えるのは現実的でない

Excelとのファイル共有が必要な場面では、VLOOKUPかINDEX/MATCHのほうが安全です。XLOOKUPはExcel 2021またはMicrosoft 365以降でしか動かないからです。

行と列の両方でキーを指定する2次元検索を1つの数式で完結させたい場合は、INDEX/MATCHが最も扱いやすい選択肢になります。XLOOKUPでも2次元検索はできますが、XLOOKUPを入れ子にする書き方になり、かえって読みにくくなります。

また、すでにVLOOKUPで構築された既存テンプレートが社内に多く存在する場合、それを全部XLOOKUPへ置き換える工数とリスクを考えると、新規分だけXLOOKUPを採用する方が現実的なこともあります。

つまり「3つを場面で使い分ける」のが最も実務的なんです。次の章で詳しく比較していきましょう。

VLOOKUP・XLOOKUP・INDEX/MATCH 機能比較表（7項目）

3関数の違いを7つの観点で表にまとめました。手元のケースと照らし合わせて、どれが当てはまるか見てみてください。

機能VLOOKUPXLOOKUPINDEX/MATCH
左側の列を検索できるか不可可可
複数列を一度に返せるか不可可（スピル）可（要工夫）
Excel 2016/2019との互換性対応非対応（#NAME?エラー）対応
#N/Aエラー処理の簡潔さIFERROR/IFNAが必要第4引数で直接指定可IFERROR/IFNAが必要
2次元検索（行・列キー指定）不可可（入れ子で対応）可（最も書きやすい）
列挿入時のズレ耐性弱い（列番号が手動）強い（列を直接参照）強い（列を直接参照）
数式の複雑さ低（単関数）低（単関数）中〜高（2関数の組み合わせ）

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

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

たとえば、A列に社員ID、B列に氏名、C列に部署名という名簿があるとします。「部署名から社員IDを調べたい」場合、VLOOKUPでは対応できません。返したい列（A列の社員ID）が、検索したい列（C列の部署名）より左にあるからです。

XLOOKUPとINDEX/MATCHはこの制限がなく、どの方向にも検索できます。具体的な書き方は後の章で見ていきましょう。

複数列を一度に返せるか（スピル機能）

XLOOKUPはresult_range（返す範囲）に複数列を指定すれば、該当行の複数値を一度に返せます。スピル機能（1つの数式で複数セルに値が広がる機能）に対応しているからです。

=XLOOKUP(F2, A2:A100, B2:D100)

この数式1本で、商品名・単価・在庫の3列を一度に取り出せます。VLOOKUPは1数式で1列しか返せないため、3列欲しければ3本の数式を書く必要があります。

INDEX/MATCHでも複数列を返せますが、MATCHを別セルに切り出して参照する形にするのが一般的です。

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

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

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

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

列挿入時のズレ耐性

VLOOKUPの第3引数「2」「3」という列番号指定は、列を追加・削除したときに崩れるリスクがあります。

たとえば次の数式を考えてみてください。

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

A:Cの範囲でF2を検索し、2列目（B列）を返す数式です。ここでA列とB列の間に「商品略称」列を挿入すると、もともとのB列はC列にズレます。しかしVLOOKUPの「2」はそのままなので、新しい「商品略称」列を返してしまいます。

XLOOKUPとINDEX/MATCHは列を直接参照する（B:Bのように書く）ため、列の挿入があっても参照が自動更新され、ズレません。この差はメンテナンス性に大きく影響します。

3ステップ選択フロー｜どの検索関数を使うか3つの質問で決める

「比較表は見たけど、結局自分はどれを使えばいいの？」という方のために、3つの質問に答えるだけで決まる選択フローを用意しました。

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

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

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

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

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

Excel 2016・2019などの古いバージョンでファイルを開く可能性があるなら、VLOOKUPかINDEX/MATCHを選んでください。Excel 2021 / Microsoft 365のみなら、XLOOKUPでも問題ありません。

STEP3：2次元検索が必要か

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

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

3ステップを1枚にまとめると

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

条件の組み合わせおすすめ関数
左方向検索が不要 & Excel共有が不要XLOOKUP
左方向検索が不要 & Excel共有が必要（古いExcel含む）VLOOKUP
左方向検索が必要INDEX/MATCH（共有不要ならXLOOKUPも可）
2次元検索が必要（行×列キー）INDEX/MATCH
Excel 2021 / Microsoft 365のみで共有XLOOKUP（Excelでも動作可）

迷ったら「Excelとの共有があるかどうか」を最初に確認するのがコツです。社内資料や社外提出が絡む場面では互換性が最優先になりますよ。

VLOOKUPからXLOOKUPへの書き換え手順【4パターン】

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

次のサンプルデータを共通で使います。F2セルに商品コードを入力する想定です。

A列（商品コード）B列（商品名）C列（単価）D列（在庫数）
A001りんご12050
A002バナナ8080
A003みかん15030

パターン1：基本の書き換え（1列を返す）

最もシンプルな書き換えパターンです。

VLOOKUP（ビフォー）

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

A:Cの先頭列（A列）でF2を検索し、2列目（B列）の値を返します。

XLOOKUP（アフター）

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

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

VLOOKUPの「2」が、XLOOKUPでは「B:B」という列の直接参照に置き換わっていますね。

パターン2：複数列を一度に返す（スピル活用）

商品名・単価・在庫を1数式で取り出したい場合です。

VLOOKUP（3本必要）

=VLOOKUP(F2, A:D, 2, FALSE)  // 商品名
=VLOOKUP(F2, A:D, 3, FALSE)  // 単価
=VLOOKUP(F2, A:D, 4, FALSE)  // 在庫

XLOOKUP（1本でOK）

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

B:Dという複数列の範囲を指定するだけで、スピル機能により隣の2セルにも値が自動で広がります。数式の本数が3本→1本になり、メンテナンスもラクになります。

パターン3：エラー処理を組み込む

「見つからなかったときに『該当なし』と表示したい」場合の書き換えです。

VLOOKUP + IFERROR

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

XLOOKUP（第4引数）

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

XLOOKUPは第4引数（missing_value）にエラー時の値を直接書けるため、IFERRORで外側を囲む必要がありません。数式が1段短くなりますよ。

パターン4：ワイルドカード検索

「商品コードの一部だけ覚えている」場合などに使うワイルドカード（*や?）検索の書き換えです。

VLOOKUP（完全一致モードでワイルドカード可）

=VLOOKUP("A00*", A:C, 2, FALSE)

VLOOKUPでは第4引数を FALSE（完全一致）にした状態であれば、*（任意の文字列）や ?（任意の1文字）が使えます。

XLOOKUP（一致モードに2を指定）

=XLOOKUP("A00*", A:A, B:B, "該当なし", 2)

XLOOKUPでは第5引数（一致モード）に「2」を指定すると、ワイルドカードが有効になります。指定しないと完全一致モード（既定値0）で動くため、*や?が文字列として扱われてしまいます。

書き換え時にこの引数を忘れがちなので注意してくださいね。

INDEX/MATCHが真価を発揮する3つの場面

「INDEX/MATCHは古い・複雑そう」と敬遠されがちですが、3つの場面では今でも最強の選択肢です。

場面1：左方向検索（部署名から社員IDを引く）

A列に社員ID、B列に氏名、C列に部署という名簿で、「営業部の社員IDを取り出したい」場合を考えます。

A列（社員ID）B列（氏名）C列（部署）
E001山田太郎営業部
E002鈴木花子経理部
E003佐藤次郎総務部

INDEX/MATCH

=INDEX(A:A, MATCH("営業部", C:C, 0))

MATCHで「営業部」がC列の何行目にあるかを調べ、その行のA列の値をINDEXで取り出します。MATCHの第3引数「0」は完全一致を意味します。

XLOOKUPでも同じことができますが、INDEX/MATCHは「検索列と返す列を完全に独立して指定する」発想なので、表構造の柔軟性ではINDEX/MATCHのほうが一枚上手です。

場面2：2次元検索（行×列キーで値を特定）

月別×支店別の売上クロス集計表から、「5月の大阪支店の売上」を取り出す場合です。

 4月5月6月
東京500600550
大阪400450480
名古屋300350320

シート上のA1:D4にこの表があるとします。F2に「大阪」、G2に「5月」が入力されている前提です。

INDEX/MATCH/MATCH

=INDEX(B2:D4, MATCH(F2, A2:A4, 0), MATCH(G2, B1:D1, 0))
  • 1つ目のMATCHで「大阪」が行のどこにあるか（2行目）を調べる
  • 2つ目のMATCHで「5月」が列のどこにあるか（2列目）を調べる
  • INDEXで「2行目・2列目」の値である450を返す

XLOOKUPで書くと（入れ子になる）

=XLOOKUP(F2, A2:A4, XLOOKUP(G2, B1:D1, B2:D4))

XLOOKUPでも書けますが、内側のXLOOKUPが配列を返し、それを外側のXLOOKUPが処理する構造で、慣れていないと読みにくいです。INDEX/MATCH/MATCHのほうが「行を決める→列を決める→値を取る」という発想がそのまま数式に表れていて、可読性が高いです。

場面3：Excel互換性が必要な複雑な検索

「Excel 2016を使う取引先と共有するが、左方向検索も必要」という板挟みの状況では、INDEX/MATCH一択になります。XLOOKUPはExcel 2016では#NAME?エラー、VLOOKUPは左方向検索ができないため、両方の制約を満たせるのはINDEX/MATCHだけです。

特に経理・人事系のテンプレートはExcelで作られていることが多いので、その業務領域ではINDEX/MATCHを覚えておく価値が高いですよ。

検索関数で頻出する#N/A・#REF!・#VALUE!エラーの対処法

検索関数を使っていると遭遇する3大エラーと、それぞれの対処法をまとめます。

#N/Aエラー（一致する値が見つからない）

最も多く遭遇するのが#N/Aです。検索キーが範囲内に存在しない場合に表示されます。

対処法1: IFERROR で全エラーを処理

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

ただしIFERRORは#N/A以外のエラー（#REF!や#VALUE!）も同じ「該当なし」に置き換えてしまうため、数式のバグを見逃すリスクがあります。

対処法2: IFNA で #N/A のみ処理（推奨）

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

IFNAは#N/Aだけを処理し、他のエラーはそのまま表示します。バグに気づきやすくなるため、こちらの方が安全です。

対処法3: XLOOKUP の第4引数（最もシンプル）

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

XLOOKUPなら第4引数を指定するだけで完結します。

3つの方法を比較すると次のようになります。

方式数式の長さ#N/A以外のエラーデバッグ
IFERROR長い隠れる（危険）しにくい
IFNAやや長い表示されるしやすい
XLOOKUP第4引数短い表示されるしやすい

XLOOKUPが使える状況ならXLOOKUP第4引数方式、VLOOKUP/INDEX/MATCHならIFNAを使うのが鉄板です。IFERROR/IFNAの詳しい使い分けはスプレッドシートのIF・IFS・IFERROR・IFNAの使い分けガイドも参考にしてみてください。

#REF!エラー（参照範囲が無効）

#REF!は「参照しているセルや列が削除された」「数式の参照範囲が無効になった」場合に発生します。

検索関数で発生する典型ケース:

  • VLOOKUPの範囲内の列を削除した
  • 別シート参照の元シートを削除した
  • スピル範囲が他のセルとぶつかった（XLOOKUPの複数列返却時）

対処は「数式の参照を見直す」のが基本です。エラーを覆い隠さず、原因を特定してから修正しましょう。

#VALUE!エラー（引数の型が不正）

#VALUE!は「数値が必要な引数に文字列を入れた」など、引数の型が合わない場合に発生します。

検索関数での典型ケース:

  • VLOOKUPの第3引数（列番号）に数値ではなく文字列を入れた
  • 検索キーのセルに半角全角の混在がある（見た目が同じでも別物と判定される）
  • 数値が文字列として保存されている（セルの左上に緑の三角マークが出る）

検索キー側と検索範囲側で、数値と文字列の型が一致しているかをまず確認しましょう。

ケース別おすすめパターン集（実務シーン6選）

実務でよくある6つのシーンで、どの関数を選ぶべきかを整理しました。

シーン1：商品マスタから単価を引いてくる（請求書作成など）

検索キー（商品コード）が左端にあり、右側の単価を返すだけのシンプルなケース。

  • Excel共有なし: XLOOKUP（短くて読みやすい）
  • Excel共有あり: VLOOKUP（互換性最優先）

シーン2：社員名簿から氏名・部署を一度に取り出す（人事系資料）

社員IDから氏名・部署を引く定番パターン。複数列返却が必要な場面です。

  • Excel共有なし: XLOOKUP（スピル機能で1数式にまとめられる）
  • Excel共有あり: VLOOKUP（列数だけ数式を書く）

シーン3：部署名から代表者の社員IDを逆引きする

返したい列（社員ID）が、検索したい列（部署）より左にある左方向検索ケース。

  • Excel共有なし: XLOOKUP または INDEX/MATCH
  • Excel共有あり（古いExcel含む）: INDEX/MATCH一択

シーン4：月×支店のクロス集計から値を取り出す

行と列の両方でキー指定が必要な2次元検索ケース。

  • どの場面でも: INDEX/MATCH/MATCH（最も読みやすい）

シーン5：取引先名を「あいまい検索」で引く

「株式会社」をつけ忘れた取引先名でも引きたい場合のワイルドカード検索ケース。

  • Excel共有なし: XLOOKUP（第5引数に2を指定）
  • Excel共有あり: VLOOKUP（完全一致モードで*や?が使える）

シーン6：Excel 2016と共有しつつ左方向検索が必要

互換性と機能制約のダブルパンチ。INDEX/MATCH一択です。

  • どの場面でも: INDEX/MATCH（XLOOKUPは#NAME?、VLOOKUPは方向制限）

迷ったらこの6シーンを思い出してみてください。実務の8割はこれでカバーできるはずですよ。

まとめ｜スプレッドシートでの検索関数ベストプラクティス

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

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

まずは「Excelファイル共有が必要かどうか」を判定基準の最上位に置きましょう。共有が不要ならXLOOKUPで統一、共有が必要ならVLOOKUPまたはINDEX/MATCHに切り替える、という指針で迷いが激減します。

そのうえで、左方向検索や2次元検索といった「特殊な検索パターン」が出てきたら、INDEX/MATCHを取り出してくる、というのが実務の鉄板フローです。

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

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

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