結論: XLOOKUP関数は最強
新登場のXLOOKUP関数には以下のようなメリットがあり、VLOOKUP関数の欠点がほとんど解消されています。
- 参照元の表に列を挿入しても、列が崩れない
- 検索キーが検索範囲の右側にあっても検索可能
- 上から一致するものを探すだけでなく、下から一致するものを探すことも可能
さらに、
- 検索パフォーマンスもVLOOKUP関数よりやや早い
ため、もうVLOOKUP関数を使うべきケースはほとんどないです。VLOOKUPさん、今まで長い間お疲れさまでした。これまでVLOOKUPが使われていたような場面では、これからXLOOKUPを使用するのが主流になりそうです。
ただし、
- バイナリ検索のパフォーマンスは微妙
なので、これは現状使う必要がないです。
最強関数 XLOOKUP が満を持してGoogleスプレッドシートに登場
いままで頻繁に頻繁に、それはもう頻繁に、スプレッドシートの主役として使っていたVLOOKUP関数とお別れするときがやってきたようです。
Excelでは2020年から実装されていたVLOOKUP関数の上位互換ともいえるXLOOKUP関数が、2022年9月頃、ついにGoogleスプレッドシートでも実装されました。
Excelではバージョン間の互換性を気にして、便利ながらもなかなか使えないことの多い関数でしたが、バージョン差異を気にする必要のないGoogleスプレッドシートでは、大手を振って使用することができます。
何ができるの？
まずは公式ドキュメントを見てみましょう。こちらの内容をもとにVLOOKUP関数とXLOOKUP関数の違いをまとめると、以下のとおりです。
| VLOOKUP関数 | XLOOKUP関数 | |
|---|---|---|
| ①検索列の指定方法 | 検索する表全体と、検索したい列の番号を指定 | 検索したい列の範囲を直接指定 |
| ②検索方法 | 上から順番のみ。バイナリ検索はできない*1 | 上からでも下からでもOK。バイナリ検索も可能 |
| ③表に列が挿入された場合の挙動 | 列番号を直接指定しているため、参照がずれてしまう*2 | 影響なし |
| ④検索キーが検索列の右側にある場合 | 対応できない*3 | 問題なし |
| ⑤検索結果が複数ある時に返す値 | 一番上のみ | 一番上でも一番下でも指定可能 |
| ⑥横方向の検索 | HLOOKUP関数でVLOOKUP関数同様の実装が可能 | 縦でも横でも対応可能 |
*1 VLOOKUPでも検索方法の引数をTUREまたは1にすればバイナリ検索ができますが、完全一致では使用できないため、ここでは考慮しません
*2 列番号の指定で関数を用いることで回避はできます
*3 検索範囲の配列を結合して生成する等で回避はできます
パッと見た感じでは、どう見てもXLOOKUP関数はVLOOKUP関数（+HLOOKUP関数）の上位互換にしか見えません。
すごいポイント
Point 1. 列を挿入しても参照が崩れない
まずは今までのVLOOKUP関数のおさらいです。
G列（検索キー）の情報をもとに、左側の表の4列目に相当するE列（値3）の情報を取得しています。
ちなみに列を挿入すると、以下のように参照が崩れてしまいます。
これは参照する列（4列目）を定数で指定しているためです。列を挿入したことによって、4列目が値3から値2になってしまいました。VLOOKUP関数あるあるの問題です。なお表の範囲は、列の挿入に伴って自動でF列まで拡張されます。しかし列数を指定している「4」はそのままなので、参照がずれてしまいます。
これをXLOOKUP関数で書き換えると、
G列（検索キー）の情報をもとに、B列（検索範囲）とE列（結果の範囲）を直接指定して情報を取得しています。
列を挿入しても、検索範囲の列を直接指定しているため、参照が崩れません。E列への参照が自動的にF列への参照に変更されます。
Point 2. 検索キーが検索範囲の右側にあっても検索可能
これはそのままですね。VLOOKUP関数だと工夫しないとできないやつです。
Point 3. 上から一致するものを探すだけでなく、下から一致するものを探すことも可能
検索条件に一致するデータが複数ある場合、VLOOKUP関数では基本的に一番上のものを返すことしかできませんでしたが、XLOOKUP関数では一番下のものを返すことができます。最新のデータを拾いたいときなどに便利ですね。
検索パフォーマンス比較
便利とは言っても、大容量のデータを扱うGoogleスプレッドシートにおいて、検索パフォーマンスは超重要です。そこで、VLOOKUP関数とXLOOKUP関数で、検索速度を比較してみます。
検索対象のデータ
10万行x4列からなるテーブルに対して、10万回の検索をかけます。
パフォーマンス計測方法
GAS（Google Apps Script）を用いてH列に関数を入力し、シートの更新にかかった時間を計測します。
ここは詳細を述べると長くなってしまうので割愛しますが、要約すると、各セルに必要な数式をGASで入力した後に、SpreadsheetApp.flush() の完了にかかった時間を計測しています。1回だと誤差が大きいため、各手法10回ずつ実行して平均値を求めました。
計測結果
| 関数 | 入力方法 | 検索方法 | 計測結果 | 数式 |
|---|---|---|---|---|
| XLOOKUP | 全セルに数式入力 | 線形検索 | 1.162秒 | =XLOOKUP(G3,$B:$B,$E:$E) |
| XLOOKUP | 全セルに数式入力 | バイナリ検索 | 1.371秒 | =XLOOKUP(G3,$B:$B,$E:$E,,,2) |
| XLOOKUP | 先頭行にARRAYFORMULAで記載して展開 | 線形検索 | 0.249秒 | =ARRAYFORMULA(XLOOKUP(G3:G,$B:$B,$E:$E)) |
| XLOOKUP | 先頭行にARRAYFORMULAで記載して展開 | バイナリ検索 | 0.285秒 | =ARRAYFORMULA(XLOOKUP(G3:G,$B:$B,$E:$E,,,2)) |
| VLOOKUP | 全セルに数式入力 | 線形検索 | 1.509秒 | VLOOKUP(G3,$B:$E,4,0) |
| VLOOKUP | 先頭行にARRAYFORMULAで記載して展開 | 線形検索 | 0.282秒 | =ARRAYFORMULA(VLOOKUP(G3:G,$B:$E,4,0)) |
結果考察
- XLOOKUP関数でもVLOOKUP関数でも、全セルに直接数式を入力するより、ARRAYFORMULAを利用して数式を展開したほうが圧倒的に早いです。
- XLOOKUP関数のバイナリ検索は、パフォーマンスが上がるどころか、むしろやや低下しています。元データを昇順に並べないといけないという制約もあるため、現状ではバイナリ検索を使うメリットはないでしょう（デフォルトの線形検索がベターです）。ちなみにExcelだと、バイナリ検索にすることでかなり高速化できるそうです。
- VLOOKUP関数よりXLOOKUP関数の方がやや早いです。こうなると、いよいよVLOOKUP関数の出番が・・・
VLOOKUP関数の方が良い場面
検索対象のデータが外部ソースの場合
検索対象のデータを外部ソースにするときは、VLOOKUP関数で書いたほうがパフォーマンスが良く、また数式がスッキリします。そもそもデータソースが外部の場合はXLOOKUPのメリットである「列を挿入しても参照が崩れない」という前提もなくなるため、パフォーマンス優先でVLOOKUPを使っても良いかもしれません
//データセットは前項のパフォーマンス計測に用いたのと同じものを使用
//XLOOKUPで書いた場合
=ARRAYFORMULA(XLOOKUP(G3:G,IMPORTRANGE(省略),IMPORTRANGE(省略)))
//平均実行時間：0.281秒
//VLOOKUPで書いた場合
=ARRAYFORMULA(VLOOKUP(G3:G,IMPORTRANGE(省略),4,0))
//平均実行時間：0.260秒
実際IMPORTRANGE関数の中身はかなり長いシートidが記載されるので、可読性の観点からも、こういったケースではVLOOKUP関数の方がベターな場合もありそうです。
また、VLOOKUP関数の方がパフォーマンスが良いことについては、VLOOKUP関数では外部データソースへのアクセスが1回で済むのに対し、XLOOKUP関数だと2回アクセスが必要だからでは？と考えていますが、詳しいことまでは現時点で分かっていません。
Excelとの互換性を気にする場合
XLOOKUP関数がExcelで採用されたのは2020年からなので、これより前のバージョンのExcelでは使用できません。Googleスプレッドシートで作成したファイルをダウンロードし、不特定多数がExcelで開くことを想定するのであれば、VLOOKUP関数を使用した方がベターです。