XLOOKUP関数とは
XLOOKUP関数は、Microsoft 365のExcelで使える新しい検索関数です。従来のVLOOKUP関数やINDEX＋MATCH関数の代替として設計されており、検索値と戻り値の範囲を指定するだけなので、列番号を数える必要がありません。
たとえば「商品IDから商品名を調べる」「社員番号から部署名を取り出す」「顧客コードから担当者を引く」といった操作が、直感的な式で書けます。実務では、見積書・請求書の作成、在庫管理表の照合、名簿の検索など、検索処理のほぼすべての場面で活用できます。
基本的な使い方
書式は =XLOOKUP(検索値, 検索範囲, 戻り範囲) の3つが基本です。第4引数以降は省略できます。
引数の意味
- 検索値：探したい値（例：商品ID「A102」）
- 検索範囲：検索値を探す縦1列（または横1行）の範囲
- 戻り範囲：見つかった位置に対応する値を取り出す範囲
- 見つからない場合（省略可）：#N/Aの代わりに表示する文字列
入力手順
- 結果を表示したいセルを選択する
- =XLOOKUP( と入力し、検索値のセルを指定する
- 検索範囲（ID列）をカンマで区切って指定する
- 戻り範囲（取り出したい列）を指定してEnterを押す
検索値・検索範囲・戻り範囲の3つだけなので、VLOOKUPの「3番目の引数に列番号を指定する」ルールを覚える必要がありません。列の間に行を挿入しても、範囲参照は自動で追従するため式が壊れません。
VLOOKUPとの主な違い
| 項目 | VLOOKUP | XLOOKUP |
|---|---|---|
| 列番号の指定 | 必要（何列目か数える） | 不要（戻り範囲で直接指定） |
| 左方向の検索 | 不可（右側のみ） | 可能（左右どちらでも） |
| 見つからない場合 | #N/Aエラー | 第4引数で任意の文字を表示 |
| 列の挿入で式が壊れる | 列番号がずれて壊れる | 範囲参照なので安全 |
| 完全一致の指定 | 第4引数にFALSEが必要 | 既定で完全一致 |
特に「左方向の検索ができる」点は大きく、ID列が右・名前列が左という表でもそのまま検索できます。これまでINDEX＋MATCH関数の長い式で書いていた処理が、XLOOKUPなら1つの短い式で完結します。
一致モードと検索モード（第5・第6引数）
第5引数の一致モードでは、完全一致以外の検索も指定できます。
- 0：完全一致（既定）
- -1：完全一致、なければ次に小さい値
- 1：完全一致、なければ次に大きい値
- 2：ワイルドカード（*と?）を使った検索
たとえば「料率表で金額に応じた区分を探す」ときに-1を指定すると、上限金額を跨ぐ検索が1つの式で書けます。第6引数の検索モードでは、末尾から検索（-1）やバイナリ検索（2、-2）を指定でき、大量データの高速検索に役立ちます。
実務で役立つ活用例
例1：複数列をまとめて取り出す
戻り範囲に複数列（例：B2:D100）を指定すると、結果が横方向にスピルします。商品IDから「商品名・単価・在庫」を一度に取り出せるので、見積書の明細作成が大幅に速くなります。
例2：IFERROR関数なしでエラー処理する
VLOOKUPではIFERROR関数で包む必要があった#N/A対策も、XLOOKUPでは第4引数だけで済みます。式が短くなり、可読性も上がります。
例3：日付の範囲から該当区分を探す
一致モード-1と組み合わせると、「締切日を過ぎているか」「年齢層どの区分か」のような閾値判定を、判定表を参照する形でメンテナンスしやすく実装できます。
よくあるエラーと対処法
#N/Aエラーが出る
検索値が存在しない場合に表示されます。第4引数に「該当なし」などの文字列を指定すれば、エラーの代わりに表示を切り替えられます。
#NAME?エラーが出る
XLOOKUP関数はMicrosoft 365・Excel for web・Excel 2021以降でのみ利用できます。Excel 2019以前では代替としてINDEX＋MATCH関数を使いましょう。
検索値に似た文字がヒットしない
検索値の前後に空白が入っていると一致しません。TRIM関数で空白を除去してから検索してください。また、見た目が同じ数字でも、片方が「文字列」でもう片方が「数値」の場合も一致しません。セルの書式を統一するか、VALUE関数・TEXT関数で型を合わせてください。
表の中身が変わると結果がおかしい
検索範囲を行まで含めて固定したい場合は、$付きの絶対参照（例：$A$2:$A$100）を使いましょう。テーブル機能（Ctrl+T）で表化すれば、行追加にも自動で追従します。
よくある質問
Q. XLOOKUPで該当する行をすべて取り出せますか？
A. 標準では先頭の1件だけが返ります。該当行をすべて取り出したい場合は、FILTER関数を使ってください。
Q. 大文字・小文字は区別されますか？
A. 区別されません。「ABC」と「abc」は同じ値として扱われます。区別したい場合はEXACT関数と組み合わせた配列式が必要です。
Q. スマホのExcelでも使えますか？
A. ExcelモバイルアプリとExcel for webでは利用できます。ただし、古いデスクトップ版でファイルを開くと@付きに変換される点に注意してください。
まとめ
XLOOKUP関数は、列番号不要・左方向検索OK・エラー対策も引数1つでできる、VLOOKUPの後継となる関数です。一致モードや検索モードまで含めれば、INDEX＋MATCH関数で処理していた高度な検索もすべて1つの式で書けます。まだVLOOKUPの列番号ずれに悩んでいる方は、この機会に切り替えを検討してみてください。
関連記事
- Excel VLOOKUP関数で複数の一致を処理する方法
- ExcelのLOOKUP関数で値を検索 – 使い方と例
- Excel VLOOKUP関数でIF条件：条件付きで検索！
- Excel VBAでVLOOKUP関数：マクロでデータ検索！