XLOOKUP と VLOOKUP の違い|構文の比較と、置き換えなくてよい場面
結論
XLOOKUP は、表から値を探して対応する値を返す検索関数で、VLOOKUP の弱点をいくつか解消した後継的な位置づけの関数です。Microsoft のサポートでも、VLOOKUP のページで「改良版であり、任意の方向で機能し、既定で完全一致を返す」関数として XLOOKUP が案内されています。
主な違いは次の3点です。
- 既定の一致方法:XLOOKUP は完全一致が既定。VLOOKUP は省略すると近似一致になる
- 範囲の指定方法:XLOOKUP は「検索範囲」と「戻り範囲」を別々に指定する。VLOOKUP は1つの表範囲と、その中の列番号で指定する
- 探せる方向:XLOOKUP は検索列の左側にある列の値も返せる。VLOOKUP は範囲の左端の列で検索し、右側の列しか返せない
ただし、XLOOKUP は Excel 2016 および Excel 2019 では使用できないことが公式に明記されています。使える環境かどうかが、使い分けの前提になります。

意味・仕組み
そもそも何をする関数か
どちらも「一覧表の中から、指定した値と同じ行を探し、その行にある別の値を取り出す」関数です。たとえば商品コードを手がかりに、商品マスターから商品名や単価を取り出す、といった使い方をします。
違いは、その指定の仕方と考え方にあります。
VLOOKUP は、表全体を1つの範囲として渡し、「左から何列目の値を返すか」を番号で指定します。検索する値は、その範囲の最初の列に無ければならず、返せるのはそれより右の列の値だけです。
XLOOKUP は、検索する列(検索範囲)と、値を取り出す列(戻り配列)を、それぞれ独立した範囲として渡します。2つの範囲は同じ行数で対応していればよく、戻り配列が検索範囲の左側にあっても構いません。また、戻り配列に複数列を指定すると、複数の値をまとめて返せます。
検索の向きと順序
VLOOKUP は範囲の上から下へ探すのが基本です。XLOOKUP は検索モードを指定でき、先頭から探す(既定)のほか、末尾から逆向きに探すこともできます。同じ検索値が複数行ある表で「最後の1件」を取りたい、といった場面ではこの違いが効きます。
使い方
構文の比較
それぞれの構文は次のとおりです。
=VLOOKUP(検索値, 範囲, 列番号, [検索の型])
=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])
引数を並べて比べると、考え方の違いが見えてきます。
| 項目 | VLOOKUP | XLOOKUP |
|---|---|---|
| 検索値 | 第1引数 | 第1引数(同じ) |
| 範囲の指定 | 表全体を1つの範囲で指定し、検索値はその最初の列に必要 | 検索範囲と戻り配列を別々に指定する |
| 戻す値の指定 | 範囲の中での列番号(左端が1) | 戻り配列そのものを範囲で指定する |
| 既定の一致方法 | 近似一致(第4引数を省略すると TRUE) | 完全一致(一致モード 0 が既定) |
| 検索方向 | 上から下のみ(左端列を検索) | 戻り配列は検索範囲の左右どちらでも可。検索モードで末尾からの検索も可 |
| 見つからない場合 | #N/A エラーになる。表示を変えるには IFERROR 等で包む | 第4引数「見つからない場合」で代わりの値を直接指定できる。省略時は #N/A |
同じ検索を両方で書く
商品コード(セル F2)から、表 B2:D11 の3列目にある単価を取り出す例です。
VLOOKUP では、表全体と列番号で書きます。
=VLOOKUP(F2,B2:D11,3,FALSE)
XLOOKUP では、検索列 B2:B11 と戻す列 D2:D11 を分けて書きます。
=XLOOKUP(F2,B2:B11,D2:D11)
XLOOKUP 側で完全一致の指定が見当たらないのは、完全一致が既定だからです。VLOOKUP で完全一致にするための FALSE の書き忘れは、近似一致で意図しない値が返る事故のもとでした。その心配が減るのは実務上の大きな違いです。
見つからない場合の表示まで含めるなら、XLOOKUP では第4引数に書けます。
=XLOOKUP(F2,B2:B11,D2:D11,"該当なし")
VLOOKUP で同じことをするには、IFERROR で全体を包む形になります。
=IFERROR(VLOOKUP(F2,B2:D11,3,FALSE),"該当なし")
XLOOKUP の利点
- 左側の列も取れる:検索範囲と戻り配列が独立しているため、表の並びを入れ替えなくても、検索列より左にある値を返せます
- 完全一致が既定:第4引数の書き忘れによる近似一致の事故が起きにくくなります
- 列挿入で壊れにくい:VLOOKUP の列番号指定は、表の途中に列を挿入すると指す列がずれてしまいます。XLOOKUP は戻り配列を範囲で指定するため、列番号のずれという壊れ方がありません
- 見つからない場合を数式内で指定できる:IFERROR で包まずに済み、数式が短くなります。また、IFERROR と違い「見つからなかった」以外のエラーを隠しません
使える環境の注意
XLOOKUP は新しい関数であり、Microsoft のサポートには Excel 2016 および Excel 2019 では使用できないと明記されています。手元の環境が Microsoft 365 や Excel 2021 以降かどうかを確認してから使ってください。
古いバージョンの Excel で XLOOKUP 入りのブックを開くと、関数として認識されず、意図した結果が得られないことがあります。取引先や他部署など、開く側の環境が混在するファイルでは、この点が置き換えの判断材料になります。
既存の VLOOKUP を無理に置き換えなくてよい場面
- すでに正しく動いている VLOOKUP の数式が大量にあるブック。動いているものを一斉に書き換える作業自体が、ミスの混入経路になります
- Excel 2016 / 2019 を使う人が開く可能性があるファイル。互換性を優先するなら VLOOKUP のままが無難です
- 表の構造が単純で、列挿入の予定もなく、完全一致の
FALSEも正しく書かれている場合。得られる改善が小さいなら、触らない判断も妥当です
新規に数式を書く場面で、手元も共有先も新しい Excel でそろっているなら、XLOOKUP を選ぶ理由は十分にあります。
つまずき所・よくある失敗
- VLOOKUP で第4引数を省略してしまう:省略時は近似一致になり、検索値と違う行の値が返ることがあります。完全一致にしたい場合は必ず
FALSE(または 0)を指定してください。XLOOKUP にはこの落とし穴がありません - XLOOKUP の一致モードを安易に変える:一致モードの -1 や 1 は「見つからない場合に次に小さい/大きい項目を返す」近似の指定です。料金表の区分判定など意図がある場合以外は、既定の 0(完全一致)のまま使うのが基本です
- 検索モードのバイナリ検索(2 / -2)を並べ替えなしで使う:バイナリ検索は検索範囲が昇順/降順に並べ替えられている前提のモードです。前提を満たさないと無効な結果が返ります。通常は既定の 1 で問題ありません
- 列番号の数え間違い(VLOOKUP):列番号は表範囲の左端を1として数えます。シートの列番号(A=1…)とは別物です。範囲の開始列がずれると、戻る値もずれます
- 環境違いで開いてしまう:前述のとおり、古い Excel では XLOOKUP が使えません。「自分の PC では動いた」で共有してしまうと、相手側で初めて問題に気づく形になります
まとめ
- XLOOKUP は検索範囲と戻り配列を分けて指定し、完全一致が既定、左側の列や末尾からの検索にも対応する
- VLOOKUP は表範囲+列番号の指定で、完全一致には
FALSEの明示が必要。省略すると近似一致になる - XLOOKUP は列挿入で壊れにくく、見つからない場合の値も数式内で指定できる
- ただし Excel 2016 / 2019 では XLOOKUP は使えない。共有先の環境が混在する場合は VLOOKUP のままにする判断も正しい
