ExcelのVLOOKUP関数とXLOOKUP関数の違い

この記事では、ExcelのVLOOKUP関数とXLOOKUP関数の違いをご紹介します。

VLOOKUP関数を使用してデータ検索をする際、エラーの処理や計算速度の遅さに使いづらさを感じることがあります。

以下では、Excel 2021から導入されてVLOOKUP関数の弱点を克服したXLOOKUP関数のメリットや注意点などを詳しくご紹介します。

VLOOKUP関数とは？

上記の画像ように、VLOOKUP関数はデータを縦方向に検索して、検索値に一致する同じ行の値を指定した列から抽出します。書式は「=VLOOKUP（検索値,範囲,列番号,[検索の型]）」です。

ビジネスシーンで広く使われる一方で、使い方の制限やエラー処理が発生した場合にはほかの関数を組み合わせる必要があるため、応用する際に複雑になることもあります。

VLOOKUP関数の引数の説明や使い方の詳細は、以下の記事でご紹介しています。

ExcelのVLOOKUP関数の使い方｜指定の列と同じ行にある値を返す

XLOOKUP関数とは？

上記の画像ように、XLOOKUP関数は縦方向または横方向にデータ検索をする関数です。範囲または配列を検索して、検索値に一致する値を抽出します。

書式は「=XLOOKUP(検索値,検索範囲,戻り配列,[見つからない場合],[一致モード],[検索モード])」です。

XLOOKUP関数は、Excel 2021から追加された新しい関数なので、「Excel 2021」以降のバージョンと「Microsoft 365」以外は使用できません。

XLOOKUP関数の引数の説明や使い方の詳細は、以下の記事「XLOOKUP関数の基本的な使い方」セクションでご紹介しています。

ExcelのXLOOKUP関数の使い方｜縦方向/横方向を両方検索する

ExcelのVLOOKUP関数とXLOOKUP関数の違い

以下のテーブルにVLOOKUP関数とXLOOKUP関数の違いについてまとめています。

以下では、紹介したVLOOKUP関数とXLOOKUP関数の違いや注意点を詳しくご説明します。

検索値について

検索値は、検索キーとなる指定した文字列またはセル参照のことです。VLOOKUP関数とXLOOKUP関数は、検索値を探す列の指定に違いがあります。

VLOOKUPの場合

VLOOKUP関数を使う際、特に不便になるのが検索列の制約です。

VLOOKUP関数は、範囲の「左端」が自動的に検索列になります。

検索列が2列目以降にある場合、列番号に負の数を指定することができないので、検索列より左側を検索することができません。

例えば「商品コード」を検索値として「価格」を抽出したい場合、左側の表は検索列が「左端」にあるので、「商品コード」1002に紐づく「価格」110がC10セルに表示されています。

しかし、右側の表は検索列が「2列目以降」にあり、列番号で-1を指定しているので#VALUE!エラーが表示されています。

XLOOKUPの場合

XLOOKUP関数は、検索列の位置に指定はありません。

検索値が左端にない場合でも、検索範囲を正しく指定すれば検索列より左側を検索することが可能です。

列を入れ替えたりする必要がないので、VLOOKUP関数よりも扱いやすいです。

検索範囲（範囲）について

検索範囲（範囲）は、検索値を探すセルの範囲のことです。VLOOKUP関数とXLOOKUP関数は、範囲選択の指定に違いがあります。

VLOOKUPの場合

上記の画像のB4セルからE6セルがVLOOKUP関数で指定する範囲です。

VLOOKUPの範囲は、検索列から値を抽出したい列（例：4列目）までをすべて選択する必要があります。

ただし、選択範囲が広い場合は計算に時間がかかり、ファイル自体も重くなるため、膨大なデータを扱う場合はおすすめできません。

XLOOKUPの場合

上記の画像のB4セルからB6セルがXLOOKUP関数で指定する範囲です。

XLOOKUPは、表の範囲に関わらず検索する範囲または配列だけを選択することができるので、データ数が多い場合におすすめです。

ただし、引数の「戻り配列」と行数や列数をそろえる必要があるので、ご注意ください。

また、XLOOKUP関数は検索範囲を複数選択できるので、検索値が2つ以上ある場合に複数条件を指定することができます。

上記の画像は、検索範囲に指定する「商品群」と「商品タイプ」を&で繋いで複数指定している例です。

VLOOKUP関数の範囲は1つしか指定できないため、重複している値を検索するには、作業用の列に「商品群」と「商品タイプ」を結合させたセルの値（例：=B4&C4）を表示させて検索する必要がありました。

しかし、XLOOKUP関数は範囲を複数指定できるので数式や列を挿入する手間がなくなりました。

戻り配列（列番号）について

XLOOKUP関数の「戻り配列」とは、VLOOKUP関数では「列番号」を意味します。

以下で、列指定の違いをご紹介します。

VLOOKUPの場合

上記の画像は、VLOOKUP関数の列番号の指定例です。

VLOOKUP関数の列番号は、範囲の左側から数えて抽出したい列が何列目にあるのかを指定します。

列数が多い場合は、指定したセルの列番号を返すCOLUMN関数などを組み合わせると列番号を取得することができます。

COLUMN関数で列番号を取得する方法は、以下の記事でご紹介しています。

ExcelのVLOOKUP関数の列番号をCOLUMN関数で自動にする

XLOOKUPの場合

上記の画像は、XLOOKUP関数の戻り配列の指定例です。

XLOOKUP関数は、抽出する列の範囲または配列を指定することができます。

また、単独で範囲を指定できるため、VLOOKUP関数のように列の削除をした場合にエラーが返されたり、数式をコピーする際に列番号を手動で修正する手間がなくなりました。

ただし、引数の「検索範囲」と行数をそろえる必要があるので、ご注意ください。

見つからない場合について

XLOOKUP関数の引数である「見つからない場合」は、検索値が見つからない場合に表示される#N/Aエラーを指定した文字列などに変換することです。

VLOOKUPの場合

VLOOKUP関数は、単独でエラー処理をする機能がありません。

ただし、#N/Aエラーだけを指定した文字列などに変換するIFNA関数と組み合わせることで、エラー処理をすることができます。

詳細は、以下の記事「ExcelのVLOOKUP関数で#N/Aエラーを表示させない方法」セクションでご紹介しています。

ExcelのVLOOKUP関数で#N/Aエラーを表示させない方法

XLOOKUPの場合

上記の画像は、検索値が見つからない場合に「該当なし」とC10セルに表示するように指定した例です。

「戻り配列」に続いてダブルクォーテーション内の文字列を指定することで、検索値が検索範囲にない場合、任意の文字列を表示することができます。

「見つからない場合」は省略可能な引数なので、指定しない場合は「#N/A」が表示されます。

一致モード（検索方法）について

XLOOKUP関数の「一致モード」とは、VLOOKUP関数では「検索方法」を意味します。

どちらも省略可能な引数ですが、省略した場合の「一致」の意味が変わるので注意が必要です。

VLOOKUPの場合

VLOOKUP関数の検索方法は以下の2つです。

VLOOKUP関数は、検索方法を省略可すると近似値を返す「TRUE」がデフォルトで選択されます。

ただし、検索値と完全に一致する値を抽出する「FALSE」を指定するのが基本になるので、検索方法は「FALSE」を指定しましょう。

XLOOKUPの場合

XLOOKUP関数の検索方法は以下の4つです。

XLOOKUP関数は、検索方法を省略すると完全に一致する値を返す「1」がデフォルトで選択されます。

基本的に「1」以外を選択しないので、省略しても問題ありません。

検索モードについて

検索モードとは、XLOOKUP関数でデータ検索をする際に検索する方向などを指定します。

VLOOKUPの場合

VLOOKUP関数は、検索値に一致する値を縦方向に検索するので、下から上に検索することはできません。

ただし、COUNTIF関数を組み合わせることで下から上に検索した場合と同じ結果を返すことができます。

詳細は、以下の記事でご紹介しています。

ExcelのVLOOKUP関数で下から検索する方法

XLOOKUPの場合

XLOOKUP関数の「検索モード」は以下の4つです。

バイナリ検索（バイナルサーチ）とは、中央値を基準にして検索範囲を絞って効率的に探索を行うことです。

例えばデータを昇順にした後で「2」を指定した場合、検索値が中央値より小さければデータの上半分だけを検索し、検索値が中央値より大きければデータの下半分だけを検索するので、計算時間が短くなります。

上記の画像は、「検索モード」で-1を指定して重複した検索値を下から検索した例です。

VLOOKUP関数では不可能だった下からの検索が簡単にできるようになりました。

ただし、下からデータ検索をする機会は基本的にありませんので、上から下に検索したい場合はデフォルトで指定されているので省略可能です。

VLOOKUP関数とXLOOKUP関数はどちらが使いやすいのか

VLOOKUP関数のデメリットなどを解消したXLOOKUP関数の方が便利です。

以下は、VLOOKUP関数と比較した場合のXLOOKUP関数のメリットです。

• 縦横どちらの検索もできる
• 左端以外を検索列に指定できる
• 2つ以上の範囲を選択できるので複数条件を指定できる
• #N/Aエラーの場合に任意の文字列を指定できる
• 表の下から上方向に検索ができる
• 完全一致する値を返すので、一致モード（検索方法）を省略できる
• 戻り配列だけを指定するので、削除や挿入をした場合にエラーが表示されない
• 検索列と戻り配列を単独でそれぞれ選択できるので、計算が速い

ただし、XLOOKUP関数は「Excel 2021」以降のバージョンまたは「Microsoft 365」のみで使用可能なので、互換性の問題などがあります。

VLOOKUP関数をXLOOKUP関数に置き換えた場合に、XLOOKUP関数に対応していないバージョンを使っている人がファイルを開いた際に正しく機能しなくなってしまうので注意が必要です。