行ごと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関数とおなじように、引数にセル番地・セル範囲の指定で稼働するように設計しています。

実際のシート

Google Sheets: Sign-in