ExcelのXLOOKUPの使い方｜初心者向けにVLOOKUPとの違いから解説
本ページはプロモーションが含まれています
XLOOKUPは、「探す値」「探す列」「返したい列」の3つを指定するだけで使える検索関数です。VLOOKUPのように列番号を数える必要がなく、検索列より左にある値も取り出せます。この記事では、基本の書き方から、見つからないときの表示の変え方、よくあるエラーまでを順番に解説します。
目次
XLOOKUPとは？使えるExcelのバージョン
XLOOKUPは、表の中から条件に合う値を探して、対応する別の列の値を返す関数です。商品コードから商品名や単価を取り出す、社員番号から部署名を取り出す、といった場面で使います。
使えるのは、Microsoft 365のExcel、Excel 2021以降、Web版のExcelです。Excel 2019以前では使えません。
古いExcelの人とファイルを共有するときは注意
XLOOKUPを使ったファイルを、XLOOKUPに対応していないExcelで開くと、正しく計算されずエラー表示になることがあります。取引先や社内でExcelのバージョンが混在している場合は、相手の環境を確認するか、VLOOKUPやINDEX/MATCHを使うほうが安全です。自分のExcelのバージョンは「ファイル」→「アカウント」から確認できます（表示はお使いのバージョンで異なる場合があります）。
XLOOKUPの基本の書き方
XLOOKUPの基本形は次のとおりです。最初の3つだけ覚えれば、ほとんどの場面で使えます。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])| 引数 | 意味 | 例 |
|---|---|---|
| 検索値 | 探したい値 | 商品コードが入ったセル |
| 検索範囲 | 検索値を探す列（1列） | 商品マスタの「コード」列 |
| 戻り範囲 | 結果として返したい列 | 商品マスタの「商品名」列 |
| 見つからない場合 | 省略可。見つからないときに表示する内容 | "該当なし" |
| 一致モード | 省略可。省略すると完全一致 | 0 |
| 検索モード | 省略可。省略すると上から順に検索 | 1 |
例：商品コードから商品名を取り出す
E列に商品コード、F列に商品名、G列に単価が入った商品マスタ（2〜100行目）があり、A2セルに入力した商品コードの商品名をB2に表示したいとします。
- B2セルを選択する
=XLOOKUP(A2, $E$2:$E$100, $F$2:$F$100)と入力する- Enterキーを押す
- 下の行にもコピーする場合は、B2セルの右下の■をドラッグする
検索範囲と戻り範囲に「$」を付けているのは、数式をコピーしたときに範囲がずれないようにするためです。F4キーを押すと「$」をまとめて付けられます。
単価を取り出したい場合は、戻り範囲を $G$2:$G$100 に変えるだけです。列番号を数え直す必要はありません。
VLOOKUPとの違い
すでにVLOOKUPを使っている人向けに、主な違いを表にまとめます。
| 比べるポイント | VLOOKUP | XLOOKUP |
|---|---|---|
| 返す列の指定 | 左から何列目かを数字で指定 | 返したい列そのものを範囲で指定 |
| 検索列より左の値 | 取り出せない | 取り出せる |
| 一致の初期設定 | 第4引数を省略すると近似一致 | 省略すると完全一致 |
| 見つからないとき | #N/A（IFERRORなどで対応） | 第4引数で表示内容を指定できる |
| 列を挿入したとき | 列番号がずれて結果が変わることがある | 範囲が自動で追従する |
| 使えるバージョン | 幅広いバージョンで使える | Microsoft 365・Excel 2021以降など |
特に大きいのは「完全一致が初期設定」という点です。VLOOKUPでは最後の引数にFALSEを付け忘れて、意図しない値が返ってくるミスがよくあります。XLOOKUPは省略しても完全一致で探すので、初心者でも間違えにくくなっています。
VLOOKUPで#N/Aに悩んでいる場合は、ExcelのVLOOKUPで#N/Aが出る原因7つと直し方【初心者向け】も参考にしてください。スペースの混入や数値と文字列の違いなど、原因の多くはXLOOKUPでも共通です。
便利な使い方3つ
1. 見つからないときに「該当なし」と表示する
4番目の引数に表示したい文字を入れます。
=XLOOKUP(A2, $E$2:$E$100, $F$2:$F$100, "該当なし")何も表示したくない場合は ""（ダブルクォーテーション2つ）を指定します。VLOOKUPのようにIFERRORで囲む必要はありません。
2. 検索列より左にある値を取り出す
たとえば商品名（F列）から商品コード（E列）を探したい場合も、範囲を入れ替えるだけで書けます。
=XLOOKUP(A2, $F$2:$F$100, $E$2:$E$100)VLOOKUPでは表の列を並べ替えるか、INDEXとMATCHを組み合わせる必要があった処理です。
3. 複数の列をまとめて取り出す
戻り範囲を複数列にすると、隣のセルにまとめて結果が表示されます。
=XLOOKUP(A2, $E$2:$E$100, $F$2:$G$100)この例では、B2に商品名、C2に単価が一度に表示されます。結果が表示されるセルに別のデータが入っていると「#SPILL!」エラーになるので、右側のセルは空けておきましょう。
下から検索したいときは検索モードを使う
同じコードが複数行ある表で、一番下（最新）のデータを取り出したいときは、6番目の引数に -1 を指定します。例: =XLOOKUP(A2, $E$2:$E$100, $F$2:$F$100, "該当なし", 0, -1)。通常は上から探して最初に見つかった値を返します。
よくあるエラーと対処法
| 表示 | 主な原因 | 対処法 |
|---|---|---|
| #N/A | 検索値が検索範囲に見つからない | 値の存在、余分なスペース、数値と文字列の違いを確認する。第4引数で表示を変える |
| #VALUE! | 検索範囲と戻り範囲の行数が違う | 2つの範囲の開始行・終了行をそろえる（例: どちらも2〜100行目） |
| #NAME? | 関数名の入力ミス、またはXLOOKUPに対応していないExcel | つづりを確認する。バージョンを「ファイル」→「アカウント」で確認する |
| #SPILL! | 複数列の結果を表示する先にデータがある | 結果が表示されるセルを空ける |
#N/Aが出たときは、まず検索値のセルと検索範囲の値が本当に同じかを確認します。=LEN(A2) で文字数を数えると、見た目ではわからないスペースの混入に気付けます。
範囲の指定ミスを防ぐには、表を「テーブル」に変換しておくのも有効です。表の中のセルを選んで「Ctrl＋T」を押すとテーブルになり、データを追加しても範囲が自動で広がります。
XLOOKUPを使うべきか迷ったときの判断基準
XLOOKUPとVLOOKUPのどちらを使うかは、次のように考えると迷いません。
- 自分や社内の全員がMicrosoft 365・Excel 2021以降を使っている → XLOOKUPがおすすめ
- 古いバージョンのExcelを使う人とファイルをやり取りする → VLOOKUPやINDEX/MATCHが無難
- Googleスプレッドシートなど他のツールでも開く → 開く側のツールで使えるか事前に確認する
重複のない一覧を作るUNIQUE関数なども、XLOOKUPと同じく比較的新しいExcelで使える関数です。あわせて覚えたい場合はExcelで重複を削除する関数｜UNIQUEとCOUNTIFの使い分けも参考にしてください。
まとめ
- XLOOKUPは「検索値・検索範囲・戻り範囲」の3つを指定するだけで使える
- 初期設定が完全一致なので、VLOOKUPよりミスが起きにくい
- 第4引数で、見つからないときの表示を指定できる
- 検索列より左の値も取り出せ、複数列もまとめて返せる
- 使えるのはMicrosoft 365・Excel 2021以降など。古いExcelとファイルを共有するときは注意する
まずは手元の表で、商品コードから商品名を取り出す基本の形を試してみてください。3つの引数の意味がつかめれば、ほかの使い方もすぐに応用できます。