行ごとVLOOKUPしてしまう。VLOOKROW【関数】【Googleスプレッドシート】【名前付き関数】
VLOOKUP関数・XLOOKUP関数の物足りない点
エクセルやスプレッドシートをデータベース的に使うときにVLOOKUP関数・HLOOKUP関数で面倒だなと思う点。
行の範囲を抽出できないところです。
三列にまたがったセルをピックアップしたいとき、３つの計算式を入れないといけません。
今回は、一つの計算式で一行の範囲（複数の連続したセル）をピックアップできる関数、VLOOKROW関数のご案内です。
XLOOKUP関数で解決できたので記事追加しました(2024/4/14)
VLOOKROW(①開始,②終了,③基準,④ヘッダー範囲,⑤ヘッダー開始)
セルの指定は次の5つ
① 抽出したい範囲のスタート（左上）
② 抽出したい範囲のおわり（右上）
③ 抽出したいキーレコード
④ 範囲：キーレコードの対応する範囲
⑤ キーレコードの対応する範囲のスタート
VLOOKROW関数：動作イメージ
解説
名前付き関数についてはこちらの記事を参照ください
「関数の定義」
今回の関数の定義です。
=index(indirect(ROW_N2(s)&(row(head)+(match(key,range,false))-1)):indirect(ROW_N2(e)&(row(head)+(match(key,range,false))-1)))
よく見ていただきますと、名前付き関数のなかに、名前付き関数「ROW_N2」という関数を入れています。
こちらの関数の定義は
=substitute(left(cell("address",xx),find("$",cell("address",xx),find("$",cell("address",xx))+1)-1),"$","")
としています。
VLOOKROWの中に組み込んでもいいのですが、よくわからなくなりそうなので、分けて記載しています。
関数について
=index(indirect("H"&(row(G9)+(match(B3,G9:G14,true))-1)):indirect("J"&(row(G9)+(match(B3,G9:G14,true))-1)))
というのがもとの関数です（名前付き関数を使わない）
indirect関数を使って作成しておりますが、「H」「J」というように、列番号を計算式内で指定しないといけません。「ROW_N2」は、セル指定することで、そのセルの列番号を取り出す関数です。
この関数を入れることで、普通のVLOOKUP関数とおなじように、引数にセル番地・セル範囲の指定で稼働するように設計しています。
