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

いま動いている 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 は既定が完全一致なので、乗り換えた結果が変わったのではなく、正しくなったということです。