1. 既存の手段と悩み

VLOOKUP関数は大変強力だが、検索元のセル範囲の1列目（一番左）に検索キーがある場合にしか使えない。
2列目以降も含めた任意の列を検索キーにする手法として、INDEX関数とMATCH関数を組み合わせたものが既に考案されてい¹る²が³、INDEX関数はARRAYFORMULA関数と併用できない。
でも、何百行と存在するデータ達に対してARRAYFORMULA関数を使わずに、数式を載せたセルを手動で何百行とドラッグして適用するなんて考えたくない。
GASはめんどい。

一体どうすれば…！

2. 結論

配列数式{範囲1, 範囲2}で範囲を結合することで、1列目を検索キー列とした新たなセル範囲をVLOOKUP関数に渡す。

=ARRAYFORMULA(VLOOKUP(検索キー範囲, {検索元のキー列, 検索元全体}, 列番号, FALSE))

INDEX関数は一切使わない。
あと、処理速度については知らない。

3. 実例

実際にGoogleスプレッドシートを作ってみた。そちらを見てもらえれば十分な気がする。
でもまぁ、せっかくなので記事内でも解説する。
なお、上記シートの実際の表示と比べて、記事内のスクリーンショットは説明のために少し変更されている場合がある。

3.1. 検索元となる表

太陽系。
見ての通り、赤枠がデータ。この中を色々と検索する。

3.2. 検索元の1列目をキーとして検索する場合

まずはごく普通のVLOOKUP関数の使い方をおさらい。
検索元の1列目、"#" 列にある番号を垂直検索する。

3.2.1. 実装

検索したい番号はA列へ普通に手打ちした。
B列以降には、黒太枠で示したB15とB16にだけ数式を入れている。
つまり、B17、B18、C15:E18は手打ち上は空っぽであり、B15とB16に入れた数式の自動配列展開によって中身が埋められている。

3.2.2. 解説

見出しの方はただの配列数式なので良いとして、検索結果の方の数式はこちら。

=ARRAYFORMULA(VLOOKUP($A16:$A18, $A$2:$E$11, COLUMN(B:E)))

VLOOKUP関数自体はこんな人⁴。


=VLOOKUP(
    検索キー : 検索する値。
  , 検索範囲 : 値を検索する範囲。
  , 列番号 : マッチした行の何列目を返すか（相対的、1始まりの数値）。
 [, 並べ替え済み] : 検索範囲が並べ替え済みか否か（真偽値）。省略時TRUE。
)

ARRAYFORMULA関数と併用するために、VLOOKUP関数の第1引数「検索キー」を$A16:$A18として縦方向に拡張、第3引数「列番号」をCOLUMN(B:E)（※）として横方向に拡張して指定している。
このような数式を検索結果の出力範囲の左上セルにだけ置くことで、あとはARRAYFORMULA関数が検索結果を2次元方向へよしなに展開してくれる。えらい。
（※ COLUMN関数はセル範囲から列番号を数値として1つ返す関数で、例えば=COLUMN(A5)とか=COLUMN(A:A)とかやると1を返す。）

3.3. 検索元の2列目以降をキーとして検索する場合

ようやく本題。
検索元の2列目にある天体名を垂直検索する。

3.3.1. 実装

検索したい天体名3つは普通に手打ちし、黒太枠で示したA22とB23にだけ数式を入れている。
つまり、B22:F25のうちB23以外は手打ち上は空っぽであり、やはりA22とB23に入れた数式の自動配列展開によって中身が埋められている。

3.3.3. 解説（見出し）

検索結果の方に触れる前に、よりシンプルな例として見出しの手抜きに着目してみる。
A22に入れている配列数式は以下の通り。

={A$15, A$1:E$1}

A$15とは、さっきの番号検索表の見出しとして新たに打ち込んだ "キー" という文字列を持つセル。
A$1:E$1とは、検索元の表が持つ見出しセル達。
この2つの範囲を{範囲1, 範囲2}という配列数式の記法で結合することで、1つの範囲として扱っている。

↑ こんな感じ。

さて、これさえわかれば本命のデータ検索も怖くない。

3.3.4. 解説（検索結果）

検索結果の出力範囲の左上セル、B23に入れている数式はこちら。

=ARRAYFORMULA(VLOOKUP($A23:$A25, {$B$2:$B$11,$A$2:$E$11}, COLUMN(B:F), FALSE))

VLOOKUP関数の第1引数「検索キー」と第3引数「列番号」については先ほどと同じで、それぞれ$A23:$A25として縦方向に拡張、COLUMN(B:F)として横方向に拡張している。

重要なのは第2引数の「検索範囲」。
やはり{範囲1, 範囲2}の記法を使って、検索元の表全体の1つ左側に、検索したい値のあるB列を結合している。

↑こんな感じ。
当然、検索キーの列（この場合は「天体名」）がダブるので、削りたければ削る。今回はめんどいので残した。

また、第4引数「並べ替え済み」を必ずFALSEにする。というのも、今回は元の表が検索キー基準で並べ替えられていないので、TRUEにしてしまうと正しくない結果になるため。
というか、こんな使い方をする状況では基本的に並べ替えなんてされてないだろうから、毎度FALSEにしておくのが無難か。
何なら公式ヘルプでもFALSEを推奨している⁵。しかし省略時のデフォルト値はTRUE。ご注意を。

3.5. 実装（全体）

ということで、1～5列目全てをそれぞれ検索キーとして垂直検索した結果全体をお披露目。
赤枠が検索元のデータ。黒太枠が数式を入れたセル。薄灰色が検索キーを手打ちしたセル。

なお、衛星の "-"（39行目）や分類の "木星型惑星"（45行目）、"地球型惑星"（46行目）の検索結果を見るとわかるが、該当する行が複数ある場合は一番上のものにヒットする模様。
ともあれ成功した。

4. 余談

ここから冥王星。

4.1. A列の左に新しい列を挿入すると…

何らかの気が変わって全体の一番左に新しい列を挿入してしまうと、COLUMN関数が適切な列数を指さなくなるため、検索結果がズレたり、#REF!になったりする。

解決するには、VLOOKUP関数の第3引数「列番号」を更に-COLUMN(B:B)+2で補正する。

ここまでやる必要があるかは知らない。

4.2. 数式を見出し部分に全部収める

IF関数とROW関数を駆使して「最初の行なら見出しを、それ以降の行なら検索結果を出力」という数式にすることで、数式用セルを検索結果範囲の中に置くのではなく見出し部分の方に収めることができる。

例によって、黒太枠が数式を入れたセル。
見ての通り、表の値の領域（B52:F54）には数式セルが一切なくなるので、ソートとかがしやすくなるかも。

数式はかなり長くなるので画像ではカットしたが、全貌はこちら。頭のIF分岐とお尻の閉じカッコが増えただけ。

=ARRAYFORMULA(IF(ROW(B51:B54)=ROW(B51), A1:E1, VLOOKUP($A51:$A54, {$B$2:$B$11,$A$2:$E$11}, COLUMN(B:F)-COLUMN(B:B)+2, FALSE)))

ROW関数はCOLUMN関数の行バージョンで、例えば=ROW(A10)とやると行番号である10を返す。
これをIF関数の第1引数の条件分岐に使うことで、ARRAYFORMULAが配列展開を行っているセルが何行目なのかによって処理を分岐させることができる。
なお、単にROW(B51:B54)=51としても良いのだが、4.1.と同様に突然の行挿入にも耐えるべく右辺をROW関数で補正した。

ここまでやる必要があるかは知らない。

4.3. 旧手法

一応、INDEX関数とMATCH関数を組み合わせた旧手法も試してみる。

まず、旧手法の数式はこちら（1行目）。
新手法（2行目）と比較してみよう。

=INDEX(検索元全体, MATCH(検索キー, 検索元のキー列, 0), 列番号)
=ARRAYFORMULA(VLOOKUP(検索キー範囲, {検索元のキー列, 検索元全体}, 列番号, FALSE))

INDEX関数とMATCH関数は、それぞれこんな人。

指定のセル範囲を指定の行数・列数だけずらした所にあるセルの内容を返す。
=INDEX(
    参照 : セル範囲。
 [, 行] : オフセット行数（相対的、1始まりの数値）。
 [, 列] : オフセット列数（相対的、1始まりの数値）。
)

指定のセル範囲の中から値を検索し、その相対的な位置を数値として返す。
=MATCH(
    検索キー : 検索する値。
  , 検索範囲 : 値を検索する範囲。1次元限定。
 [, 検索の種類] : 検索範囲の並べ替え状態を指定（-1/0/1）。省略時1。今回は0。
)

そして実装はこちら。
例によって、黒太枠が数式を入れたセル。

ちょっと待って、②（スマート）の方は何？？？
旧手法ではARRAYFORMULA関数が使えないから、全部のセルに数式を入れなきゃいけないんじゃないの？？？

実はINDEX関数は、第2引数（行）や第3引数（列）を省略したり0にしたりすると、指定がされていない方向には自動で配列展開を行う。
また、特に=INDEX(範囲)という風に第1引数しか指定しない場合、それは配列数式={範囲}とほぼ同じものになるっぽい。

よって、INDEX関数がARRAYFORMULA関数と併用できないとはいえ、その場合でも愚直に全てのセルをドラッグして同じ数式で埋める必要は無く、結果を出力する範囲の一番左の列にだけ数式を入れれば良い。
というか、こういう機能があるから併用できないんじゃなかろうか。

4.3.1. 余談の余談

実は、上記のINDEX関数の配列展開については、記事の執筆終盤に偶然気付いたのでかなりビビった。
「え、そんなのどこに載ってるん…？いや確かにそれは省略可能な引数だけど、省略するとそうなるの？他の関数でもそういうのあるの？宇宙は不思議がいっぱいですね？？？」となった。

なお、ARRAYFORMULA関数の公式ヘルプには、「多くの配列数式は、ARRAYFORMULA 関数を明示的に使用しなくても、隣接するセルに自動的に展開します」という記述がある。
また、INDEX関数とよく似ているOFFSET関数で試しても上記と同じような結果になった。
ひょっとしたらこれは、特定の関数の仕様というよりも、配列的な記法全般の仕様なのかもしれない。何せ私は普段配列をどうのこうのすることが無いので、こういう仕様を全く想像していなかった…。

4.4. Excelでの実現

知らない。

おわり

ExcelのVLOOKUP関数で左端列以外を検索したいときのメモ - Qiita ↩
検索値より左側にあるデータを抽出する方法 - Qiita ↩
スプレッドシートでvlookup関数とは逆向きに値を抽出してくれる数式を作ってみた - Qiita ↩
第3引数の名前は本当は「指数」だが、本記事ではわかりやすくするために「列番号」とした。 ↩
見ると、「通常は FALSE をおすすめします」「並べ替え済みを FALSE に設定することをおすすめします」「通常は FALSE にします」と3回も推している。 ↩

任意の列を検索キーにしてVLOOKUPとARRAYFORMULAしたい！【Googleスプレッドシート】