スプレッドシートのXLOOKUP関数の使い方｜VLOOKUPとの違い・逆方向検索・スピル活用まで実例解説

VLOOKUPで「左側の列が取れない」「毎回IFERRORを書くのが面倒」と感じたことはありませんか。

データの列順が変わるたびに数式を直したり、エラー対策でネストが深くなったり。VLOOKUPの限界にぶつかっている方は多いはずです。

そんな悩みを一気に解決してくれるのが、XLOOKUP関数（読み方：エックスルックアップ） です。Googleスプレッドシートでも2022年8月から使えるようになり、今では実務の検索処理の主役になっています。この記事では、XLOOKUP関数の基本構文から逆方向検索・スピル・末尾検索・ワイルドカード、そしてVLOOKUPやIFS・SWITCHとの使い分けまで、実例とサンプル画像つきで解説します。

XLOOKUP関数とは？

XLOOKUP関数は、指定した範囲から値を検索し、対応するデータを返す関数です。読み方は「エックスルックアップ」。Xは「eXtended（拡張された）」の頭文字で、従来のVLOOKUP・HLOOKUPの弱点を克服するために設計された後継関数です。

Googleスプレッドシートでは2022年8月にリリースされ、Excelでも Microsoft 365 と Excel 2021 以降で使えます。覚えればVLOOKUPの出番がほぼなくなる、汎用性の高い検索関数です。

XLOOKUPでできること

• 縦方向・横方向の両方を1つの関数で検索できる
• 検索列の左側にあるデータも取得できる（VLOOKUPでは不可）
• 見つからない場合の表示値を関数内で直接指定できる
• 複数列・複数行をまとめて返せる（スピル対応）
• 末尾からの検索（最新データの取得）が1つの引数でできる
• ワイルドカードや正規表現にも対応している

対応バージョンと互換性

アプリ	対応状況
Googleスプレッドシート	2022年8月以降に作成されたシートで利用可
Microsoft 365 / Excel for Web	利用可
Excel 2021	利用可
Excel 2019 以前	利用不可（VLOOKUPまたはINDEX+MATCHで代替）

補足

他のメンバーが古いExcelで開く可能性がある場合は、互換性の観点からVLOOKUPを併用するか、XLOOKUPの結果を「値貼り付け」で確定させてから共有すると安全です。

XLOOKUP関数の書き方（構文と引数）

基本構文

=XLOOKUP(検索キー, 検索範囲, 結果の範囲, [見つからない場合の値], [一致モード], [検索モード])

引数が6つあるように見えますが、最初の3つだけで基本的な検索はできます。4〜6番目はオプションなので、最初は気にしなくて大丈夫です。

引数の詳細

引数	必須/任意	説明
検索キー	必須	検索したい値（セル参照・文字列・数値など）
検索範囲	必須	検索キーを探す1列または1行の範囲
結果の範囲	必須	返したいデータが入っている範囲（複数列も可）
見つからない場合の値	任意	一致しない場合に返す値（省略時は #N/A）
一致モード	任意	0=完全一致（既定）、-1=次に小さい値、1=次に大きい値、2=ワイルドカード、3=正規表現
検索モード	任意	1=先頭から（既定）、-1=末尾から、2=昇順バイナリ検索、-2=降順バイナリ検索

一致モードと検索モードは少し難しそうに見えますが、普段使いでは「完全一致（一致モード=0）」「先頭から検索（検索モード=1）」の既定値で十分です。後ろの応用例で、それぞれが役立つ場面を順番に紹介します。

一致モード=3は Sheets独自

正規表現一致（一致モード=3）はGoogleスプレッドシート独自の機能です。Excelには無いため、Excelと共有するファイルでは使わないでください。

基本的な使い方

縦方向の検索（商品マスタから取得）

まずは一番よく使う「縦方向の検索」から見てみましょう。

たとえば、下のような商品マスタがあるとします。

E1セルに「A002」と入力されているとき、F1セルで商品名を取得するには次のように書きます。

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

（P）結論: これだけで「みかん」が返ってきます。

（R）理由: 第1引数で検索キー（E1）を、第2引数で検索範囲（A列）を、第3引数で返したい列（B列）を指定しているからです。VLOOKUPのような「何列目を返すか」の列番号はいりません。

（E）具体例: 単価を取りたい場合は第3引数をC列に変えるだけです。

=XLOOKUP(E1, A:A, C:C)

（P）まとめ: VLOOKUPと違って列番号を数える必要がありません。途中で列が増減しても数式を修正しなくていいので、運用の手間が一気に減ります。

横方向の検索（月別売上から取得）

XLOOKUPは横方向のデータにも対応しています。HLOOKUPを使っていた場面をXLOOKUP1本で代替できます。

	A列	B列	C列	D列
1行目（月）	1月	2月	3月	4月
2行目（売上）	120	95	180	210

「3月」の売上を取得するには次のように書きます。

=XLOOKUP("3月", A1:D1, A2:D2)

縦でも横でも同じ書き方で使えるのがXLOOKUPの強みです。HLOOKUPを覚え直す必要もありません。

実践的な使い方・応用例

逆方向（右から左）の検索

VLOOKUPで困る場面の代表例が「検索キーより左の列のデータを取りたい」ケースです。VLOOKUPは検索列の右側しか取れませんが、XLOOKUPはどの方向でも取得できます。

Before（VLOOKUPの場合）:

左側の列を取得するには列を並べ替えるか、INDEX+MATCH関数を組み合わせる必要があり、式が複雑になりがちです。

=INDEX(A:A, MATCH(E1, B:B, 0))

After（XLOOKUPの場合）:

検索範囲と結果の範囲を自由に指定できるので、シンプルに書けます。

たとえば「商品名（B列）」で検索して「商品コード（A列）」を返す場合：

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

ちょっと不思議に見えますが、やっていることはシンプルです。「B列で探して、A列の値を返す」というだけ。「検索する列」と「返す列」を自由に組み合わせられます。

複数列をまとめて返す（スピル）

XLOOKUPは第3引数の結果の範囲に複数列を指定すると、一度にまとめて返してくれます。

商品コードで検索して「商品名」と「単価」を同時に取得する例：

=XLOOKUP(E1, A:A, B:C)

これを入力すると、F1セルに商品名、G1セルに単価が自動で展開されます。これがスピル機能です。VLOOKUPでは列ごとに数式を書く必要があったので、大幅に手間が減ります。

補足

スピル先のセル（F1の場合はG1）にすでにデータがあると #REF! エラーになります。隣のセルを空けておくのを忘れないようにしましょう。

見つからない場合にデフォルト値を返す

VLOOKUPでは一致しないと #N/A エラーが出るため、別途 IFERROR でエラー処理する必要がありました。

Before（VLOOKUPの場合）:

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

After（XLOOKUPの場合）:

第4引数に表示したい文字を指定するだけです。

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

IFERRORを外付けしなくてよいので、式がスッキリします。空白にしたい場合は "" を指定してください。

=XLOOKUP(E1, A:A, B:B, "")

部分一致（ワイルドカード）検索

第5引数の一致モードを 2 にすると、ワイルドカードが使えます。「株式会社」を含む取引先を検索する、といった柔軟な検索ができます。

=XLOOKUP("*株式会社*", A:A, B:B, "該当なし", 2)

使えるワイルドカードは次の3つです。

記号	意味
*	任意の文字列（0文字以上）
?	任意の1文字
~	ワイルドカード自体を文字として扱う（~ で を検索）

部分一致はFILTER関数でも可能ですが、XLOOKUPなら1件だけ引き当てたいときにシンプルに書けます。

正規表現で柔軟に検索する

Googleスプレッドシート独自の一致モード 3 を指定すると正規表現が使えます。たとえば =XLOOKUP("^A0", A:A, B:B, "なし", 3) で「A0」から始まる商品コードを検索できます。正規表現の基本はREGEXMATCH関数の解説もあわせてご覧ください。

末尾からの検索（最新データを取得）

第6引数の検索モードを -1 にすると、範囲の末尾から検索します。履歴テーブルから最新の記録を取得するときに便利です。

たとえば、日付順に並んだ訪問履歴から、顧客ごとの最新訪問日を取る場合：

=XLOOKUP(E1, A:A, B:B, "記録なし", 0, -1)

A列が顧客ID、B列が訪問日のとき、末尾から検索することで最新の日付が返ります。VLOOKUPでは実現が難しかった使い方です。

最新の在庫数や担当者を取得する

「商品コードでの最新の在庫更新日」「顧客IDの最終購入金額」など、追記型ログから直近の値を抽出するシーンに有効です。データを日付順に追加していくだけで、最新の値が自動で更新されます。

大規模データで高速化する（バイナリ検索）

データが昇順または降順にソート済みであれば、検索モードを 2（昇順）または -2（降順）にすることでバイナリ検索が使えます。数万件規模のデータでも体感速度が変わります。

=XLOOKUP(E1, A:A, B:B, "なし", 0, 2)

バイナリ検索の前提

バイナリ検索は 検索範囲がソート済み であることが前提です。ソートされていない範囲に指定すると、誤った結果を返すことがあります。普段の業務では検索モードを省略（既定の 1）したまま使うほうが安全です。

実務シナリオ別の使い方

シナリオ1: 在庫照合（注文一覧 × 在庫マスタ）

注文一覧の各行に、在庫マスタから「現在庫」と「単価」を同時に取得する：

=XLOOKUP(A2, 在庫マスタ!A:A, 在庫マスタ!B:C, "未登録")

スピル機能で在庫数と単価が一度に展開され、IFERRORも不要です。

シナリオ2: 取引先マスタの引き当て

取引先名から取引先コードと担当営業を逆引きする：

=XLOOKUP(B2, マスタ!B:B, マスタ!A:A, "未登録", 2)

一致モード 2（ワイルドカード）にすることで「株式会社」の有無や表記ゆれにある程度耐性を持たせられます。本格的な表記ゆれ対策にはASC関数・CLEAN関数を組み合わせるのが定石です。

シナリオ3: 直近の売上を取得

日次売上ログから、各商品の最新売上日と金額を取得する：

=XLOOKUP(A2, 売上!B:B, 売上!A:D, "売上なし", 0, -1)

「末尾から検索」の -1 と複数列スピルの組み合わせで、最新行の必要列がまとめて返ります。

よくあるエラーと対処法

#N/Aエラー（検索キーが見つからない）

（P）結論: #N/A エラーが出る主な原因は「検索キーが検索範囲に存在しない」か「データの型が違う」ことです。

（R）理由: XLOOKUPはデフォルトで完全一致（一致モード=0）を使うため、値が完全に一致しないと見つからないと判断します。

（E）よくあるミス:

• 検索キーが数値なのに検索範囲のデータが文字列になっている（または逆）
• セルに余分なスペースが入っている
• 日付の表示形式がシリアル値のまま

（P）解決策:

原因	対処方法
型の不一致（数値 vs 文字列）	TEXT 関数で文字列化 / VALUE 関数で数値化
余分なスペース	TRIM 関数で前後の空白を除去
表記ゆれ（大文字小文字・全半角）	UPPER / LOWER / ASC / JIS 関数で統一
そもそも存在しない	第4引数に "該当なし" などを指定

=XLOOKUP(TRIM(E1), A:A, B:B, "データなし")

#VALUE!エラー（範囲サイズ不一致）

検索範囲と結果の範囲の行数（または列数）が一致していないと #VALUE! エラーになります。

ダメな例:

=XLOOKUP(E1, A1:A10, B1:B5)

検索範囲が10行、結果の範囲が5行なのでサイズが一致しません。

対処: 両方の範囲の行数（または列数）をそろえましょう。列全体を指定する A:A と B:B のような書き方なら自動的にそろうので安全です。

#REF!エラー（スピル先が埋まっている）

複数列返しのときに、スピルで展開される先のセルに既存データがあると #REF! エラーが出ます。

対処: スピル先のセルを空ける、または結果の範囲を1列だけに絞る。

#NAME?エラー（関数名の誤り）

XLOOKUPと入力しても認識されない場合があります。古いスプレッドシート（2022年8月以前作成）で開いている可能性があります。新しいファイルを作成し直すか、対応シートにコピーしてください。

VLOOKUP・INDEX+MATCH・IFS・SWITCHとの違い・使い分け

関数ごとの比較表

比較項目	VLOOKUP	INDEX+MATCH	XLOOKUP
検索方向	左端列のみ	任意	任意
横方向の検索	不可（HLOOKUPが必要）	可	可
エラー処理	IFERRORが別途必要	IFERRORが別途必要	第4引数で内蔵
列番号の指定	必要（何列目か数える）	不要	不要
複数列の同時取得	不可	不可	可（スピル）
末尾から検索	不可	不可	可
対応シート	すべて	すべて	2022年8月以降

IFS / SWITCHとの使い分け

条件分岐型の関数とも比較しておきましょう。

用途	推奨関数	理由
条件式（A1>100 など）で分岐	IFS関数	比較演算子を使った分岐に強い
単一値の置換（コード→ラベル）	SWITCH関数	数式内に値を直接書く小規模変換に向く
マスタテーブルからの参照	XLOOKUP	データ量が増えてもメンテしやすい

IFSやSWITCHは「数式内に判定条件を直接書く」関数のため、対応件数が増えると保守がつらくなります。マスタとして表で管理したくなったら、XLOOKUPに切り替えるのが正解です。

XMATCHとの組み合わせ

「位置（行番号や列番号）」が欲しい場面ではXMATCH関数と組み合わせると便利です。XMATCHはXLOOKUPと同じ検索モードに対応しているため、INDEX+MATCH の上位互換として使えます。

どの関数を使うべきか

• 普段使いはXLOOKUP: 2022年8月以降のスプレッドシートなら、今後は迷わずXLOOKUPが第一候補です
• 古いシートやExcel互換重視ならVLOOKUP: 他部署とのファイル共有で古い環境に戻す可能性があるときはVLOOKUPのままが無難
• INDEX+MATCHはもう不要: XLOOKUPが使えるなら役割を完全に引き継げます
• 条件型の判定はIFS、マスタ参照はXLOOKUP: 「マスタ表があるか」で見分けるのが分かりやすいです

普段の作業で「列番号が変わるたびに修正していた」「IFERRORでネストが深くなっていた」と感じている方におすすめです。この機会にXLOOKUPに乗り換えると、作業が一気に楽になります。

まとめ

XLOOKUP関数のポイントをまとめます。

• 基本は3引数: =XLOOKUP(検索キー, 検索範囲, 結果の範囲) で動く
• 左方向にも検索できる: VLOOKUPの列制限がなく、検索範囲と結果の範囲を自由に設定できる
• エラー処理が内蔵: 第4引数で "該当なし" などを指定すれば #N/A をそのまま置き換えられる
• 縦横両対応: VLOOKUPとHLOOKUPを1つの関数に統合したイメージ
• 複数列まとめ取得: 結果の範囲に複数列を指定してスピルで展開できる
• 末尾から検索も可: 第6引数を -1 にすると最新データの取得がラク
• ワイルドカード・正規表現に対応: 一致モード 2（ワイルドカード）と 3（正規表現・Sheets独自）が使える
• 大規模データはバイナリ検索: 検索モード 2 / -2 で高速化（ソート済み前提）

VLOOKUPに慣れている方も、基本の3引数から使い始めれば違和感なく移行できます。まずは手元の作業で1回使ってみてください。慣れてしまえば「もうVLOOKUPには戻れない」と感じるはずです。

検索系の関数を体系的に学びたい方は、VLOOKUP関数・HLOOKUP関数・INDEX関数・MATCH関数・XMATCH関数の解説もあわせてどうぞ。複数条件の抽出ならFILTER関数、条件分岐はIFS関数・SWITCH関数、表記ゆれ対策はASC関数・CLEAN関数が役立ちます。