VLOOKUP(HLOOKUP)

VLOOKUP関数をarrayformula内で使用する時のhow-toです。
(とは言っても実は普通に使えるのですが)

今回はVLOOKUP関数のみを扱いますが、HLOOKUP関数も全く同様の仕様になっていますので同じ記法でOKです。

VLOOKUP関数とは1つめの引数に検索してほしい要素を渡し、2番目の引数の"先頭の列(HLOOKUPの場合は先頭の行)"の中から検索します。
そして、もし要素が見つかった場合、その列にあるセルのうち先頭行を1として3番目の引数で渡した番号の行にある要素を返す関数です。

※参考1：VLOOKUP - ドキュメント エディタ ヘルプ (google)

※参考2：HLOOKUP - ドキュメント エディタ ヘルプ (google)

例えば、

の左2列のように氏名と時給の一覧が掲載されたテーブルがあり、他の場所で氏名から検索して時給を抜き出したいとしましょう。

このとき、通常の全てのセルに書いていく形式だと以下のようになります。

そして、Arrayformulaを使用する時も通常の形式と同様に書けば大丈夫です。

ただし、Arrayformulaは列のある限り検索を行いますので、以下のように列すべてを検索範囲にしてしまうと下の列が延々と#N/Aで埋められてしまいます。

そこで、Arrayformulaの中でVLOOKUP(HLOOKUP)を使用する際は、

  • good exampleの1番目のように1番目の引数の範囲を区切る

  • good exampleの2番目のようにVLOOKUP関数全体をIFERROR関数でくくり、
    エラーが吐かれる場所に何も出ないようにしておく

のどちらかの対策を講じるのがいいでしょう。

あまり要素の追加／削除がないなら前者の対策を、頻繁に要素の追加／削除がある(特にこれから更に追加するような状況)の時は後者を使った方がよいと思います。

ちなみに、2013年12月以降の新しいgoogle スプレッドシートでは問題ないようですが、
以前のスプレッドシートではどの列のセルの要素を返すかという3番目の引数も配列にしないといけなかったようです。

※参考：How do I use VLOOKUP within ARRAYFORMULA? - Google プロダクト フォーラム https://productforums.google.com/forum/#!topic/docs/Gs29Mx0jfSE