【簡易版】VLOOKUP・XLOOKUP比較(Googleスプレッドシート)

はじめに

VLOOKUPはXLOOKUPの下位互換ではありません…！(そんな感じの記事を見てしまった)

「VLOOKUPとの比較」とか言いながら「なぜ、XLOOKUPの方がいいのか？？」って……それはちょっと酷い……

どちらにも使いどころがあるというだけの話です。

ちょうど「アンピボット」でVLOOKUP/HLOOKUPを、
「半角カタカナの全角化」でXLOOKUPを使っていたりするので、その実例を足がかりにしながら簡単にそれぞれの使いどころなどを書いてみようと思います。

(まだ紹介記事を書きたい名前付き関数が複数あるので、ここはスクショ等も載せない簡易版でササッと書きたいと思いますが、機会があればINDEX/MATCHも含めた自分なりの比較記事なども書いてみたい……かもしれない…)

VLOOKUP

それではまずVLOOKUPの方からです。

ARRAYFORMULA(VLOOKUP(縦インデックス,縦vl用,縦項目番号+1,0))

https://note.com/ine_take/n/nc8b3e756679d

「アンピボット」の名前付き関数の定義式の一部ですね。

詳細については記事の方を読んで頂ければと思いますが、
「縦インデックス」が縦方向(行方向)の配列、
「縦項目番号+1」が横方向(列方向)の配列になります。

このように、ARRAYFORMULAを使って第一引数・第三引数にそれぞれ縦方向・横方向の配列を指定すると、簡単に二次元に配列展開させることができます。

これはXLOOKUPだと上手く展開されません(MAPなどが必要になる)ので、
ARRAYFORMULAを使っての二次元展開は明確にVLOOKUPの強みと言えますね。

また、ARRAYFORMULAを使用しない場合でも、VLOOKUPはXLOOKUPの下位互換などではありません。

確かに「VLOOKUPだけ」で使おうとすると第三引数に数字を指定する仕様は融通が効かない感じがしますが、ここに別の関数を組み合わせる場合話が違ってきます。

例えばCOLUMN関数を組み合わせるだけでXLOOKUPのように列を直接指定するような使い心地を再現できますし、特に相性が良いのがMATCH関数です。

VLOOKUPで値を引っ張ってこようとする時、引用元と引用先で同じ項目名を使用するケースが非常に多いと思いますので、MATCHで項目名をキーにして引用元の項目で何番目かを取得するだけで、欲しい値を取得することができます。

この形は、数式をそのままコピペするだけで欲しい項目名の値を引っ張ってこれますし、項目名のプルダウンを用意して簡単に取得する項目を切り替えるような使い方も可能です。

XLOOKUPでは第三引数に配列を指定する必要がありますので、同じように項目名をキーにしようとするとINDEX(範囲,0,MATCH(...))などが必要になってしまうなど、第三引数を動的に取得しようとするとVLOOKUPほど気軽にはいきません。

また、項目名での検索は便利なだけでなく非常に堅牢でもあります。

引用元で項目名が変更されない限り列の増減や項目位置の変更などがあっても全く問題なく目的の値を取得可能です。

項目名が変更された場合には”壊れる”ことになりますが、その場合にははっきりとエラーになりますので、”壊れた”ことにすぐに気付くことができます。

XLOOKUPで第三引数の戻り範囲を直接指定する仕様をもって「列の増減で壊れない」などという言説もありますが、それは引用元が直接編集される場合に限ります。

IMPORTRANGEで別シートの情報を引っ張ってきていたり、外部のcsvデータを貼り付けるなどの形で運用されている場合、IMPORT元やcsvのフォーマット変更には全く対応出来ず、しかもずれた列の値を平然と取得してしまうため、場合によっては”壊れている”ことにしばらく気付けない恐れもあります。

項目名での検索が絶対の正解ではもちろんありませんが、多くの場合で非常に有効に働くことは間違いなく、これを気軽に扱えることはVLOOKUPの強みと言って良いと私は思います。

XLOOKUP

XLOOKUP上位互換説にモヤっとして書き始めたので、上段ではXLOOKUPをやや落とすような書き方になってしまいましたが、XLOOKUPはXLOOKUPで高機能で非常に良い関数です。

IF(不要記号, "",
  IF(濁音化, XLOOKUP(現在コード, 清音コード, 濁音),
    IF(半濁音化, XLOOKUP(現在コード, 半清音コード, 半濁音),
      IFNA(XLOOKUP(現在コード, 半角コード, 全角リスト), 現在)
    )
  )
)

https://note.com/ine_take/n/n2b279fb69b45

こちらも詳細は「半角カタカナの全角化」の記事を見て頂けますと幸いです。

まず、気になってる方もいらっしゃると思うので、「最後のIFNA無駄じゃない？」について。

これは……理屈から言っておそらく無駄ですね…。

XLOOKUPは第四引数に「見つからない場合」を指定できるので、そこに「現在」を入れておけばIFNAを使う必要はなさそうです。
今度修正しておきます…。
(AIのせいにしたいけれど、さすがに気付けよ過ぎて何も言えない……)

…気を取り直して、この「見つからない場合」を指定できるのもXLOOKUPの良いところですよね…！

それはそれとして…上の実例においてXLOOKUPの特に強みである点としては「配列のサイズが一致していれば検索範囲と戻り範囲が全くバラバラの配列でも問題ない」というところになります。

これをVLOOKUPまたはHLOOKUPで処理しようとする場合、配列をまずHSTACKなりVSTACKなりする必要があり非効率です。

先ほど、「XLOOKUPで第三引数の戻り範囲を直接指定する仕様」について、必ずしも堅牢ではないといった指摘をしましたが、実際のシート上の範囲ではなくメモリ上の配列を渡す場合には、列ズレなどの心配もなく、手軽に扱えて非常に良いですね。

また、同じXLOOKUPで縦方向の配列にも横方向の配列にもそのまま対応できるのも扱いやすいポイントです。

おわりに

ここまで読んで頂きありがとうございます。

書き始めた動機の問題でVLOOKUPの方がやや熱くなってしまいましたが、どちらも良い関数だと思います。

XLOOKUPの方が新しい関数なので、単体での扱いやすさは確かにあるかもしれませんが、どちらが上位互換下位互換というものではないと私は思っています。

最後に、以上は全てGoogleスプレッドシートでの話です。
あまり違いはないかと思いますが、EXCELで当てはまらない部分があるようでしたらご容赦下さい。