Google SpreadSheet でテーブルの ID で検索して値を取ってくるようなときにお世話になる VLOOKUP 関数
検索する ID より右にある列の値しか取得できないのですが、検索する ID の列より左にある値がほしいケースも多々あるのでやり方のメモ

データ

シート名 _data

サンプルデータ (アイカツ！キャラクターデータ)

✅️ XLOOKUP を使う (推奨)

=XLOOKUP(検索キー, 検索範囲, 結果の範囲, 見つからない場合の値, 一致モード, 検索モード)

XLOOKUP は上記の構文なので、キャラクター名 (B 列) からキャラID (A列) の値を取得するには次のようにすれば良い

XLOOKUP

=XLOOKUP("星宮いちご", '_data'!$B$2:$B, '_data'!$A$2:$A, "該当なし")
// -> "C001"

📝 XLOOKUP は検索範囲を上から捜査し、最初にマッチした行から値を返す

同じ値を持つ列が複数ある属性 (D列) をキーワードにした場合一番最初に発見された行の値が返される

XLOOKUP は上から捜査し最初に発見した 1行から値を返す

=XLOOKUP("クール", '_data'!$D$2:$D, '_data'!$B$2:$B, "該当なし")
// -> 霧矢あおい

INDEX + MATCH を使う (古の方法)

行と列から参照する値を返す INDEX の行数を MATCH で検索することで検索値より左にある値も取得することができる

= INDEX(返す値の範囲, 行数: MATCH(検索キー, 範囲, 0))

note: MATCH の第三引数を 0 にすることで完全一致する値を検索する

サンプル: キャラクター名 (`B 列`) からキャラID (`A列`) の値を取得

INDEX + MATCH で検索値より右にある値を取得する

=INDEX('_data'!$A$2:$A, MATCH("紫吹蘭", '_data'!$B$2:$B, 0))
// -> C003

VLOOKUP の第3引数を工夫する (Hack的)

=VLOOKUP(検索キー, 範囲, 番号, [並べ替え済み])

VLOOKUP の第二引数 範囲 を波括弧で囲った配列にて2つめに左側の列を指定し、第三引数の取得する列番号を 2番目とすると、検索値より左の値が(なぜか)取得できる

VLOOKUP で検索値より左の列の値を取得する Hack

=VLOOKUP("藤堂ユリカ", {'_data'!$B$2:$B, '_data'!$A2:$A}, 2, FALSE)
// -> C005

検索値に合致する行から複数の値を取得したい

XLOOKUP 第三引数 (結果の範囲) を範囲で指定する

第三引数を範囲指定すると指定された範囲の値が返される

XLOOKUP 第三引数を範囲指定すると複数列が返される

=XLOOKUP("有栖川おとめ", '_data'!$B$2:$B, '_data'!$A$2:$E, "該当なし")
// -> C004, 有栖川おとめ, ハッピーレインボー, ポップ, B

_data シートの合致した行の A 〜 E 列の値が返される
この方法では範囲がそのまま返されるので、指定した列の値だけ欲しいケースがある

✅️ 1. XLOOKUP 第三引数 (結果の範囲) を配列で取得する列を指定する

XLOOKUP は第三引数 (結果の範囲) を波括弧の配列にすると、配列に含まれる列の値だけ取ってくることができる

XLOOKUP は返す列を配列で指定できる

=XLOOKUP("星宮いちご", '_data'!$B$2:$B,
  {'_data'!$A$2:$A, '_data'!$C$2:$C, '_data'!$E$2:$E},
  "該当なし")
// -> C001, エンジェリーシュガー, O

✅️ 2. XLOOKUP 第三引数 (結果の範囲) に CHOOSECOLS 関数を使い取得する列を指定する

XLOOKUP は第三引数 (結果の範囲) を範囲と選択された列から配列を返す CHOOSECOLS 関数を使うことで、取得したい列を番号指定できる

XLOOKUP + CHOOSECOLS で取得する列を指定する

=XLOOKUP("霧矢あおい", '_data'!$B$2:$B,
  CHOOSECOLS('_data'!$A$2:$E,{1, 4, 3, 5}),
  "該当なし")

CHOOSECOLS 関数
この関数は、既存の範囲内の選択された列から新しい配列を作成します。
CHOOSECOLS(配列, 列番号 1, [列番号 2])
参考: CHOOSECOLS 関数 - Google ドキュメントエディタヘルプ

📝 INDEX + MATCH を使って複数の値を取得する方法 (古の方法)

INDEX の第一引数 (返す値の範囲) を範囲にすれば、含まれる範囲すべての列の値が返される

INDEX + MATCH の第一引数を範囲にすると複数列が返る

=INDEX('_data'!$A$2:$E, MATCH("藤堂ユリカ", '_data'!$B$2:$B, 0))
// -> C005, 藤堂ユリカ, ロリゴシック, クール, B

範囲が指定できるので、XLOOKUP の方法と同じように配列にすれば、返す列を指定することもできる

配列を使う

INDEX + MATCH の第一引数を配列で取得する列を指定

=INDEX(
  {'_data'!$A$2:$A, '_data'!$D$2:$D},
  MATCH("藤堂ユリカ", '_data'!$B$2:$B, 0)
)
// -> C005, クール

CHOOSECOLS を使う

INDEX + MATCH の第一引数を CHOOSECOLS で取得する列を指定

=INDEX(
  CHOOSECOLS('_data'!$A$2:$E, {1, 4, 5, 3}),
  MATCH("藤堂ユリカ", '_data'!$B$2:$B, 0)
)
// -> C005, クール, B, ロリゴシック

📝 非推奨: ARRAYFORMULA + VLOOKUP で複数の値を取得する方法 (Hack的)

VLOOKUP の第三引数 (取得する列番号) を波括弧の配列にしただけでは先頭の列番号の値しか返らないが、全体を ARRAYFORMULA で囲うと配列の数だけ値が返される。

ARRAYFORMULA + VLOOKUP で複数列を返す

=VLOOKUP("紫吹蘭", {'_data'!$B$2:$B, '_data'!$A2:$E}, {2, 4}, FALSE)
// -> C003

=ARRAYFORMULA(
  VLOOKUP("紫吹蘭", {'_data'!$B$2:$B, '_data'!$A2:$E}, {2, 4}, FALSE)
)
// -> C003, スパイシーアゲハ

📝 第三引数の列番号は、{B, A:E} なので、B, A, B, C, D, E となるので 2 は A列, 4 は C列となる

恐らく内部的に下記のように VLOOKUP が複数回実行されているのだと思われる 👇️

{
  VLOOKUP(検索値, 配列, 出力1, 0),
  VLOOKUP(検索値, 配列, 出力2, 0)
}

可読性もあまり良くなく、公式ドキュメントにも書かれてない挙動なのであまり推奨できないと考えます

まとめ

検索値より左の値を取得したい場合は XLOOKUP を使うのが良い
- 合致した行から複数の値を取得するなら XLOOKUP と CHOOSECOLS の組み合わせが見通しが良い
- 取得したい値が少ない場合は、第三引数を配列にするのがシンプル
XLOOKUP も VLOOLUP 同様に検索範囲から最初に合致した行を1つだけ取得する
複数行取得したい場合は、素直に QUERY を使う方が良い

[参考]