「VLOOKUPは使えるけど、左側の列を検索できなくて困った」。そんな経験はありませんか。列番号の管理やIFERRORの記述が手間に感じることもありますよね。
XLOOKUP関数はVLOOKUPの弱点をすべて解消した次世代の検索関数です。この記事では基本の書き方からエラー対処、VLOOKUPとの使い分けまで解説します。
この記事は次のような人におすすめ
– VLOOKUPの制約（左方向検索や列番号管理）に困っている人
– XLOOKUP関数の基本的な書き方を覚えたい人
– VLOOKUPからXLOOKUPへの乗り換えを検討している人
XLOOKUP関数とは？
読み方と基本的な役割
XLOOKUPは「エックスルックアップ」と読みます。名前の「X」には「次世代」「拡張」という意味が込められています。
やっていることはシンプルです。「この値を検索して、対応するデータを持ってきて」という指示を出す関数です。VLOOKUPと目的は同じですが、使い勝手が大きく進化しています。
XLOOKUP関数の対応バージョン
XLOOKUPはExcel 2021およびMicrosoft 365で使える関数です。Excel 2019以前のバージョンでは使えません。
NOTE
会社のパソコンがExcel 2019以前の場合は、VLOOKUP関数やINDEX関数 + MATCH関数で同様のことができます。
XLOOKUP関数の書き方（構文と引数）
基本構文
XLOOKUPの構文はこちらです。
=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])引数は6つあります。必須は最初の3つだけです。残り3つは省略できます。
引数の説明
| 引数 | 必須/省略可 | 意味 | 指定例 |
|---|---|---|---|
| 検索値 | 必須 | 探したい値。セル参照や直接入力 | A2 |
| 検索範囲 | 必須 | 検索値を探す範囲（1行または1列） | B2:B100 |
| 戻り配列 | 必須 | 結果として返したい範囲 | C2:C100 |
| 見つからない場合 | 省略可 | 該当なし時に表示する値 | “該当なし” |
| 一致モード | 省略可 | 検索の一致方法（0/1/-1/2） | 0 |
| 検索モード | 省略可 | 検索する方向（1/-1/2/-2） | 1 |
VLOOKUPの「列番号」がなくなったのが大きな変化です。検索範囲と戻り配列を別々に指定します。列の挿入や削除で数式が壊れません。
第4引数：見つからない場合
検索値が見つからなかったときに返す値を指定できます。
=XLOOKUP(A2, B2:B100, C2:C100, "該当なし")VLOOKUPではIFERROR関数で囲む必要がありました。XLOOKUPなら引数ひとつで済みます。数式がすっきりしますね。
省略すると#N/Aエラーが返ります。実務では「該当なし」や空文字 "" を指定するのがおすすめです。
第5引数：一致モード
検索の方法を4種類から選べます。
| 値 | 動作 | 用途 |
|---|---|---|
| 0（既定） | 完全一致。見つからなければ#N/Aを返す | 通常の検索（実務の9割はこれ） |
| -1 | 完全一致。なければ次に小さい値 | 料金表の下限マッチ |
| 1 | 完全一致。なければ次に大きい値 | しきい値の上限マッチ |
| 2 | ワイルドカード一致（*/? を使用） | 部分一致検索 |
省略時は完全一致（0）になります。VLOOKUPの既定値はTRUE（近似一致）だったので正反対です。これだけでも「変な値が返る」トラブルが減ります。
第6引数：検索モード
検索の方向を指定できます。
| 値 | 動作 | 用途 |
|---|---|---|
| 1（既定） | 先頭から末尾へ検索 | 通常の検索 |
| -1 | 末尾から先頭へ検索 | 最新データの取得 |
| 2 | 昇順バイナリ検索 | 大量データの高速検索 |
| -2 | 降順バイナリ検索 | 大量データの高速検索 |
VLOOKUPは常に上から下への一方向検索でした。XLOOKUPなら -1 で末尾から検索できます。更新履歴から最新データを取りたいときに便利です。
XLOOKUP関数の基本的な使い方
商品コードから商品名を取得する
B列〜D列に商品マスタがあるとします。B列が商品コード、C列が商品名、D列が価格です。
F2に入力したコードから商品名を取得するにはこう書きます。
=XLOOKUP(F2, B2:B100, C2:C100)VLOOKUPとの違いは「列番号がない」ことです。「B列で探して、C列を返す」と直感的に読めます。
価格を取得したい場合は、戻り配列をD列に変えるだけです。
=XLOOKUP(F2, B2:B100, D2:D100)別シートのデータを参照する
検索先が別シートにある場合も書き方は同じです。
=XLOOKUP(A2, マスタ!B:B, マスタ!C:C, "該当なし")VLOOKUPのように範囲全体をひとつで指定する必要がないため、列がズレる心配もありません。
左方向にあるデータを検索する
XLOOKUPの最大のメリットのひとつです。「右の列で検索して左の列を返す」が簡単にできます。VLOOKUPでは不可能でした。
たとえばC列の商品名で検索して、B列の商品コードを取得する場合です。
=XLOOKUP(F2, C2:C100, B2:B100)検索範囲と戻り配列の指定を入れ替えるだけです。VLOOKUPでこれをやろうとするとINDEX+MATCHが必要でした。
XLOOKUP関数の実務活用パターン
スピルで複数列を一括取得する
XLOOKUP関数はスピルに対応しています。戻り配列に複数列を指定すると、結果が隣のセルに自動的にあふれ出ます。
=XLOOKUP(F2, B2:B100, C2:E100)この数式だけで商品名・価格・在庫数を一度に取得できます。VLOOKUPでは列ごとに数式を書く必要がありました。
TIP
スピルの結果が表示される先のセルにデータがあると#SPILL!エラーになります。出力先のセルは空にしておいてください。
末尾から検索して最新データを取得する
同じ検索値が複数回登場するデータで最新のレコードを取得したい。更新履歴などで使える場面です。
検索モードに -1 を指定します。
=XLOOKUP(F2, A2:A1000, B2:B1000, "データなし", 0, -1)VLOOKUPは常に先頭から検索するため、最初のデータしか返せませんでした。
ネストして2段階検索する
部署コードから部署名を取得し、さらに担当者を取得する。こうした2段階の検索もネスト（入れ子）で対応できます。
=XLOOKUP(XLOOKUP(A2, 部署!A:A, 部署!B:B), 担当者!A:A, 担当者!B:B)内側のXLOOKUPの結果を、外側のXLOOKUPの検索値に渡しています。
ワイルドカードで部分一致検索する
一致モードに 2 を指定すると、ワイルドカードが使えます。
=XLOOKUP("*東京*", A2:A100, B2:B100, "該当なし", 2)* は任意の文字列、? は任意の1文字にマッチします。セル参照を使う場合は & で連結します。
=XLOOKUP("*"&E2&"*", A2:A100, B2:B100, "該当なし", 2)複数条件で検索する
XLOOKUPで複数条件を使うには、検索値と検索範囲を & で連結します。
=XLOOKUP(E2&F2, A2:A100&B2:B100, C2:C100, "該当なし")VLOOKUPのように補助列を作る必要がありません。Microsoft 365ではそのままEnterで動作します。Excel 2021では Ctrl+Shift+Enter が必要な場合があります。
VLOOKUPとXLOOKUPの違い
ここがこの記事のポイントです。両方の関数を比較表で整理します。
| 比較項目 | VLOOKUP | XLOOKUP |
|---|---|---|
| 検索方向 | 右方向のみ | 左右どちらも可能 |
| 列の指定方法 | 列番号（数値） | 戻り配列（範囲指定） |
| 列追加時の影響 | 番号がズレて壊れる | 影響なし |
| エラー時の処理 | IFERRORが必要 | 第4引数で指定可能 |
| 既定の一致方式 | 近似一致（TRUE） | 完全一致（0） |
| 逆方向検索 | 不可 | 検索モード -1 で可能 |
| スピル対応 | 非対応 | 対応（複数列を一括返却） |
| 複数条件 | 補助列が必要 | &連結で対応可能 |
| 対応バージョン | 全バージョン | Excel 2021 / 365 のみ |
XLOOKUPを使うべき場面
以下に当てはまるなら、XLOOKUPがおすすめです。
- Excel 2021またはMicrosoft 365を使っている
- 左方向のデータを検索する必要がある
- 列の挿入・削除が頻繁にあるシートで使う
- IFERRORを毎回書くのが手間
- 1つの数式で複数列のデータを取得したい
VLOOKUPを使い続けてよい場面
一方、こんな場面ではVLOOKUPのままで問題ありません。
- Excel 2019以前のバージョンを使う人とファイルを共有する
- 既存のシートにVLOOKUPが多数あり、書き換えるコストが大きい
- 右方向の単純な検索だけで足りている
詳しい使い方はVLOOKUPの解説記事をご覧ください。
INDEX+MATCHとの使い分け
INDEX関数 + MATCH関数はVLOOKUPの弱点を補う定番テクニックです。XLOOKUPとの違いを整理します。
| 比較項目 | INDEX+MATCH | XLOOKUP |
|---|---|---|
| 左方向検索 | 可能 | 可能 |
| 数式の読みやすさ | やや複雑（2関数の組み合わせ） | シンプル（1関数で完結） |
| エラー時の既定値 | IFERRORが必要 | 第4引数で指定可能 |
| 対応バージョン | 全バージョン | Excel 2021 / 365 のみ |
| スピル対応 | 工夫が必要 | 標準対応 |
Excel 2021以降ならXLOOKUPがシンプルです。古いバージョンとの互換性が必要なら、INDEX+MATCHを選んでください。
よくあるエラーと対処法
#N/Aエラー
「検索値が見つからない」という意味のエラーです。
| 原因 | 対処法 |
|---|---|
| 検索値がデータに存在しない | 第4引数で「該当なし」等を指定する |
| データ型の不一致（数値と文字列） | VALUE関数で型を統一する |
| 余分なスペースが含まれている | TRIM関数でスペースを除去する |
| 全角と半角が混在している | ASC関数で半角に統一する |
TIP
第4引数を設定しておけば#N/Aエラーそのものを回避できます。VLOOKUPでIFERRORを使っていた方には嬉しいポイントです。
#VALUE!エラー
検索範囲と戻り配列のサイズが一致していないときに出ます。
=XLOOKUP(A2, B2:B100, C2:C50)この例では検索範囲が99行なのに戻り配列が49行です。両方の行数を揃えてください。
#SPILL!エラー
スピルの出力先にデータがあると発生します。結果が展開されるセルをすべて空にしてください。
#NAME?エラー
対応していないバージョンで使うと表示されます。Excel 2019以前では利用できません。
対処法は以下の2つです。
間違った値が返る
エラーにはならないのに期待と違う値が返るケースです。
| 原因 | 対処法 |
|---|---|
| 検索範囲に絶対参照をつけ忘れている | F4キーで$記号を付ける |
| 戻り配列の指定が間違っている | 返したいデータの列を再確認する |
| 一致モードの指定ミス | 通常は0（完全一致）を使う |
まとめ
XLOOKUP関数のポイントを振り返ります。
- 構文は
=XLOOKUP(検索値, 検索範囲, 戻り配列)の3引数が基本 - 列番号が不要なので、列の追加・削除で壊れない
- 左方向検索が標準でできる
- 第4引数でエラー時の表示を指定でき、IFERRORが不要
- スピル対応で複数列を一括取得できる
- 対応バージョンはExcel 2021とMicrosoft 365のみ
VLOOKUPの弱点を解消した次世代の関数です。使える環境であれば、まずはXLOOKUPを試してみてください。
関連記事
- ExcelのVLOOKUP関数の使い方｜引数・エラー対処・INDEX+MATCHとの使い分けまで
- ExcelのMATCH関数の使い方｜検索値の位置を取得する方法
- INDEX関数の使い方
- IFERROR関数の使い方
- ExcelのTRIM関数の使い方｜余分なスペースを一括削除する方法
- VALUE関数の使い方
- アルファベット順 Excel関数一覧
- 機能別 Excel関数一覧
エラー値についてのまとめ記事
関数でエラーが発生した際に表示されるエラーの種類と対処方法は、以下の記事でまとめています。
