Excel関数のなかでも、従来から使われてきた「VLOOKUP関数」は多くの方にとっておなじみですが、
Excel 365やExcel 2021以降では、その進化版ともいえる「XLOOKUP関数」が利用できるようになりました。
XLOOKUPは、VLOOKUPの弱点を解消し、より柔軟でミスの起きにくい検索ができる新しい関数です。
ただ、初めて使うと「値が見つからない」「0が表示される」「複数条件で使いたいのにうまくいかない」など、
細かいつまずきも多く、“結局VLOOKUPでいいや…”と諦めてしまうケースも少なくありません。
この記事では、XLOOKUP関数の基本的な使い方から、よくあるトラブルの原因と対処法までを初心者向けにやさしく解説します。
記事内では以下のような疑問にも触れています。
-
「XLOOKUPってどのバージョンから使えるの？」
-
「見つからないのに #N/A が出ない／空白にならない」
-
「重複データや複数条件で使うには？」
-
「エラーや表示形式の調整はどうする？」
さらに、より詳しく知りたい方向けに関連記事リンクも用意しています。
これからXLOOKUPを本格的に活用したい方のスタートガイドとして、ぜひ参考にしてみてください！
XLOOKUP関数とは？VLOOKUPとの違いと対応バージョン
XLOOKUP関数の基本構文とできること
XLOOKUP関数は、指定した検索値に一致するデータを表の中から探し、対応する値を返す関数です。
従来のVLOOKUPやHLOOKUPに代わる新しい検索関数として、Excel 365やExcel 2021以降で利用できます。
基本構文は以下の通りです：
| 引数 | 意味 |
|---|---|
| 検索値 | 探したいデータ（例：商品コードなど） |
| 検索範囲 | 検索値を探す対象の範囲（縦でも横でもOK） |
| 戻り値の範囲 | 一致した行や列に対応する結果を返す範囲 |
| 見つからない場合 | 該当がなかったときに返す値（省略可） |
| 一致モード | 完全一致／近似一致などの指定（省略可） |
| 検索モード | 上から／下から／バイナリなど検索方法（省略可） |
一致モード（第4引数）の指定について
XLOOKUP関数の第4引数「一致モード」は、検索値とどのように一致させるかを指定するオプションです。省略した場合は「完全一致（0）」になりますが、ほかにも以下のような指定が可能です。
| 一致モード | 内容 | 使用例・用途 |
|---|---|---|
0（省略可） |
完全一致 | 社員番号や商品コードのように、ぴったり一致した値を探したいとき |
-1 |
完全一致がない場合、次に小さい値を返す（検索対象は昇順に並んでいる必要あり） | テストの点数や売上などの評価ランク分け（30点以上はD、50点以上はCなど） |
1 |
完全一致がない場合、次に大きい値を返す（検索対象は降順に並んでいる必要あり） | 減点式の評価や割引条件など |
2 |
ワイルドカード一致（*や?が使用可能） |
部分一致検索や「商品名に“リンゴ”が含まれるもの」などのあいまい検索に便利 |
検索モード -1 や 1 の使いどころ
-1 や 1 は、検索値に完全一致がなかった場合に「近い値」を探す用途で使います。
ただし、データが昇順（-1）・降順（1）に正しく並んでいる必要があり、実務ではやや上級向けの使い方です。
普段の業務では 0（完全一致）を指定するのが基本ですが、
段階評価や料金表のような範囲に基づく検索では -1 が便利な場面もあります。
たとえば、以下のように80点という得点に応じて成績ランクを自動判定したい場合：
このように-1を指定すれば、80点にぴったり一致する値がなくても、**それ以下で最大の値（ここでは70）**に一致する「B」が返されます。
検索モード2について
ワイルドカード一致では、以下の記号が使えます。
-
*（アスタリスク）… 任意の文字列
例："A10*0"→A1000,A1010などに一致 -
?（クエスチョン）… 任意の1文字
例："A10?"→A100,A101,A109などに一致 -
~（チルダ）…*や?を文字として扱いたいとき
例："A10~*"→A10*という文字列に一致
注意点：
検索対象と検索値の両方が「文字列形式」である必要があります。数値形式の列にワイルドカードを使ってもうまく一致しないことがあるため、データの形式を揃えておくのがおすすめです。
検索モード（第6引数）の指定について
XLOOKUP関数の第6引数「検索モード」は、どこから検索を始めるか・どのような順で検索するかを指定するオプションです。通常は省略しても動きますが、「重複データがある」「末尾に近いデータを優先したい」といったケースで活用できます。
| 検索モード | 内容 | 主な用途 |
|---|---|---|
1（省略時） |
上から下へ順番に検索 | 一般的な使い方。最初に一致した値を返す |
-1 |
下から上へ逆順で検索 | 重複がある中で、最後に出てくる値を優先したいとき |
2 |
バイナリ検索（昇順） | 昇順に並んだ大量データの高速検索（データ要件あり） |
-2 |
バイナリ検索（降順） | 降順に並んだ大量データの高速検索（データ要件あり） |
▼ポイント解説：
-
通常は「1」または省略でOKです。
-
データの中に同じ値が複数あるとき、「1」なら先に出てきたもの、「-1」なら後に出てきたものを返します。
-
「2」や「-2」はデータが正しく並んでいる必要があり、検索のスピードを速くしたいときに使います（使う場面は少なめです）。
▼ 使用例（下から検索したい場合）：
上記のように「-1」を指定すれば、「りんご」が複数あるときに、最後に出てきた単価が返されます。
※画像では検索範囲がテーブル化されているため、参照範囲にテーブルの項目名が記載されています。
昇順・降順に正しく並んだデータでないと、誤った結果が出る可能性があります。初心者の方はまず「1」や「-1」で使い慣れるのが安心です。
VLOOKUPとの違い（列方向、柔軟性、複数一致など）
XLOOKUP関数は、VLOOKUPの不便な点を解消した機能が多数搭載されています。
代表的な違いを以下にまとめます。
| 項目 | VLOOKUP関数 | XLOOKUP関数 |
|---|---|---|
| 検索方向 | 左から右への一方向のみ | 左右どちらでも検索可能 |
| 戻り値の指定 | 列番号（数値）で指定 | セル範囲で直接指定（列挿入に強い） |
| 見つからない時 | #N/A エラーが出る |
任意の値を表示できる（例：”なし”） |
| 一致条件 | 完全一致か近似一致の選択のみ | あいまい一致やワイルドカードも可能 |
| 検索モード | 固定（上から順に検索） | 上から／下からの選択が可能 |
| 対応する軸 | 縦方向検索が基本 | 縦・横どちらでも対応 |
VLOOKUPでは「左に戻れない」「列挿入で壊れる」「複数条件に対応しにくい」といった制限がありましたが、
XLOOKUPではこれらの弱点が大きく改善されているのが特徴です。
XLOOKUPが使えるバージョンと確認方法
XLOOKUP関数は、以下のバージョンで使用可能です：
-
Microsoft 365（旧Office 365）
-
Excel 2021以降（永続ライセンス版）
-
Web版Excel（一部制限あり）
※Excel 2019やそれ以前のバージョンでは使用できません。
使用中のExcelが対応しているか確認するには、=XLOOKUP( と入力してみて、関数候補が表示されるかを確認すると簡単です。
XLOOKUPの便利な使い方とエラー対策まとめ
よくあるエラーと原因【#N/A・#VALUE!・スピルエラー】
XLOOKUP関数を使っていて最も多いのが「正しく設定しているのにエラーが出る」という悩みです。
特に以下のエラーがよく発生します。
| エラー | 主な原因 |
|---|---|
#N/A |
一致するデータが見つからない／スペルや空白の違い |
#VALUE! |
検索範囲や戻り値範囲のサイズが不一致 |
#SPILL! |
結果のスピル先にすでに値が入っている（または結合セルあり） |
それぞれの対処法については、別記事でも詳しく紹介予定ですが、
簡易的に以下のような対応ができます。
-
#N/A 対策：
IFNA関数やIFERROR関数でエラーを制御 -
#VALUE! 対策：検索範囲と戻り値範囲のサイズを一致させる
-
#SPILL! 対策：空白セルを確保する／結合セルを解除する
エラーの種類については以下の記事をご参考ください。
「0を表示しない」「空白にしたい」場合の設定
XLOOKUPの結果に 0 が表示されてしまうと、「本当に0なのか」「エラーなのか」が分かりづらいことがあります。
そんなときは以下のように工夫することで、ゼロやエラー時に空白表示することができます。
■ 表示例（0を空白に）：
=IF(XLOOKUP(検索値, 検索範囲, 戻り値範囲)="", "", XLOOKUP(...))
■ 表示例（エラー時に空白）：
重複データや複数条件での使い方
XLOOKUP関数は、検索値が複数ある場合は最初の一致のみ返す仕様です。
そのため「重複データをすべて取得したい」場合や、「複数条件で絞り込みたい」場合は、工夫が必要です。
■ 重複データの取得には？
-
Excel 365なら
FILTER関数との併用がおすすめ -
INDEX＋MATCH＋SMALLで順番に取得する方法もあります
■ 複数条件の検索には？
※「&」で結合した検索キーを使って、一意の値に変換して検索します。
XLOOKUPが「重い」と感じるときの対策
XLOOKUPは便利ですが、大量データで何百・何千件と参照すると処理速度が落ちることがあります。
原因と対策は以下の通りです。
| 原因 | 対策 |
|---|---|
| 関数のネストが多い | 結果を別セルに分けて計算する |
| 検索範囲が広すぎる | 必要最小限の範囲に限定する（例：A:A → A2:A1000） |
| 同じXLOOKUPを大量に使っている | 計算結果を一時的に値貼り付けして固定する |
他関数との比較（INDEX/MATCH、FILTER）
| 関数 | 特徴・違い |
|---|---|
| XLOOKUP | 高機能でシンプル。柔軟な検索が可能 |
| INDEX＋MATCH | 柔軟だが構文が複雑。古いバージョンでも使える |
| FILTER | 条件に一致する複数データの抽出に最適（Excel 365限定） |
実務では「一つだけ取得＝XLOOKUP」「複数件抽出＝FILTER」「複雑条件＝INDEX＋MATCH」など、目的に応じた使い分けが効果的です。
目的に合った関数を使い分けることで、作業効率も大きく変わってきます。ぜひ今回の内容を日々の業務に活かしてみてください。