VLOOKUP の不満、XLOOKUP でほぼ解決します
Excel で「別の表からデータを引っ張ってくる」とき、長らく VLOOKUP が定番でした。便利なんですが、地味に困るところもあったんですよね。
- 検索列が左端にないと使えない
- 列番号を数えるのが面倒（しかも列を1本追加するとズレて全部やり直し）
- 見つからないと「#N/A」が表示されて見栄えが悪い
これ、PC ショップ時代に「経理の Excel が壊れた」と持ち込まれた案件のうち体感で半分くらいは、列を増やしたあとに VLOOKUP の 2 が 3 になっておらず参照ズレを起こしていたパターンでした。XLOOKUP（エックスルックアップ）なら、上の3つの不満はだいたい解消できます。Microsoft 365 と Excel 2021 以降で使える新しい関数なんです。
XLOOKUP の基本の書き方
まずはいちばんシンプルな書き方から。
=XLOOKUP(検索値, 検索範囲, 戻り範囲)たとえば、商品コード「A001」の商品名を別シートの商品マスタから取得したい場合はこうです。
=XLOOKUP(A2, 商品マスタ!A:A, 商品マスタ!B:B)VLOOKUP と並べてみると違いがわかりやすいです。
| VLOOKUP | XLOOKUP | |
|---|---|---|
| 書き方 | =VLOOKUP(A2, 商品マスタ!A:C, 2, FALSE) | =XLOOKUP(A2, 商品マスタ!A:A, 商品マスタ!B:B) |
| 列番号 | 必要（2列目=2） | 不要（範囲で直接指定） |
| 検索列の位置 | 左端のみ | どこでもOK |
| 見つからない場合 | #N/A エラー | 代替値を設定できる |
XLOOKUP のうれしいポイント3つ
1. 検索列が左にある必要がない
VLOOKUP は検索する列が範囲の一番左にないと使えませんでした。XLOOKUP は検索範囲と戻り範囲を別々に渡す仕様なので、列の物理的な順番を気にする必要がありません。これだけでも、表の作り方の自由度がぜんぜん違ってきます。
2. 見つからないときの代替値を設定できる
#N/A エラーの代わりに「該当なし」と表示したい場合は、第4引数に書くだけです。
=XLOOKUP(A2, 商品マスタ!A:A, 商品マスタ!B:B, "該当なし")これまでは =IFERROR(VLOOKUP(...), "該当なし") と外側で囲む必要があったんですが、それがいらなくなる。地味ですが、業務の Excel ではこれが効きます（自分も最初これを知らずに、IFERROR を二重に重ねて読みづらい数式を量産していた時期がありました）。
3. 完全一致がデフォルト
VLOOKUP の事故でいちばん多かったのが、第4引数の FALSE を書き忘れて近似値マッチになり、おかしな値を返してくるパターン。XLOOKUP は最初から完全一致がデフォルトなので、引数を省略しても安全側に倒れてくれます。
VLOOKUP からの乗り換え手順
「全部 XLOOKUP に書き換えるぞ」と気合いを入れたくなる気持ちはわかるんですが、いきなり一斉置換はおすすめしません。実機で何度かやってみてわかったんですが、共有ファイルだと相手の Excel バージョンが古くて開けなくなる事故が起きます。
- 新しい数式は XLOOKUP で書く。既存の VLOOKUP はとりあえずそのまま
- エラーが出ている VLOOKUP 式を見つけたら、そのタイミングで XLOOKUP に置き換える
- VLOOKUP を含むファイルを他の人に共有する場合は、相手の Excel バージョンを確認（Excel 2019 以前は XLOOKUP 非対応）
注意: Excel 2019 以前や、Google スプレッドシートの一部古い環境では XLOOKUP が動きません。共有先がこれらに該当する場合は、無理に書き換えず VLOOKUP のまま運用するのが無難です（公式互換性は Microsoft サポートの XLOOKUP 関数ページ に記載があります）。
FAQ
XLOOKUP は Google スプレッドシートでも使える？
はい、Google スプレッドシートでも2023年から対応 しています。書き方は Excel と同じです。
VLOOKUP はもう使わなくていい？
新規の数式は XLOOKUP がおすすめですが、VLOOKUP が廃止されるわけではないので、既存ファイルを無理に書き換える必要はありません。「過去の VLOOKUP は触らない、新規だけ XLOOKUP」が、結局のところいちばん事故が少ないやり方です。
XLOOKUP で複数条件検索はできる？
直接は対応していませんが、検索値と検索範囲を & で結合すれば擬似的に複数条件検索ができます。例: =XLOOKUP(A2&B2, 表!A:A&表!B:B, 表!C:C)
参考文献
- XLOOKUP 関数 — Microsoft サポート
- VLOOKUP 関数 — Microsoft サポート
