【Excel 関数】VLOOKUPとXLOOKUPの違い — 乗り換えるタイミングと書き換えの手順

この記事は約10分で読めます。

いま動いている VLOOKUP を、わざわざ XLOOKUP に書き換える必要があるのか。ネットでは「XLOOKUP のほうが新しい」としか書かれていないことが多く、判断材料になりません。この記事では両者の違いを「何が壊れにくくなるか」で整理し、乗り換えてよい場面とそのままにしておく場面を分けます。

この記事で分かること

  • VLOOKUP と XLOOKUP の違いを、引数の形ではなく「どこで壊れるか」で整理した内容
  • 列番号の指定をやめると、表のメンテナンスがどう変わるか
  • 乗り換えてよい場面と、VLOOKUP のまま残したほうがよい場面の判断基準
  • 既存の VLOOKUP を書き換える4ステップの手順

どんな場面で使うか

次のような「毎回ヒヤッとする作業」が起きているなら、乗り換えの検討時期です。

  • マスターに列を1列足したら、参照していた集計表の値がまとめてずれた
  • マスターの左側にある列を引きたいのに、キー列を動かせず諦めている
  • #N/A を IFERROR で隠しているが、本当に未登録なのか数式の間違いなのか分からない
  • 検索値が複数ヒットするとき、いちばん新しい行を取りたいのに毎回先頭が返る

逆に、配布先の Excel のバージョンがそろっていない場合は、乗り換えないほうが安全なこともあります。その判断基準も後半で扱います。

基本説明

この記事のサンプルは「注文一覧」シートと「商品マスター」シートの2枚構成です。まず、操作する元データの姿を確認します。

「注文一覧」シートは、注文ID・商品コード・数量だけを持った1行1件の表です。

行 A列（注文ID） B列（商品コード） C列（数量）
2 1001 A-101 10
3 1002 B-201 2
4 1003 A-103 25
5 1004 C-301 6
6 1005 B-202 1
7 1006 A-105 4
8 1007 A-102 12
9 1008 B-201 3

「商品マスター」シートは、商品コードをキーにした6行のマスターです。行7が最終行になります。

行 A列（商品コード） B列（商品名） C列（単価） D列（区分）
2 A-101 A4コピー用紙 480 事務用品
3 A-102 ボールペン黒 120 事務用品
4 A-103 クリアファイル 95 事務用品
5 B-201 マウス 1980 PC周辺
6 B-202 キーボード 3480 PC周辺
7 C-301 ホワイトボードマーカー 150 事務用品

2枚は別シートなので、同じ列記号でも指している内容が違います。数式では必ずシート名を付けて書きます。なお注文一覧の行7にある A-105 は、商品マスターに存在しない廃番コードとしてわざと混ぜてあります。

やりたいことは同じで「商品コードで商品名と単価を引く」だけです。同じ処理を2つの関数で書くと、次のようになります。

=VLOOKUP($B2,商品マスター!$A$2:$D$7,2,FALSE)
=XLOOKUP($B2,商品マスター!$A$2:$A$7,商品マスター!$B$2:$B$7)

VLOOKUP は「表全体を渡して、左から何列目かを番号で言う」書き方です。XLOOKUP は「探す列」と「返す列」をそれぞれ範囲で渡します。

比べる点 VLOOKUP XLOOKUP
返す列の指定 左端からの列番号 返す列そのものを範囲で指定
列を挿入したとき キー列より右、返す列の位置までに挿入すると別の列を返す 範囲が自動でずれるので追従する
キーより左の列 返せない 返せる
完全一致 第4引数に FALSE が必要 既定が完全一致
見つからないとき #N/A。隠すには IFERROR を足す 第4引数に表示したい値を書ける
複数列をまとめて返す 数式を列ごとに作る 返す範囲を複数列にすると結果が自動で広がる（スピル）
使えるバージョン ほぼ全て Microsoft 365・Excel 2021 以降

差が出るのは「表を直したあと」です。VLOOKUP の 2 という数字は表の形を覚えているだけなので、キー列より右、返す列の位置までに列を1列挿入すると、数式は何も言わずに別の列を返し始めます。エラーにならないのでその場では気づけません。

手順

既存の数式を1つずつ置き換えていきます。サンプルでは注文一覧の D列に商品名、E列に単価、F列に金額を出します。

ステップ1. VLOOKUPの数式を入れて、いまの結果を控える

配布サンプルのD・E列にはまだ数式が入っていません。まず次のVLOOKUP数式をD2・E2に入れて9行目までコピーします。

D2: =VLOOKUP($B2,商品マスター!$A$2:$D$7,2,FALSE)
E2: =VLOOKUP($B2,商品マスター!$A$2:$D$7,3,FALSE)

書き換える前に、この結果をコピーして別の場所に値貼り付けしておきます。書き換え後に見比べる材料がないと、「同じ結果になっているか」を確認できません。行数が多いときは先頭10行だけで十分です。配布サンプルでは、7行目（A-105）は商品マスターに無いため #N/A になります。この行だけステップ3で表示が変わるので、#N/A のまま控えて構いません。

ステップ2. 列番号を範囲に置き換える

D2 に入れる数式を、次のように書き換えます。第2引数が「探す列」、第3引数が「返す列」です。

D2: =XLOOKUP($B2,商品マスター!$A$2:$A$7,商品マスター!$B$2:$B$7)
E2: =XLOOKUP($B2,商品マスター!$A$2:$A$7,商品マスター!$C$2:$C$7)

E列は単価なので、返す範囲を $C$2:$C$7 に変えるだけです。VLOOKUP のように 2 を 3 に直すのではなく、返したい列を直接指す形になります。探す列の範囲と返す列の範囲は、行数をそろえるのが約束です。ここでは両方とも2行目から7行目です。

ステップ3. 見つからないときの表示を決める

このままだと、商品マスターに無い A-105 の行は #N/A になります。XLOOKUP は第4引数に「見つからなかったときに返す値」を書けます。

D2: =XLOOKUP($B2,商品マスター!$A$2:$A$7,商品マスター!$B$2:$B$7,"マスター未登録")
E2: =XLOOKUP($B2,商品マスター!$A$2:$A$7,商品マスター!$C$2:$C$7,0)
F2: =$C2*$E2

D列は文字で「マスター未登録」、E列は計算を止めないよう 0 にしています。IFERROR で囲む書き方との違いは、数式の書き間違いまで隠してしまわないことです。IFERROR は参照ミスによる #REF! も同じ文言で塗りつぶしますが、XLOOKUP の第4引数は「見つからなかった」ときだけ働きます。

ステップ4. 書き換えないものを決める

全部を書き換える必要はありません。次のどれかに当てはまるなら、VLOOKUP のまま残すほうが安全です。

  • ファイルを社外や他部署に配る。相手の Excel が 2019 以前だと #NAME? になる
  • 動いていて、マスターの列構成を今後も変えない集計表
  • 自分以外の担当者が保守しており、XLOOKUP を読める人がいない

判断の軸は「新しいかどうか」ではなく、その表がこれから変更されるかどうかです。変わらない表の数式を触る理由はありません。

サンプルコードまたは例

ステップ3までの数式を D2:F2 に入れて9行目までコピーすると、次の結果になります。

行 B列（商品コード） C列（数量） D列（商品名） E列（単価） F列（金額）
2 A-101 10 A4コピー用紙 480 4800
3 B-201 2 マウス 1980 3960
4 A-103 25 クリアファイル 95 2375
5 C-301 6 ホワイトボードマーカー 150 900
6 B-202 1 キーボード 3480 3480
7 A-105 4 マスター未登録 0 0
8 A-102 12 ボールペン黒 120 1440
9 B-201 3 マウス 1980 5940

F2:F9 を合計すると 22895 になります。未登録の行はE列を0円にしているため合計は止まりませんが、D列の「マスター未登録」を見れば抜けている行だとすぐ分かります。IFERROR のように結果全体を塗りつぶすわけではありません。

キーより左の列を引く

商品名から商品コードを逆に引く例です。VLOOKUP では列の並びを変えないかぎりできませんが、XLOOKUP は探す列と返す列を別々に指定するので、左右の位置関係を問いません。D〜F列は使用済みなので、空いているH2に入れます。

H2: =XLOOKUP("マウス",商品マスター!$B$2:$B$7,商品マスター!$A$2:$A$7)

結果は B-201 です。

商品名と単価を1つの数式でまとめて返す

返す範囲を複数列にすると、結果が右方向にスピルします。ここまでの手順でE2にはステップ3の数式が入っているため、試す前にE2の数式を消してE2を空けてください（E3以下はそのままで構いません）。そのうえで次の数式をD2に入れると、D2に商品名、E2に単価が同時に入ります。

=XLOOKUP($B2,商品マスター!$A$2:$A$7,商品マスター!$B$2:$C$7)

B2 は A-101 なので、D2 に A4コピー用紙、E2 に 480 が表示されます。この使い方をするときはE2に別の数式を入れないでください。スピルの出口をふさぐことになります。確認できたらD2の数式を消し、D2とE2にステップ3の数式を戻してください。

同じキーが複数ある表で、最後の行を取る

注文一覧では B-201 が行3と行9の2か所にあります。第5引数（一致モード）に 0 を指定して完全一致にし、第6引数に -1 を渡すと末尾から検索します。D〜F列は使用済みなので、ここもI2に入れます。

I2: =XLOOKUP("B-201",注文一覧!$B$2:$B$9,注文一覧!$A$2:$A$9,"",0,-1)

結果は 1008 で、既定の検索（先頭から）なら 1002 が返ります。「最新の1件を取りたい」という用途はこれで満たせます。

よくあるエラー

#NAME? になる

XLOOKUP が搭載されていないバージョンで開いています。数式バーに _xlfn.XLOOKUP と表示されていれば確定です。ファイルを作った側では正しく見えるので、配布して初めて発覚します。相手の環境が分からない場合は VLOOKUP のままにしてください。

#VALUE! になる

探す列の範囲と返す列の範囲で、行数が食い違っています。$A$2:$A$7 に対して $B$2:$B$20 のように、片方だけ広げると起こります。両方を同じ行数にそろえてください。

#SPILL! になる

返す範囲を複数列にしたとき、スピルする先のセルに既に値が入っていると起こります。先の例なら E2 を空にしてから D2 の数式を入れます。

エラーは出ないが、値が合わない

VLOOKUP から書き換えた直後にこれが起きたら、ステップ1で控えた結果と見比べます。多くは「返す列を1列ずれて指定した」もので、エラーにならないぶん見つけにくいのが厄介です。キーは合っているのに値だけおかしいときは、返す範囲の列記号を疑ってください。

見つからないはずなのに何か返ってくる

VLOOKUP 側で第4引数を省略していた場合に起こります。省略すると近似一致になり、検索列が昇順に並んでいる前提で近い行を返します（並んでいなければ結果はさらに不安定です）。書き換え前の数式に FALSE が付いていなかったなら、もともとの結果が間違っていた可能性があります。XLOOKUP は既定が完全一致なので、乗り換えた結果が変わったのではなく、正しくなったということです。

まとめ

  • VLOOKUP の列番号は表の形を覚えているだけなので、キー列より右、返す列の位置までに列を挿入すると静かに壊れる
  • XLOOKUP は「探す列」と「返す列」を範囲で指定するため、表を直しても追従する
  • 見つからないときの表示は第4引数で指定できる。IFERROR と違い、数式の間違いまでは隠さない
  • キーより左の列を引く・複数列をまとめて返す・末尾から検索する、は XLOOKUP だけができる
  • 書き換えの判断は「新しいかどうか」ではなく、その表が今後変更されるかどうかで決める
  • 配布先に Excel 2019 以前が混ざるなら、VLOOKUP のまま残すのが安全

まずは1つの数式だけ書き換えて、ステップ1で控えた結果と見比べるところから始めてみてください。

関連記事

対応バージョン: Microsoft 365 または Excel 2021 以降

サンプルファイル

記事中で使用しているサンプルデータはこちらからダウンロードできます。

タイトルとURLをコピーしました