【Excel】VLOOKUP関数とXLOOKUP関数の速度比較の事例

最近のVLOOKUP関数は、かなり高速に改善されている印象です。

過去には「データを昇順にして近似一致で検索する方法」と「通常の完全一致での検索」では実感の速度に大きな違いがあったように思いますが、実感するほどの差が少なくなったと感じます。

一方、XLOOKUP関数は検索列と結果列の選択がしやすいなど、VLOOKUP関数と比較して便利な関数ですが、大量データの場合、VLOOKUP関数より速度が少し遅い印象です。

今回、5万件のデータ処理で時間が遅く感じたので、VLOOKUP関数とXLOOKUP関数の処理時間を調べてみました。

データ処理の内容やPC環境等もあるので、一つの事例ですが、結果は次のとおりです。

XLOOKUP関数で複数列の抽出時間にストレスを感じたケースで、「VLOOKUP関数で１列ずつ抽出する方法」に変更すると、かなり時間を短縮できました（詳細は後述）。
検索範囲を昇順に並び替えても良いケースは、XLOOKUP関数の検索モードを「バイナリ検索（二分探索）」にすることで高速化できる。

なお、使用のExcelは「Microsoft® Excel® for Microsoft 365 MSO」です。

VLOOKUP、HLOOKUP、MATCHについての参考情報です。

Office 365 バージョン 1809 以降では、並べ替えなし完全一致で、同じテーブル範囲から複数の列を検索するときの速度が大幅に改善されているとのことです。

今回の事例では、次のとおりだった。

2列の抽出では、XLOOKUP関数より、VLOOKUP関数で１列ずつ抽出が早い。
下記の詳細結果のとおり、検索範囲の並び順が並べ替えなしのテーブルで、
処理時間はXLOOKUPが19秒程に対し、VLOOKUPは1秒程。
１列だけの抽出の場合は、大きな差はない
VLOOKUP関数の検索の型は「完全一致」と「近似一致」で多少の差。
検索範囲を昇順にしておくと早い。XLOOKUP関数は「データを昇順にして近似一致で検索する方法」でより高速化できる。
検索範囲は、「通常の範囲」と「テーブル形式」で速度に大きな差はなかった。

比較の詳細結果は次のとおり。処理時間は3回測定の平均値で算出。

処理時間は、VBAでシート「出力」の2行目に数式を入力後、5万行まで数式コピーした時の時間を計測。
VLOOKUPの数式は複数列を抽出できないため、次のように１列ずつ設定
=VLOOKUP(A2,検索範囲!$A$1:$C$50000,2,FALSE)
=VLOOKUP(A2,検索範囲!$A$1:$C$50000,3,FALSE)
近似一致の場合は、検索の型をTRUEもしくはバイナリ検索とし、次のようにIF文で完全一致のみ抽出
VLOOKUPの場合：
=IF(VLOOKUP(A2,検索範囲!$A$2:$A$50000,1,TRUE)=A2,VLOOKUP(A2,検索範囲!$A$2:$C$50000,2,TRUE),"")

XLOOKUPの場合：
VLOOKUPの近似一致とXLOOKUPのバイナリ検索を組み合わせている。
=IF(VLOOKUP(A2,検索範囲!$A$2:$A$50000,1,TRUE)=A2,XLOOKUP(A2,検索範囲!$A$2:$A$50000,検索範囲!$B$2:$C$50000,,-1,2),"")