【スプレッドシートの便利な使い方】VLOOKUP関数じゃなくてXLOOKUP関数を使う
【スプレッドシートの便利な使い方】VLOOKUP関数じゃなくてXLOOKUP関数を使う
要約
スプレッドシートで他の列の値を検索して参照する際にはVLOOKUP関数を使うことが一般的でしたが、XLOOKUP関数を使うことでより柔軟な検索が可能になります。
VLOOKUP関数と同じことができるのに加えて、弱点を克服しています。
今回の記事の例
今回使用したスプレッドシートは下記のリンクから見ることができます。合わせてご覧ください。
https://docs.google.com/spreadsheets/d/1gyRHe9obHOWCKaaWt8tk-34kMHWEpMODiEp4g2awyDc/edit#gid=1986788343
VLOOKUP関数の他の列の値を検索して参照とは
1つ目の表には、A列に県名、B列に県庁所在地が記載されています。
2つ目の表では、自分が行ったことのある県名を記載しているとします。
このとき、2つ目の表に、1つ目の表の県庁所在地を参照して表示したいとします。
このとき、VLOOKUP関数を使うと、次のように書くことができます。
=VLOOKUP(D5,A:B,2,FALSE)
VLOOKUP関数では、
- 検索する値
- 検索する範囲
- 検索する範囲のうち、何列目を参照するか
- 検索の種類[任意]（基本的にはfalse）
を指定することで、他の列の値を検索して表示することができます。
この例だと、D5セルの値をA列から探し、合致する条件を見つけてB列の値を表示しています。
マスターデータとなる表（今回の例だと、県名と県庁所在地の表）があれば、このような検索をすることで、手入力の手間を省くことができます。
VLOOKUP関数の弱点
非常に便利なVLOOKUP関数ですが、いくつかの弱点があります。
検索する列が一番左にないといけない
先程の例では、A列に県名、B列に県庁所在地という表を参照するために、VLOOKUP関数の2つ目の引数にA:Bという範囲を指定しました。
つまり、A列を検索しています。
もし、県庁所在地を検索して、県名を表示したい場合、B列を検索することになりますが、VLOOKUP関数では、検索する列が一番左にないといけません。
これを解決するためのシンプルな方法は、下記のようにC列にA列の値をコピーしておくことです。
こうすることで、検索する列を左に持ってくることができます。
ですが、同じデータが複数ある表ができてしまい、ちょっと見にくいです。
参照先を数字で指定する必要がある
先程の例では、A列に県名、B列に県庁所在地という表を参照するために、VLOOKUP関数の2つ目の引数にA:Bという範囲と、2という数字を指定しました。
2は、A〜B列のうちの2列目というのを意味しています。
例えばA〜G列を検索対象としてG列の値を表示したい場合、
Gは7番目なので、
VLOOKUP(D5,A:G,7,FALSE)
のような書き方をする必要があります。
隣の列を参照する場合はそこまで困りませんが、Aから数えてGは7番目と即答できる方は少ないのではないでしょうか。
（ちなみに私は子供の時のまま、指を折ってAから数えています）
さらに、検索列がA列でない場合はもっと大変で、例えばC列を検索してG列を参照する場合は
VLOOKUP(D5,C:G,5,FALSE)
と5を指定する必要があります。
Cから数えてGが5番目であると即答できる方はもっと少ないのではないでしょうか。
XLOOKUP関数とは
XLOOKUP関数とは、Excelで最初に導入され、その後Googleスプレッドシートにも導入された関数です。
列を参照するVLOOKUP関数、行を参照するHLOOKUP関数と同じことを1つの関数でできるようになり、さらに上記の弱点も克服しています。
XLOOKUP関数でVLOOKUP関数と同じことをする
XLOOKUP関数ではできることが増えていますが、まずはVLOOKUP関数と同じことをする方法を見てみましょう。
XLOOKUP関数では、
XLOOKUP(Q5,N:N,O:O,"",0,1)
のように書くことで、VLOOKUP関数と同じことをすることができます。
- 検索する値
- 検索する範囲
- 参照する範囲
- 見つからなかったときに表示する値[任意]
- 検索の種類[任意]（基本的には0）
- 上から探すか下から探すか[任意]（1で上から探す）
大きなポイントは、2つ目の引数で検索する範囲、3つ目の引数で参照する範囲と、分けて指定できるようになったことです。
これにより、XLOOKUP関数は以下のようにVLOOKUP関数の弱点を克服しています。
VLOOKUP関数では検索する列が一番左にないといけない
→ XLOOKUP関数では列の順番は気にしなくていい
VLOOKUP関数では参照先を数字で指定する必要がある
→ XLOOKUP関数では参照先を列番号で直接指定できる
ARRAYFORMLA関数との組み合わせも可能
VLOOKUP関数、XLOOKUP関数ともに、下記の記事でご紹介したARRAYFORMULA関数と組み合わせることも可能です。
https://zenn.dev/kzautomation/articles/beecf960ac5521
例えば、
ARRAYFORMULA(XLOOKUP(W5:W,T:T,U:U,"",0,1))
という関数を使うことで、W列の値をT列から探して、U列の値を表示するということができます。
まとめ
XLOOKUP関数はVLOOKUP関数の弱点を克服した関数です。
VLOOKUP関数でできていたことはすべてできるため、今後、VLOOKUP関数を使おうと思った際は、この記事を思い出してXLOOKUP関数を使うことをおすすめします。
スプレッドシートの作業効率を上げるために、XLOOKUP関数を使いこなしてみてください。
Discussion