VLOOKUPを使いこなしている方なら、一度はこんな悩みを感じたことがあるのではないでしょうか。
- 検索列が左端にないとダメなの？
- 列番号を数えるのが面倒・・・
- 列を追加したら数式が壊れた！
これらをすべて解決するのが XLOOKUP関数 です。
Microsoft 365（旧Office 365）で使えるXLOOKUPは、VLOOKUPの弱点を完全に克服した「次世代の検索関数」です。
この記事では、中級者の方に向けてXLOOKUPの構文・実務活用例・VLOOKUPとの違いをわかりやすく解説します。
VLOOKUP関数については下記のまとめ記事を参照下さい。
XLOOKUP関数とは？
XLOOKUP関数は、指定した範囲から値を検索して、対応する別の範囲の値を返す関数です。
2019年にMicrosoft 365向けに登場し、長年使われてきたVLOOKUP関数・HLOOKUP関数を置き換える関数として設計されました。
一言で言うと、「縦でも横でも・左にも右にも・エラー処理まで1つの関数でこなせる万能検索関数」です。
使える環境
- Microsoft 365（Windows・Mac）
- Excel 2021以降
- Excel for the web（ブラウザ版）
- Excel 2019以前（使用不可）
自分の使っているExcelのバージョンが不明な方は「ファイル」→「アカウント」で確認できます。
XLOOKUPの基本構文
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])| 引数 | 必須／任意 | 内容 |
|---|---|---|
| 検索値 | 必須 | 探したい値（セル参照・文字列・数値） |
| 検索範囲 | 必須 | 検索値を探す列（または行） |
| 戻り範囲 | 必須 | 結果として返したい列（または行） |
| 見つからない場合 | 任意 | 一致しないときに返す値（省略するとエラー表示） |
| 一致モード | 任意 | 0=完全一致（既定）、-1=以下、1=以上、2=ワイルドカード |
| 検索モード | 任意 | 1=先頭から（既定）、-1=末尾から、2=昇順バイナリ、-2=降順バイナリ |
最初は必須の3引数だけ覚えればOKです。
まず試したい！基本的な使い方
例：社員番号から氏名を取得する
A列(A2～A5)に社員番号、B列(B2～B5)に氏名が並んでいる表で、E1セルに入力した番号の氏名をF1セルに表示する場合：
=XLOOKUP(E2, A2:A5, B2:B5)これだけです。
VLOOKUPと比べてみましょう。
| VLOOKUP | XLOOKUP | |
|---|---|---|
| 数式 | =VLOOKUP(E1, A2:B5, 2, FALSE) | =XLOOKUP(E1, A2:A5, B2:B5) |
| 列番号の指定 | 必要（2列目→2） | 不要 |
| 検索列の位置 | 左端のみ | どこでも可 |
| エラー処理 | IFERROR関数が別途必要 | 第4引数に書くだけ |
VLOOKUPでは困った3つのシーンで威力を発揮
① 左側の値を取得したいとき
VLOOKUPは「検索列より右の列しか返せない」という制約があります。
たとえばB列で検索してA列の値を取得したい場合、VLOOKUPでは対応できません。
XLOOKUPは検索範囲と戻り範囲を独立して指定できるため、どの方向でも自由です。
例：B列(B2～B5)の社員番号で検索してA列(A2～A5)の氏名を返す場合
=XLOOKUP(D1, B2:B5, A2:A5)
② エラー表示を消したいとき
VLOOKUPで一致しない場合は #N/A が表示され、別途IFERRORで囲む必要がありました。
VLOOKUPの場合：=IFERROR(=VLOOKUP(E1, A2:B5, 2, FALSE), "該当なし")
XLOOKUPの場合：=XLOOKUP(E1, A2:A5, B2:B5,"該当なし")第4引数に表示したいテキストを書くだけで完結します。
③ 列を追加・削除しても壊れないようにしたいとき
VLOOKUPの列番号（3列目→3）は固定値なので、表の列構成が変わると数式がズレて誤動作します。
XLOOKUPは戻り範囲を直接セル範囲で指定するため、列を増減させても数式は自動追従します。
よくあるミスと対処法
| 症状 | 原因 | 対処法 |
|---|---|---|
#N/A が表示される | 検索値が検索範囲に存在しない | 第4引数に代替テキストを設定 |
#VALUE! エラー | 検索範囲と戻り範囲のサイズが合っていない | 両方の行数（または列数）を揃える |
#SPILL! エラー | スピル先のセルが空ではない | 結果が広がる範囲を空にする |
| 意図しない値が返る | 一致モードが「近似一致」になっている | 第5引数を 0（完全一致）に明示する |
まとめ
XLOOKUPのポイントを振り返りましょう。
- 検索範囲と戻り範囲を分けて指定するので、左方向の検索も自由自在
- 第4引数でエラー処理を内包できるため、IFERRORが不要
- 列番号の指定がないので、表の構成変更に強い
- 複数列の戻り範囲＋スピルで1つの数式で複数列を取得できる
- 逆方向検索で最新データの取得も簡単
Microsoft 365またはExcel 2021以降をお使いであれば、今すぐVLOOKUPをXLOOKUPに置き換えることをおすすめします。
最初は3つの引数（検索値・検索範囲・戻り範囲）だけ覚えてしまえば、あとは実務の中で自然と応用が身についていきます。
ぜひ今日から試してみてください！
