XLOOKUP関数で完全一致・近似一致・逆順検索を制する｜スピル対応・複数条件・エラー処理の実務パターンとMOS Excel試験対策

2026年7月27日

「VLOOKUPで左側の列が検索できない」「検索列と戻り列の位置を変えると式が壊れる」——そんな制約を根本から解消するのがXLOOKUP関数です。

XLOOKUPはExcel 365／Excel 2021以降で使える次世代の検索関数です。検索範囲と戻り範囲を完全に分離した設計により、左方向への逆引き・逆順検索・複数列の一括取得・エラー処理の内包など、従来のVLOOKUP・HLOOKUPでは回り道が必要だった操作をシンプルな1式で実現します。

本記事では、XLOOKUPの基本構文・6つの引数の使い方・一致モードと検索モードの使い分け・実務シナリオ別の活用パターン・MOS Excel試験の出題ポイントを体系的に解説します。

XLOOKUP関数の基本構文

XLOOKUPの構文は次の通りです。

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

最もシンプルな使い方（引数3つ）：A2に入力した商品コードをE列から検索し、対応するF列の商品名を返す例です。

=XLOOKUP(A2, E:E, F:F)

VLOOKUPと比べて列番号の指定が不要です。戻り範囲を直接指定するため、列の挿入・削除で式が壊れる心配がありません。

6つの引数を完全解説

引数	内容	省略	デフォルト
検索値	検索する値またはセル参照	必須	—
検索範囲	検索値を探す列または行	必須	—
戻り範囲	返す値が入っている列または行（複数列可）	必須	—
見つからない場合	一致なし時に返す値（エラー処理を内包）	省略可	#N/Aエラー
一致モード	検索の一致条件の指定（下表参照）	省略可	0（完全一致）
検索モード	検索方向の指定（下表参照）	省略可	1（先頭から末尾）

第4引数の「見つからない場合」は、従来のIFERROR(VLOOKUP(…), “”)に相当する処理をXLOOKUP内に直接書ける点が大きな進歩です。

一致モードの使い分け

第5引数の一致モードは4種類あります。

値	動作	主な用途
0（省略時）	完全一致のみ。一致なしは「見つからない場合」の値を返す	コード・ID検索
-1	完全一致。なければ検索値より小さい最大値を返す	料金テーブル・割引段階の範囲参照
1	完全一致。なければ検索値より大きい最小値を返す	次の締め日・上限単価の参照
2	ワイルドカード（? * ~）による部分一致	名前の一部や品番パターンの検索

一致モード-1の例：売上金額に応じた割引率テーブルを参照する場合です。G列に金額区分（0, 10000, 50000）、H列に割引率（0%, 5%, 10%）が入っているとき、B2の売上金額に対する割引率を求めます。

=XLOOKUP(B2, G:G, H:H, "区分外", -1)

B2が35,000円なら10,000以上50,000未満の区分が適用され「5%」が返ります。VLOOKUPでは第4引数にTRUEを指定してソート必須でしたが、XLOOKUPの一致モードはより明示的です。

ワイルドカード（一致モード2）の例：D2に「A-*」と入力し、品番がA-から始まる最初の商品を検索します。

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

検索モードの使い分け（逆順検索がカギ）

第6引数の検索モードは4種類あります。

値	動作	主な用途
1（省略時）	先頭から末尾方向に検索（最初に一致した値を返す）	通常のコード検索
-1	末尾から先頭方向に検索（最後に一致した値を返す）	最新レコード・最後の取引の取得
2	昇順でソートされた範囲へのバイナリ検索（高速）	大量データの検索高速化
-2	降順でソートされた範囲へのバイナリ検索（高速）	大量データの降順検索高速化

検索モード-1（逆順検索）はXLOOKUP最大の強みのひとつです。同じ顧客コードに複数の注文履歴がある場合、末尾から検索することで最新の注文データだけを取り出せます。VLOOKUPでは実現できなかった操作です。

' A列に顧客コード（重複あり）、B列に注文日、C列に金額が時系列に並んでいる場合
' E2の顧客コードに対応する最新の注文日を取得
=XLOOKUP(E2, A:A, B:B, "履歴なし", 0, -1)

実務シナリオ別の活用パターン

シナリオ1：商品マスタから複数情報を一括取得する（スピル）

戻り範囲に複数列を指定すると、結果がスピル（自動で隣接セルに展開）します。D列に商品コード、E列に商品名、F列に単価、G列に在庫数が入っている場合、A2の商品コードで検索して商品名・単価・在庫数を3セルに一気に返せます。

' B2に入力（C2・D2にスピルして展開される）
=XLOOKUP(A2, D:D, E:G)

VLOOKUPでは列ごとに3本の式が必要でしたが、XLOOKUPは1本でまかなえます。

シナリオ2：複数条件で検索する（＆演算子で連結）

「支店コード＋担当者コード」の組み合わせで行を特定したい場合、検索値と検索範囲をそれぞれ&で連結します。

' A列=支店コード、B列=担当者コード、C列=売上金額の表から
' E2の支店×F2の担当者に対応する売上金額を取得
=XLOOKUP(E2&F2, A:A&B:B, C:C, "該当なし")

Ctrl+Shift+Enterによる配列数式は不要です（Excel 365では通常の式として入力できます）。

シナリオ3：エラー処理を内包して「未登録」を表示する

第4引数「見つからない場合」にテキストや数値を直接書けるため、IFERRORでXLOOKUPを囲む必要がありません。

' コードが見つからない場合は「未登録」を表示
=XLOOKUP(A2, D:D, E:E, "未登録")

' 数値集計に使う場合は0を返す
=XLOOKUP(A2, D:D, F:F, 0)

シナリオ4：横方向（行）を検索してデータを取り出す

XLOOKUPは検索範囲・戻り範囲が「行」でも動作します。HLOOKUPと同等の操作を同じXLOOKUP式で実現できます。1行目に月名（1月～12月）、2行目に売上が並んでいる場合、特定月の売上を取り出します。

' 1行目を月名で検索、2行目の値を返す（水平方向の検索）
=XLOOKUP(D1, 1:1, 2:2, "月なし")

シナリオ5：XLOOKUP同士をネストして2次元検索する

縦と横の両方向を交差検索したい場合、戻り範囲に別のXLOOKUPを入れることで実現します。A1:E5がクロス集計表（行ラベル・列ラベル付き）の場合、H1の行ラベルとH2の列ラベルに対応する交差セルの値を返します。

=XLOOKUP(H1, A2:A5, XLOOKUP(H2, B1:E1, B2:E5))

外側のXLOOKUPが行を特定し、内側のXLOOKUPが列を特定するという二段構造です。INDEX+MATCHの二次元検索より式の構造が直感的です。

VLOOKUPからXLOOKUPへの移行ポイント

既存のVLOOKUP式をXLOOKUPに書き換える際の対応関係を整理します。

項目	VLOOKUP	XLOOKUP
完全一致の指定	第4引数にFALSEまたは0	省略（デフォルトが完全一致）
戻り列の指定	列番号（数値）で指定	戻り範囲を直接指定
左方向への逆引き	不可（INDEX+MATCHが必要）	戻り範囲を左列に指定するだけ
エラー処理	IFERRORで外側を囲む	第4引数に直接記述
近似一致	第4引数TRUEでソート必須	一致モード-1または1で明示的に指定
複数列の取得	式を複数本書く	戻り範囲に複数列指定でスピル
最後の一致を取得	不可（工夫が必要）	検索モード-1で末尾から検索

列番号の管理が不要になる点は実務上の大きなメリットです。VLOOKUPでは列を挿入すると列番号がずれて式が誤動作しますが、XLOOKUPは戻り範囲を直接指定するため列の挿入・削除に強い設計です。

引数の組み合わせパターン早見表

やりたいこと	式の例	ポイント
基本の完全一致検索	=XLOOKUP(A2, D:D, E:E)	引数3つで最小構成
エラー時に空白を返す	=XLOOKUP(A2, D:D, E:E, “”)	第4引数に空文字
近似一致（以下の最大）	=XLOOKUP(A2, D:D, E:E, “外”, -1)	第5引数に-1
ワイルドカード部分一致	=XLOOKUP(“ABC”, D:D, E:E, “なし”, 2)	第5引数に2
最後に一致した行を取得	=XLOOKUP(A2, D:D, E:E, “なし”, 0, -1)	第6引数に-1
複数列を一括スピル	=XLOOKUP(A2, D:D, E:G)	戻り範囲をE:Gと複数列指定
複数条件検索	=XLOOKUP(A2&B2, D:D&E:E, F:F, “なし”)	検索値と検索範囲を&で連結
2次元交差検索	=XLOOKUP(H1, A2:A5, XLOOKUP(H2, B1:E1, B2:E5))	戻り範囲に内側XLOOKUP

よくあるエラーと対処法

#N/A：検索値が見つからない

最も多いエラーです。第4引数「見つからない場合」を追加することで解消します。

' エラー前
=XLOOKUP(A2, D:D, E:E)

' エラー対処後（「未登録」を表示）
=XLOOKUP(A2, D:D, E:E, "未登録")

#VALUE!：検索範囲と戻り範囲のサイズが合っていない

検索範囲が縦1列なのに戻り範囲が横1行になっているなど、向きやサイズが一致しない場合に発生します。検索範囲と戻り範囲が同じ行数（または列数）になっているか確認します。

スピル先に既存データがある（#SPILL!）

スピル先のセルに別のデータが入っていると#SPILL!エラーになります。戻り範囲に複数列を指定した場合は、スピル先のセルが空であることを確認します。

MOS Excel試験でのXLOOKUP出題ポイント

MOS Excel 365では、XLOOKUPは「数式と関数の使用」スキル項目で出題されます。VLOOKUPと並ぶ頻出関数として、引数の意味と基本的な式の作成が問われます。

引数の順序：検索値→検索範囲→戻り範囲→（見つからない場合）→（一致モード）→（検索モード）の順を正確に入力できる
完全一致検索：第5引数を省略した完全一致がデフォルトであることを理解している
「見つからない場合」の活用：第4引数に文字列や数値を指定してエラー表示を回避できる
戻り範囲の指定：列全体（E:E）や特定範囲（E2:E100）を状況に応じて使い分けられる
一致モードの使い分け：0（完全）・-1（以下の最大）・1（以上の最小）・2（ワイルドカード）の違いを問題文に応じて選択できる
VLOOKUPとの違い：列番号指定が不要であること・左方向への参照ができること・エラー処理が内包できることを説明できる

MOS試験 XLOOKUP関連チェックリスト

確認ポイント	操作内容	難易度
基本の3引数入力	=XLOOKUP(検索値, 検索範囲, 戻り範囲)を正しく入力できる	★☆☆
第4引数の活用	見つからない場合に任意の文字列・数値を返す式を作成できる	★★☆
一致モードの指定	問題の指示に従い一致モード0/-1/1/2を正しく選択できる	★★☆
検索モードの指定	末尾から検索（検索モード-1）を正しく指定できる	★★★
複数列の戻り範囲	E:Gのように複数列を戻り範囲に指定してスピルさせられる	★★☆
左方向への参照	検索列より左にある列を戻り範囲に指定できる	★★☆
XLOOKUPのネスト	内側のXLOOKUPを戻り範囲に使った2次元検索を作成できる	★★★

まとめ：XLOOKUPは検索関数の「全方向対応版」

本記事のポイントをまとめます。

検索範囲と戻り範囲を分離した設計：列番号指定が不要で、列の挿入・削除に強い。左方向への逆引きも戻り範囲を左列に指定するだけで実現する
エラー処理が第4引数で内包：IFERRORで外側を囲む必要がなく、式がコンパクトになる
一致モードが明示的：0=完全、-1=以下の最大、1=以上の最小、2=ワイルドカード。VLOOKUPのTRUE/FALSE指定より意図が明確
逆順検索（検索モード-1）が強力：同一コードの最新レコードを末尾から検索する操作がシンプルに書ける
スピルで複数列を一括取得：戻り範囲に複数列を指定するだけで結果が横に展開される。式の本数を減らせる
ネストで2次元検索：内側のXLOOKUPを戻り範囲に使うことで縦横の交差検索を実現できる
MOS試験では基本3引数から第5・6引数まで幅広く出題：一致モードと検索モードの違いを確実に押さえておく

XLOOKUPを習得すると、従来は複数関数の組み合わせが必要だった検索処理を1式でまかなえるようになります。業務のExcelファイルに含まれるVLOOKUPを段階的にXLOOKUPへ書き換えることで、メンテナンス性と可読性が大幅に向上します。MOS試験対策としては、引数の順序と一致モード・検索モードの数値を実際に入力して動きを確かめる練習が最も効果的です。

できるExcel関数 Copilot対応

XLOOKUPをはじめVLOOKUP・INDEX・MATCHなどの検索関数を豊富なサンプルで徹底解説。一致モード・検索モードの使い分けからスピル活用・2次元検索まで、MOS試験頻出の複合パターンを体系的にマスターできる実践ガイドです。

MOS Excel 365 対策テキスト＆問題集

MOS Excel 365試験の公式範囲に完全対応。XLOOKUPを含む関数の入力操作から採点基準まで丁寧に解説し、模擬問題で本番の出題パターンを繰り返し練習できます。試験直前の総仕上げにも最適な一冊です。