VLOOKUPとXLOOKUPは、どちらも「表から値を探して取り出す」関数です。やることは同じですが、XLOOKUPはVLOOKUPの弱点をひと通り解消した後継として作られました。
結論から言うと、新しく書くならXLOOKUPです。ただし、VLOOKUPを使うべき場面も残っています。この記事ではその判断基準を整理します。
書き方の比較
同じ処理を両方で書きます。
VLOOKUP
=VLOOKUP("B-201", A2:C100, 2, FALSE)XLOOKUP
=XLOOKUP("B-201", A2:A100, B2:B100)VLOOKUPは「範囲の左から2番目」と位置で指定し、XLOOKUPは「A列を探してB列を返す」と範囲で指定します。
7つの違い
| 観点 | VLOOKUP | XLOOKUP |
|---|---|---|
| 左方向の検索 | できない | できる |
| 列の挿入 | 壊れる | 壊れない |
| 見つからない場合 | IFERROR が必要 |
引数で指定できる |
| 複数列の取得 | 数式を複数書く | 1つで返せる |
| 下から検索 | できない | できる |
| 処理の重さ | 範囲全体を読む | 2列だけ読む |
| 古いExcel | 動く | 動かない |
1. 左方向の検索
VLOOKUPは範囲の左端列しか検索できません。 「商品名から商品コードを引く」ことができません。
XLOOKUPは検索範囲と結果範囲が独立しているので、位置関係を問いません。
=XLOOKUP("ハサミ", B2:B100, A2:A100)VLOOKUPで左方向を検索するには INDEX+MATCH が必要でした。XLOOKUPはそれを1つの関数で置き換えます。
2. 列の挿入で壊れるか
これが実務で最も痛い違いです。
VLOOKUPは「左から3番目」という位置で指定します。表の途中に列を挿入すると、数式が別の列を指してしまいます。エラーにならず、間違った値を返すのが厄介です。
XLOOKUPは範囲そのものを指定するので、列を挿入しても範囲が自動で追従します。
運用中に静かに壊れないという点だけでも、XLOOKUPを選ぶ理由になります。
3. 見つからないときの扱い
VLOOKUPは #N/A を返すので、IFERROR で包む必要があります。
=IFERROR(VLOOKUP(E2, A:C, 2, FALSE), "未登録")XLOOKUPは4つ目の引数で直接指定できます。
=XLOOKUP(E2, A:A, B:B, "未登録")この差は見た目以上に重要です。 IFERROR は数式全体のエラーを握りつぶすので、範囲指定のミスや #REF! まで「未登録」にしてしまいます。
XLOOKUPの第4引数は「見つからなかった場合」だけを扱うため、本当の不具合はエラーとして表面化します。 詳しくは IFERROR のページで扱っています。
4. 複数の列をまとめて返す
XLOOKUPは結果範囲を複数列にできます。
=XLOOKUP("B-201", A2:A100, B2:D100)商品名・価格・在庫が横並びで一度に出ます。VLOOKUPだと3つの数式が必要でした。
5. 下から検索する
同じキーが複数あるとき、VLOOKUPは常に最初の1件を返します。
XLOOKUPは6つ目の引数に -1 を指定すると、最後の1件を返します。
=XLOOKUP(E2, A:A, B:B, "", 0, -1)追記型の履歴表から最新の状態を取り出すという、実務で頻出の処理が1行で書けます。
6. 処理の重さ
VLOOKUPは指定した範囲全体を読みます。A:Z を指定すれば26列すべてです。
XLOOKUPは検索列と結果列の2列しか読みません。行数が数万規模になると体感で差が出ます。
7. 古いExcelとの互換性
ここだけがVLOOKUPの優位点です。
XLOOKUPは Microsoft 365 と Excel 2021 以降でしか動きません。Excel 2019以前で開くと #NAME? になります。
スプレッドシートでは両方とも問題なく使えます。
どちらを使うべきか
| 状況 | 選ぶ関数 |
|---|---|
| スプレッドシートで新規に書く | XLOOKUP |
| Excel 365 / 2021以降で新規 | XLOOKUP |
| Excel 2019以前と共有する | VLOOKUP |
| 取引先にxlsxで渡す（環境不明） | VLOOKUP |
| 既存の数式を保守する | 触らずVLOOKUPのまま |
| 大量データ（数万行以上） | XLOOKUP |
判断基準は互換性の一点です。相手の環境が分からないファイルを配るならVLOOKUP、それ以外はXLOOKUPと考えて構いません。
VLOOKUPを使い続けるなら
互換性のためにVLOOKUPを使う場合、列番号を MATCH で動的にすると、列の挿入で壊れなくなります。
=VLOOKUP($E2, $A:$D, MATCH(F$1, $A$1:$D$1, 0), FALSE)見出し行から列位置を計算しているので、列順が変わっても追従します。VLOOKUPの最大の弱点を、互換性を保ったまま潰せます。
また、4つ目の引数 FALSE は必ず書いてください。 省略すると近似一致になり、存在しない値でもエラーにならず、間違った値が返ります。詳しくは VLOOKUP のページで扱っています。
移行するときの注意
既存のVLOOKUPをXLOOKUPに置き換えるとき、引数の意味が変わる点に注意してください。
VLOOKUP(キー, 範囲全体, 列番号, FALSE)
XLOOKUP(キー, 検索列だけ, 結果列だけ)VLOOKUPの「範囲全体」をそのままXLOOKUPの第2引数に入れると動きません。検索する列だけを指定してください。
まとめ
- – 新規に書くならXLOOKUP。列の挿入で壊れないのが最大の理由
- – 左方向の検索・複数列の取得・下から検索ができる
- – VLOOKUPを選ぶ理由は互換性だけ（Excel 2019以前）
- – VLOOKUPを使うなら、列番号を MATCH で動的にする
- – VLOOKUPの4つ目の引数
FALSEは必ず書く
コメントを残す