Excel の XLOOKUP 関数は、VLOOKUP や HLOOKUP より柔軟に使いやすい検索関数です。多くの検索用途では、左方向検索や見つからないときの処理まで 1 つの関数でまとめられます。
この記事では Excel 2024（Build 19822）の実機で 16 通りの挙動を検証し、VLOOKUP との違いと 6 つの引数の使い方を、初めての人にも迷わない順番でまとめます。INDEX＋MATCH のような従来パターンも大文字小文字を区別したいときなどで今でも有効ですが、まずは XLOOKUP の基本を押さえると検索の見通しがかなり良くなります。
- XLOOKUP関数とは｜Excel 2021以降で使える検索関数
- XLOOKUPの基本構文と6つの引数
- 最小例｜商品IDから商品名を検索する
- VLOOKUPとの違いを一目で比較
- 左方向検索｜XLOOKUPならではの強み
- 見つからないときの扱い｜省略時は#N/A、第4引数で代替値
- 一致モードの4つの値（0・1・-1・2）
- 検索モード｜末尾から検索（-1）で重複キーの最後を取る
- 戻り範囲を複数列にしてスピル｜1つのXLOOKUPで複数列
- 検索値を配列にしてスピル｜1つのXLOOKUPで複数行
- XLOOKUPのよくある注意点
- サンプルファイル
- 次に読む記事
- まとめ｜XLOOKUPの基本を押さえるとVLOOKUPより柔軟に検索できる
XLOOKUP関数とは｜Excel 2021以降で使える検索関数
XLOOKUP は、指定した検索値に一致する値を別の範囲から取り出す検索関数です。次のバージョンの Excel で使えます。
- Microsoft 365（サブスクリプション版）
- Excel 2021（買い切り版）
- Excel 2024（買い切り版、本記事の検証環境）
Excel 2019 以前では XLOOKUP は使えないので、その場合は VLOOKUP や INDEX＋MATCH で代替します。XLOOKUP の関連記事を一覧で追いたいときは Excel関数：「XLOOKUP関数の基本と活用」目次 もあわせて参照してください。
XLOOKUPの基本構文と6つの引数
構文は次の通りです。必須が 3 つ、任意が 3 つで、合計 6 引数を取ります。とりあえず最初の 3 つだけ書けば VLOOKUP 相当の動きになります。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
| 位置 | 引数 | 要否 | 意味 |
|---|---|---|---|
| 1 | 検索値 | 必須 | 探したい値。セル参照・文字列・数値・配列のいずれでも OK |
| 2 | 検索範囲 | 必須 | 検索値を探す範囲（1 列または 1 行） |
| 3 | 戻り範囲 | 必須 | 見つかった行・列から返す範囲。1 列以上・1 行以上 |
| 4 | 見つからない場合 | 任意 | 一致しないときに返す値。省略すると #N/A |
| 5 | 一致モード | 任意 | 0（完全一致・既定）、-1（次に小さい）、1（次に大きい）、2（ワイルドカード） |
| 6 | 検索モード | 任意 | 1（先頭から・既定）、-1（末尾から）、2（昇順バイナリ）、-2（降順バイナリ） |
最小例｜商品IDから商品名を検索する
まずは任意引数を使わない、もっともシンプルな形から始めます。次の商品テーブルから 商品ID=101 に対応する商品名を取り出してみます。
=XLOOKUP(101, A2:A8, B2:B8)
検索範囲 A2:A8（商品ID）の中から 101 を探し、見つかった行の戻り範囲 B2:B8（商品名）の値を返します。結果は 「りんご」です。VLOOKUP と違って、列番号を指定する必要はありません。
=XLOOKUP(G2,A2:A8,B2:B8)、結果はりんご）検索範囲と戻り範囲は別々に指定できるので、A:A と B:B のように列全体を渡すこともできます。実務ではテーブルに変換して構造化参照を使うと、行が増えたときに範囲を書き換えずに済むので便利です。
VLOOKUPとの違いを一目で比較
VLOOKUP から XLOOKUP への置き換えを検討している方向けに、主な違いを表にまとめました。Excel 2024 の実機で挙動を確認した結果です。
| 項目 | VLOOKUP | XLOOKUP |
|---|---|---|
| 必須引数 | 検索値・範囲・列番号・検索方法の 4 つ | 検索値・検索範囲・戻り範囲の 3 つ |
| 戻り値の指定 | 列番号（整数）。列挿入でズレる | 戻り範囲（セル範囲）。直感的で挿入にも強い |
| 列の位置 | 検索列の右側だけ | 左右どちらでもOK |
| 見つからないとき既定 | #N/A | #N/A（共通）、第4引数で代替値を直接指定できる |
| 近似一致 | TRUE / FALSE の 2 値 | 一致モード 0 / -1 / 1 / 2 の 4 値 |
| ワイルドカード | 既定で利用可 | match_mode=2 を指定したときだけ効く |
| 検索方向 | 先頭から一方向のみ | 検索モードで末尾から・バイナリ検索も可 |
| 複数列の同時取得 | 不可（複数の VLOOKUP が必要） | 戻り範囲を複数列にするだけで OK |
| 使える Excel | 旧バージョンを含むほぼすべての Excel | Excel 2021 / Microsoft 365 / Excel 2024 |
VLOOKUP の基本的な使い方は エクセルでの関数の使い方：VLOOKUP, HLOOKUP, INDEX, MATCH の基本と応用 にまとまっているので、あわせて読むと置き換えのイメージがつきやすいです。
左方向検索｜XLOOKUPならではの強み
VLOOKUP では、検索範囲が戻したい値よりも左にある必要がありました。たとえば「商品名から商品IDを引きたい」場合、VLOOKUP ではそのままでは書けず、INDEX＋MATCH を使うのが定番でした。
XLOOKUP は検索範囲と戻り範囲を別々に指定するので、戻り範囲が検索範囲の左側にあっても問題なく動きます。商品名 "牛乳"（B 列）から商品ID（A 列）を取り出す例です。
=XLOOKUP("牛乳", B2:B8, A2:A8)
結果は 104。VLOOKUP で実現しようとすると、INDEX＋MATCH を書くか、検索列を右側にコピーする回避策が必要でした。XLOOKUP ならこのままの並びで済みます。
見つからないときの扱い｜省略時は#N/A、第4引数で代替値
検索値がテーブルに存在しないとき、第 4 引数を省略すると #N/A エラーが返ります。そのまま表示したくないときは、第 4 引数で代替値を直接指定できます。
=XLOOKUP(999, A2:A8, B2:B8, "該当なし")
#N/A、第4引数 "該当なし" 指定ありは文字列でフォールバックこの書き方は VLOOKUP の IFERROR(VLOOKUP(...),"該当なし") に相当しますが、関数をネストせずに済むぶん式が短く、読みやすくなります。#DIV/0! など他の種類のエラーも拾いたい場合は、IFERROR との併用が必要です。エラー処理の詳しいパターンは Excel XLOOKUP関数でエラー処理をマスター にまとまっています。
一致モードの4つの値（0・1・-1・2）
第 5 引数の一致モードは 4 つの値を取ります。既定は 0（完全一致）で、この値だけ覚えておけば通常の検索はカバーできます。近似一致やワイルドカードは、必要なときに使い分けてください。
| 一致モード | 動き | 用途 |
|---|---|---|
0（既定） | 完全一致 | 一般的な検索。見つからなければ #N/A または第4引数の値 |
-1 | 完全一致、なければ次に小さい値 | 成績の段階評価・税率テーブルなどの階段型ルックアップ |
1 | 完全一致、なければ次に大きい値 | 料金プランの「◯ GB 以上」判定など、上向きの閾値 |
2 | ワイルドカード（* ?）を有効にする | 部分一致検索 |
閾値 0/60/70/80/90 に対応する評価を F〜A で用意した表で、検索値 75 に各モードを適用した結果はこちらです。
75 に対する一致モード別の結果。mm=-1 は C（70の評価）、mm=1 は B（80の評価）向きを間違えないためのクイック確認
Web 上の解説では 1 と -1 の向きを逆に書いているものもあります。迷ったら、次の 2 つの式を空のセルに直接貼り付けて結果を見比べると、公式の向きをすぐ確認できます。
=XLOOKUP(75, {70,80}, {"C","B"}, , 1)→ 公式どおりなら “B”（次に大きい 80 の評価）=XLOOKUP(75, {70,80}, {"C","B"}, , -1)→ 公式どおりなら “C”（次に小さい 70 の評価）
Microsoft 公式の定義は「1 は次に大きい、-1 は次に小さい」で、本記事の実機結果もその通りになります。
検索モード｜末尾から検索（-1）で重複キーの最後を取る
第 6 引数の検索モードは、検索する向きを指定します。既定は 1（先頭から末尾へ）で、重複する検索値があったときは最初に見つかった行を返します。
商品テーブルに商品名「りんご」が 101 と 107 の 2 行あるとき、既定のままだと先頭の 101 しか取れません。末尾の 107 を取りたいときは、検索モードに -1 を指定します。
=XLOOKUP("りんご", B2:B8, A2:A8, , 0, -1)
101、search_mode=-1 は末尾行の 107。並び順を変えずに最新登録分を取れる検索モードには 2（昇順バイナリ）と -2（降順バイナリ）もあり、あらかじめソート済みの大量データで検索を速くしたいときに使えます。-1 の目的は速度向上ではなく、あくまで「どちら側から探すか」の方向制御です。重複があるときの取得行が変わる点が重要で、通常のデータでは既定のままで問題ありません。
ここまでは、XLOOKUP の基本的な使い方です。ここからは、複数列を返すスピルや、複数の検索値をまとめて処理する方法など、慣れてきたあとに便利な応用例を紹介します。初めて XLOOKUP に触れる場合は、ここまでの内容だけでも十分実用になります。
戻り範囲を複数列にしてスピル｜1つのXLOOKUPで複数列
Microsoft 365 / Excel 2021 以降では、XLOOKUP の戻り範囲に複数列を指定すると、その列数ぶん一度に取得できます。VLOOKUP のように列ごとに関数を書き分ける必要がありません。
=XLOOKUP(103, A2:A8, B2:D8)
B2:D8（3 列）を指定すると、1 つの XLOOKUP で商品名・価格・カテゴリが横に展開される入力したセル（この例では G21）から右方向へ自動的に結果がスピルし、G21=バナナ H21=150 I21=果物 のように展開されます。スピル範囲のセルを直接編集すると #SPILL! エラーになるので注意してください。複数列スピルの並べ替えや特殊な列順で取り出す応用は XLOOKUP関数で複数列のスピル配列検索結果を作成する方法 と XLOOKUP関数の「戻り範囲」へ特殊な順番の複数の列を登録する方法 にまとまっています。
検索値を配列にしてスピル｜1つのXLOOKUPで複数行
検索値側にセル範囲を指定すると、範囲の各行に対して検索が実行され、結果が縦方向にスピルします。たとえば商品ID 101 104 999（F3:F5）をまとめて検索する例です。
=XLOOKUP(F3:F5, A2:A8, B2:B8, "該当なし")
F3:F5 の 3 つの検索値に対応する結果が G3:G5 に縦に展開される（999 は第 4 引数で「該当なし」）AND 条件の複数条件検索（例えば「部署 = 営業 かつ 役職 = 主任」）は XLOOKUP と & 演算子の組み合わせで書きます。具体例は ExcelでXLOOKUP関数の複数条件検索を行う方法 を参照してください。
XLOOKUPのよくある注意点
XLOOKUP を実務で使っていてつまずきやすいポイントを 5 つ、実機で再現して整理しました。困ったときはこの節だけ読み返せば大体あたりがつくはずです。
- ワイルドカードは
match_mode=2を指定する：省略すると*や?は文字そのものとして扱われ、#N/Aになります。"りん*"で「りんご」にヒットさせたいなら=XLOOKUP(検索値, 範囲, 戻り, , 2)と書いてください。 - 文字と数値は別物として扱われる：
"101"（文字列）と101（数値）は一致しません。ID 列が数値なら、検索値も数値で渡します。CSV 取り込みで数値が文字列になっているケースはVALUE関数などで変換します。 - 大文字小文字は区別されない：
"apple"で"Apple"にヒットします。大文字小文字を区別したい場面では XLOOKUP だけでは対応できないので、EXACT関数を使う別アプローチが必要です。 - 空セルは
0として返る：検索結果のセルが空白だと、戻り値は""（空文字）ではなく0（数値ゼロ）になります。未入力と「0 が入力されている」を区別したいときは、戻り値を&""で文字列化するかIFで先に判定します。 - 検索範囲と戻り範囲の行数が揃わないと
#VALUE!：A2:A10とB2:B5のように行数が違うと#N/Aではなく#VALUE!になります。範囲を編集したら、両方の行数（または列数）が同じかを確認してください。
サンプルファイル
本記事のすべてのパターン（基本検索・左方向・見つからないとき・一致モード・検索モード・複数列スピル・検索値配列スピル・5 つの罠）を 4 シート構成にまとめたサンプルブックを用意しました。Excel 2021 以降 / Microsoft 365 / Excel 2024 で開けます。
次に読む記事
- Excel関数：「XLOOKUP関数の基本と活用」目次（シリーズ全体の目次）
- Excel XLOOKUP関数でエラー処理をマスター（IFERROR との組み合わせまで）
- Excel XLOOKUP関数で二次元検索を実現（縦と横を両方指定）
- Excel XLOOKUP関数の高度な活用（IF/AND/OR との組み合わせ）
- XLOOKUP関数で複数列のスピル配列検索結果を作成する方法
- XLOOKUP関数の「戻り範囲」へ特殊な順番の複数の列を登録する方法
- ExcelでXLOOKUP関数の複数条件検索を行う方法
まとめ｜XLOOKUPの基本を押さえるとVLOOKUPより柔軟に検索できる
- XLOOKUP は 3 引数だけでも VLOOKUP の役割を果たし、任意引数を足すと見つからないとき・近似一致・方向反転まで 1 つの関数にまとまる
- 左方向検索は XLOOKUP の大きな利点で、VLOOKUP では INDEX+MATCH を使う場面に置き換えやすい
- 一致モードは
0 / -1 / 1 / 2の 4 値。公式では1が次に大きい、-1が次に小さい - 検索モードの
-1は方向反転で、重複キーのうち末尾行を取りたいときに使う（速度向上が目的ではない） - 大文字小文字非区別や空セル 0 返しなど、共通の落とし穴は覚えておくと後から慌てない
コメント