XLOOKUP関数はExcel 2021およびMicrosoft 365で使える新しい検索関数です。VLOOKUPを使っていて「左方向に検索できない」「列番号の管理が面倒」「エラー処理にIFERRORが必要」と感じたことがある方は、XLOOKUPに乗り換えることで数式がシンプルになります。INDEX/MATCHを長年使ってきた方も、XLOOKUPで同じことがより短い数式でできます。本記事では、基本の使い方からVLOOKUPとの違い、INDEX/MATCHとの比較、実務での活用パターンまでを体系的に解説します。
XLOOKUP関数の基本構文と使い方
XLOOKUP関数は、指定した値を検索範囲から探し、対応する値を返す関数です。VLOOKUPの後継として設計されており、よりシンプルで柔軟な数式を書くことができます。
=XLOOKUP(検索値, 検索範囲, 戻り値の範囲, [見つからない場合], [一致モード], [検索モード])
- 検索値 … 探したい値（必須）
- 検索範囲 … 検索対象の列（必須）
- 戻り値の範囲 … 結果を返す列（必須）
- 見つからない場合 … 該当なし時に返す値（省略可）
- 一致モード … 0=完全一致, -1=以下, 1=以上, 2=ワイルドカード（省略可）
- 検索モード … 1=先頭から, -1=末尾から, 2=昇順バイナリ, -2=降順バイナリ（省略可）
基本の使い方
商品コード「A003」から商品名を検索する場合、以下のように記述します。
=XLOOKUP(E2, A2:A7, B2:B7)
- E2（検索値）… 探したいコード「A003」
- A2:A7（検索範囲）… 商品コードの列
- B2:B7（戻り値の範囲）… 商品名の列
結果として「キーボード」が返されます。
VLOOKUPでは「テーブル全体を選んで列番号を指定」しますが、XLOOKUPでは「検索する列」と「結果を返す列」を別々に指定します。このため、列が追加・削除されても数式が壊れにくいのが特長です。
VLOOKUPとXLOOKUPの違い — 何が変わるのか
XLOOKUPはVLOOKUPの弱点を解消するために設計された関数です。「VLOOKUPで困っていたこと」が具体的にどう解決されるかを確認しましょう。
| 比較項目 | VLOOKUP | XLOOKUP |
|---|---|---|
| 左方向の検索 | 不可（右方向のみ） | どの方向でもOK |
| 見つからない場合 | #N/Aエラー（IFERRORで処理が必要） | 4番目の引数で直接指定できる |
| 範囲の指定 | テーブル全体 + 列番号（数字）で指定 | 検索列と戻り値の列を直接指定 |
| 複数列の結果 | 1列のみ（複数列は数式を複数書く） | 複数列を同時に返せる（スピル） |
| 列の挿入・削除への耐性 | 列番号がズレて数式が壊れやすい | 列を直接参照するので影響なし |
| デフォルトの一致方法 | 近似一致（意図せず使うとミスに繋がる） | 完全一致（直感的で安全） |
| 対応バージョン | すべてのバージョン | Excel 2021以降 / Microsoft 365 |
VLOOKUPのデフォルトが「近似一致」だった問題
VLOOKUPを使ったことがある人が一度はやってしまうミスが、4番目の引数（TRUE/FALSE）の指定漏れです。VLOOKUPはデフォルトが「近似一致（TRUE）」なので、完全一致を期待していたのに近い値が返ってくる、という事故が起きます。XLOOKUPはデフォルトが「完全一致（0）」なので、このミスが起こりません。
Excel 2021またはMicrosoft 365を使っている場合は、新規の数式はXLOOKUPで書くことをおすすめします。既存のVLOOKUP数式を無理に書き換える必要はありませんが、メンテナンス時に順次置き換えていくのが効率的です。
INDEX/MATCHとXLOOKUPの比較
VLOOKUPの「左方向に検索できない」問題の回避策として、長年使われてきたのがINDEX/MATCHの組み合わせです。XLOOKUPはその機能をほぼすべてカバーしつつ、数式がずっとシンプルです。
同じ処理を3つの書き方で比較する
社員テーブルから「社員ID」をキーにして「部署名」を取得する場合を例に比べてみます。
=VLOOKUP(G2, A2:D100, 3, FALSE)
「A2:D100の3列目」という指定方法。テーブルに列を挿入すると「3」という番号がズレる可能性がある。また、社員IDより左にある列は取得できない。
=INDEX(C2:C100, MATCH(G2, A2:A100, 0))
MATCHで行番号を取得し、INDEXで値を返す2段構えの数式。左方向の検索もできるが、2つの関数を組み合わせる必要があり、慣れないうちは読みにくい。
=XLOOKUP(G2, A2:A100, C2:C100)
「A列で探して、C列の値を返す」という意図がそのまま数式に表れている。INDEX/MATCHと同じことをより短く書ける。
INDEX/MATCHには「複数の一致条件を組み合わせやすい」「配列数式との相性が良い」という強みがあります。XLOOKUPで対応できないケースはほぼありませんが、職場の既存ファイルがINDEX/MATCHで書かれている場合は読み解けるようにしておきましょう。
| 観点 | INDEX/MATCH | XLOOKUP |
|---|---|---|
| 数式の長さ | 長い（2関数のネスト） | 短い（1関数） |
| 左方向の検索 | 可能 | 可能 |
| エラー処理 | IFERRORで囲む必要あり | 4番目の引数で直接指定 |
| 複数列の同時取得 | 複数の数式が必要 | 1つの数式でスピル可能 |
| 対応バージョン | すべてのバージョン | Excel 2021以降 / 365 |
| 読みやすさ | 慣れが必要 | 意図が伝わりやすい |
INDEX/MATCHを使いこなしている人なら、XLOOKUPへの移行は数時間で慣れます。「INDEX(戻り値の範囲, MATCH(検索値, 検索範囲, 0))」をそのまま「XLOOKUP(検索値, 検索範囲, 戻り値の範囲)」に書き換えると思えば、ほぼ1対1の対応です。
見つからない場合のエラー処理
VLOOKUPでは検索値が見つからないと#N/Aエラーが表示され、IFERROR関数でラップする必要がありました。XLOOKUPでは4番目の引数に「見つからない場合の値」を指定するだけで済みます。
VLOOKUPの場合（従来の書き方）
=IFERROR(VLOOKUP(E2, A2:C7, 2, FALSE), “該当なし”)
VLOOKUP単体ではエラー処理ができないため、IFERRORで囲む必要がありました。数式が長くなるうえ、VLOOKUPの引数が正しいかを確認しながらIFERRORを書くのが面倒でした。
XLOOKUPの場合（新しい書き方）
=XLOOKUP(E2, A2:A7, B2:B7, “該当なし”)
4番目の引数に「該当なし」を指定するだけで、検索値が見つからない場合のメッセージを設定できます。
not_found引数の活用パターン
4番目の引数には、文字列だけでなくさまざまな値を返せます。状況に応じて使い分けましょう。
| 使いたい返り値 | 数式の例 | 向いているシーン |
|---|---|---|
| 日本語のメッセージ | =XLOOKUP(E2,A:A,B:B,”未登録”) | 一覧の確認・目視チェック |
| 0を返す | =XLOOKUP(E2,A:A,C:C,0) | 集計・合計に含める場合 |
| 空白を返す | =XLOOKUP(E2,A:A,B:B,””) | 印刷用のきれいな表を作る場合 |
| 別テーブルの値を返す | =XLOOKUP(E2,A:A,B:B, XLOOKUP(E2,D:D,F:F,”なし”)) | 第1候補がなければ第2テーブルを参照する |
特に最後の「見つからない場合にさらに別のXLOOKUPを実行する」パターンは、「メインの商品マスターにない場合は旧商品マスターを参照する」といった実務でそのまま使えます。
IFERROR + VLOOKUPの組み合わせが不要になるため、数式が短く読みやすくなります。数式のネストが深くなりがちなExcelにおいて、このシンプルさは大きなメリットです。
複数列の結果を一括取得する
XLOOKUPの大きな強みの一つが、一つの数式で複数列の結果を同時に取得できることです。VLOOKUPでは列ごとに別々の数式が必要でしたが、XLOOKUPなら1回の検索で済みます。
複数列を返す数式
=XLOOKUP(F2, A2:A6, B2:D6)
戻り値の範囲を「B2:D6」のように複数列にすると、検索結果の行全体（氏名・部署・役職）が横方向のセルに自動展開されます（スピル機能）。
この機能により、社員コードから「氏名」「部署」「役職」を同時に取得する場合でも、数式は1つで済みます。VLOOKUPなら3つの数式が必要だった処理が大幅に簡素化されます。
複数列の結果が自動展開される「スピル」機能はExcel 2021/365で利用可能です。展開先のセルにデータが入っていると#SPILL!エラーになるため、結果が入るセルを空にしておく必要があります。
一致モードの活用 — 段階的な金額テーブルに対応する
XLOOKUPの5番目の引数「一致モード」を使うと、完全一致だけでなく「○○以下」「○○以上」の近似値を検索できます。これが特に役立つのは、段階的な手数料テーブルや料金体系のような「いくら以上ならいくら」という構造のデータです。
一致モードの種類
| 値 | 意味 | 主な用途 |
|---|---|---|
| 0（デフォルト） | 完全一致 | コード・IDの一致検索（通常はこれ） |
| -1 | 完全一致、なければ検索値以下の最大値 | 「○○以下のランク」を求める |
| 1 | 完全一致、なければ検索値以上の最小値 | 「○○以上のランク」を求める |
| 2 | ワイルドカード一致（* や ? が使える） | 部分一致での検索 |
段階的な手数料テーブルの実例
「売上金額に応じた手数料率」のような段階テーブルを想定します。
- 0円 → 5%
- 100万円 → 4%
- 300万円 → 3%
- 500万円 → 2.5%
- 1000万円 → 2%
=XLOOKUP(B2, D2:D6, E2:E6, “範囲外”, -1)
- B2: 対象の売上金額（例: 250万円）
- D2:D6: 売上下限の列
- E2:E6: 手数料率の列
- -1: 完全一致がなければ「以下の最大値」を返す
売上が250万円の場合、「100万円以上300万円未満」に相当するため、手数料率「4%」が返されます。
VLOOKUPの「近似一致（TRUE）」でも同様のことができますが、データが昇順に並んでいる必要があり、並び順が少し変わっただけで間違った値を返してしまいます。XLOOKUPの一致モードは明示的に指定するため、意図が数式から読み取れて安全です。
右から左への検索 — 列の並び替えが不要になる
VLOOKUPで最も多い不満のひとつが「左端の列でしか検索できない」という制約です。たとえば、D列に商品名、A列に商品コードがある表で「商品名から商品コードを逆引きしたい」場合、VLOOKUPでは列の並び順を変えるか、INDEX/MATCHを使う必要がありました。
VLOOKUPの場合（列の並び替えが必要）
商品名（B列）→ 商品コード（A列）を取得したい場合、VLOOKUPでは「検索列（B列）は常にテーブルの左端でなければならない」ため、この逆引きは直接できません。対処策として列順を入れ替える、または作業列を設けてコピーするという回り道が必要でした。
XLOOKUPなら列順の制約ゼロ
=XLOOKUP(“キーボード”, B2:B7, A2:A7)
B列（商品名）を検索して、左側のA列（商品コード）の値を返します。列の順番に関係なく、検索範囲と戻り値の範囲を自由に指定できます。
これにより、「このデータを逆引きしたいからテーブル構造を変えた」という無駄な作業がなくなります。既存のマスターデータをそのまま使いながら、どの列でも検索・参照できるのがXLOOKUPの大きな利点です。
顧客コード（A列）・顧客名（B列）・担当者（C列）というテーブルがあるとき、「顧客名から顧客コードを調べる」「担当者名から担当している顧客名を調べる」など、どの方向でも自由に参照できます。INDEX/MATCHと同じことが、より短い数式で実現できます。
複数条件でのXLOOKUP
「担当者名が○○かつ月が△△の売上を取得したい」のように、複数の条件を組み合わせて検索したい場合があります。XLOOKUPでは、検索値と検索範囲をそれぞれ結合することで複数条件検索が実現できます。
キーを結合する方法（&演算子）
2つの列を「&」でつなぎ合わせた「結合キー」を作成し、それを検索値と検索範囲の両方に使います。
A列: 担当者名、B列: 月、C列: 売上金額 のテーブルで、E2に担当者名、F2に月を入力して売上を取得する場合
=XLOOKUP(E2&F2, A2:A100&B2:B100, C2:C100, “該当なし”)
- E2&F2: 検索値を「担当者名+月」の結合キーにする（例: 「田中4月」）
- A2:A100&B2:B100: 検索範囲も同様に結合した列にする
- C2:C100: 売上金額を返す
検索範囲に配列演算（A列&B列のような結合）を使う場合、Excel 2019以前では「配列数式」として入力する必要があります（Ctrl+Shift+Enterで入力）。Excel 365/2021では通常のEnterで動作します。
キー列を作業列として用意する方法
数式が複雑になる場合は、テーブルに「結合キー」の作業列を追加してしまうほうがシンプルになることもあります。
- D列（作業列）に =A2&B2 のような結合キーを作成する
- XLOOKUPでは =XLOOKUP(E2&F2, D2:D100, C2:C100, “該当なし”) とシンプルに書ける
- 数式がシンプルになりメンテナンスしやすい
どちらの方法でも結果は同じです。数式の複雑さを減らしたい場合、またはファイルを他の人も使う場合は、作業列を使う方法のほうが読みやすく安全です。
XLOOKUPの便利な応用テクニック
末尾から検索する（最新レコードの取得）
同じ検索値が複数ある場合、6番目の引数に「-1」を指定すると末尾から検索できます。最新のレコードを取得したい場面で役立ちます。
=XLOOKUP(“営業部”, C2:C100, B2:B100, , 0, -1)
「営業部」の中で最後に出現する社員名を返します。時系列で蓄積されたログデータから最新の状態を取得する場合に便利です。
ワイルドカード検索
5番目の引数に「2」を指定すると、ワイルドカード（* や ?）を使った部分一致検索ができます。
=XLOOKUP(“*PC*”, B2:B7, C2:C7, “該当なし”, 2)
商品名に「PC」を含む最初の商品の単価を返します。「東京*」なら東京で始まる支店名、「*部」なら部署名の検索にも使えます。
実践シナリオ — 社員名簿と勤怠ログを従業員IDで突合する
ここでは、実務でよくある「2つのテーブルを共通のIDで結びつける」ケースを見てみます。社員名簿と勤怠ログを従業員IDで突合して、各勤怠レコードに社員名・部署を付与する作業です。
テーブルの構成
- A列: 従業員ID（例: EMP001）
- B列: 氏名（例: 田中 太郎）
- C列: 部署（例: 営業部）
- D列: 役職（例: 課長）
- A列: 日付
- B列: 従業員ID（例: EMP001）← ここをキーに名簿を参照
- C列: 出勤時刻
- D列: 退勤時刻
XLOOKUPで名簿情報を勤怠ログに付与する
勤怠ログのE列に氏名・F列に部署を付与するとします。
=XLOOKUP(B2, 名簿!A:A, 名簿!B:C, “未登録”)
- B2: 勤怠ログの従業員ID
- 名簿!A:A: 名簿シートの従業員ID列
- 名簿!B:C: 名簿シートの氏名・部署の2列を同時に返す
- “未登録”: 名簿にないIDが来た場合の表示
E2セルに入力するとE2に氏名、F2に部署が自動展開（スピル）されます。
このような2テーブルの突合は、月次の勤怠集計、売上ログへの担当者情報付与、顧客リストへの担当営業追加など、日常業務で繰り返し登場するパターンです。VLOOKUPを3列分書いていた作業が、XLOOKUPなら1つの数式で完結します。
XLOOKUPの「戻り値の範囲を複数列にできる」機能と「別シートへのクロスシート参照」を組み合わせると、複数テーブルの突合が一気に楽になります。定期的に行う集計作業がある場合、この数式を覚えておくと毎回の作業時間を大幅に短縮できます。
バージョン互換性の確認方法
XLOOKUPが使えるバージョンを確認する
XLOOKUPはExcel 2021以降またはMicrosoft 365で利用可能です。Excel 2019以前のバージョンではXLOOKUP関数が認識されず、#NAME?エラーになります。
自分のExcelがXLOOKUPに対応しているかどうかを確認するには、以下の数式をセルに入力してみる方法があります。
=IFERROR(XLOOKUP(1,1,1),”このバージョンはXLOOKUP未対応”)
この数式を入力したとき、「1」（数値）が返ってくればXLOOKUPが使えます。「このバージョンはXLOOKUP未対応」というメッセージが返ってきた場合は、そのExcelではXLOOKUPが動作しません。
XLOOKUP数式を含むファイルをExcel 2019以前のユーザーに共有すると、数式がエラーになります。共有先のExcelバージョンを確認し、必要に応じてVLOOKUPで記述するか、値として貼り付けてから共有しましょう。
バージョンが混在する環境での対処法
社内にExcel 2019と365の両方が存在する環境では、どちらのバージョンでも動く数式を書く必要が出てくることがあります。
- 共有ファイルにXLOOKUPを使う場合は、必ず相手のバージョンを確認する
- 「値として貼り付け（Ctrl+Alt+V → V）」で数式を消してから共有する方法もある
- どうしてもVLOOKUPで書く必要がある場合は、IFERROR + VLOOKUP を使い、第4引数に「FALSE」を明示的に指定する
VLOOKUPは覚えなくてもいい？
新しくExcelを学ぶ方であれば、XLOOKUPから覚えても問題ありません。ただし、既存のファイルや他者が作成したファイルにはVLOOKUPが使われていることが多いため、VLOOKUPの読み方は理解しておくと安心です。
XLOOKUPは「検索値・検索範囲・戻り値の範囲」の3つを指定するだけで使える、シンプルで強力な関数です。VLOOKUPの制約（右方向のみ、列番号の管理、エラー処理）と、INDEX/MATCHの書きにくさをどちらも解消しており、Excel 2021/365ユーザーには積極的な活用をおすすめします。一致モードを使えば段階的な料金テーブルの参照まで、追加の工夫なしに1つの数式で対応できます。
はてなベース株式会社では、Excel関数の活用支援やデータ管理の効率化コンサルティングを提供しています。「VLOOKUPだらけのファイルを整理したい」「Excelの関数を社内で統一したい」など、お気軽にご相談ください。
