【スプレッドシート】Vlookupで複数列を一気に取得する方法
Ubie Discoveryで事業開発をしているKennyです。
Ubie Engineers & Designers Advent Calendar 2022に参加しています。エンジニアやデザイナーが中心になっているアドベントカレンダーで、エンジニアでもデザイナーでもないのですがせっかくなので。是非他のメンバーの記事もご覧ください！
さて、以前に投稿した記事でGoogle スプレッドシートでハマった３つの落とし穴という記事を投稿しましたが、私はスプレッドシートをかなり利用しています。
新たなサービスや取り組みを始める際、方向性がどう変わるか・運用がどのようになるのか明確には言えない状況が多々あり、最初からシステマチックな仕組み作りをすることは難しかったりします。
そんななか、簡易な形でもデータを管理したり、分析したりするのにスプレッドシートは便利です。
また、人数をかけられるわけではないため細かなことであっても一人ひとりの高い作業効率が必要です。スプレッドシートで管理シートを作るとしても早く完成できる方が当然良いです。他にもやることいっぱいあります。
ちょっとした知識の有無で作業効率が全然違ってくることがあります。日常的に使うスプレッドシートでもそんな小技があるのでシェア出来たらと思います。
Vlookupで複数列を一気に取得する
Vlookupは極めて一般的な関数で、多用されていると思います。
構文は以下の通りです。
VLOOKUP(検索キー, 範囲, 番号, [並べ替え済み])
ここで「番号」は「範囲」で指定した中での列番号を指定します。
通常であればここは一つの数値しか適用できません。
例として、商品IDを入れると「商品名」と「単価」をマスターから拾ってくることを想定してみます。
B列に商品IDを入れると、F列以降のマスターからデータを参照します。
商品名を取り出すVlookup関数はこのような感じになるかと思います。
残りのセル（上図の赤いセル部分）にも同じように商品名と単価も取り出したいとします。
同じような関数を全てのセルに埋め込むことで実現できますが、もし変更が発生した際に修正が面倒だったり、行が増えていき関数が入ってない行が追加された場合に正しく動かなかったりします。（スプレッドシートでは初期の行数が足りなくなると自動で追加されますが直前のセルに入っている関数までコピーしてくれるわけではありません）
こんな時はArrayformula関数とVlookup関数を組み合わせることで簡単に取り出せます。
※Arrayformulaの詳しい仕様は公式ヘルプをご参照ください
一つのVlookupで複数列を指定する
まずは、複数"列"を一つの関数で取り出してみます。さきほどの図でC3セルに入れた関数を以下のように変更します。
=ArrayFormula(VLOOKUP(B3,F:I,{2,4},0))VlookupをArrayformula関数に入れ、列番号を指定する3番目の引数を { } で囲み、カンマで区切りながら取り出したい列番号を列挙します。
上記の記述だと「F列からI列の2番目と4番目の列を取り出す」ということになります。
結果は以下の通り、一つの関数で複数の列が一度に取り出せています。
ひとつのVlookupで全ての行に反映し、行追加でも関数を適用
続いて縦方向、全ての行に同じように動作するようにします。
さきほどの関数で「B3」としていたところを、「B3:B」に変更すると、行末端まで適用されます。
=ArrayFormula(VLOOKUP(B3:B,F:I,{2,4},0))
一つの関数で全ての行にも反映されるようになりました。
ですが、このままだとB列が空欄でもVlookupをしようとして#N/Aになっています。
無駄なVlookupの処理が動かないよう、B列に値が入っている場合のみ動くようif文を追加します。
=ArrayFormula(IF(B3:B<>"", VLOOKUP(B3:B,F:I,{2,4},0),))
B列に値が入っている行だけ正しくVlookup結果が出るようになりました！
このようにArrayformulaを活用して自動的に行末まで適用すると、行追加などでもトラブルを回避できて安定します。
また、一つの関数でコントロールしているので、変更の要望があっても一瞬で解決します。Vlookup以外でも使えますので是非活用してみてください。
※Arrayformula内では使えない関数（SUM等）もあるのでご注意ください。
今後も何か小技を不定期に記事化したいと思います。
ときどき「列追加されてもVlookupが壊れないようにしたい」という相談を受けることがあるので、次回はそれを記事にしてみたいと思います。