【Googleスプレッドシート】"多目的LOOKUP"の名前付き関数

はじめに

(作った名前付き関数の紹介記事です。
スプレッドシートの名前付き関数についてはこちらで私見を語っています。)

VLOOKUPとXLOOKUPの比較についての記事を書いたきっかけとなった記事も然りですが、色々見ていて「世間では思った以上にXLOOKUP的な戻り範囲の指定方法が好まれているらしい」という気付きを得ました。

確かに「この列(行)の値が欲しい」をサクッと直接指定できるというのは便利ではありますよね。

これはつまるところXLOOKUP登場以前からINDEX-MATCHで使われていた手法とほぼ同じ指定方法なわけですが、実は私の関数学習はINDEX-MATCHから始まったので非常に馴染み深いものでもあります。

ﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔ
(なのでこの戻り範囲指定の仕様をもって「新しい！」というのは大分おかしいですし、XLOOKUPは「VLOOKUP/HLOOKUPの進化版」というよりむしろ「INDEX-MATCHの一つの用法の特化版」といえるもので、つまり「VLOOKUP/HLOOKUPの上位互換」というのははっきりとズレていると思うのですよね……。)
ﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔﾓﾔ

…はっ、すみません、つい「XLOOKUP上位互換説」に対するモヤモヤが溢れ出てしまっておりました。

ともあれ、今回「多目的LOOKUP」の名前付き関数を作るにあたって最初に考えたのが、この「XLOOKUP的(INDEX-MATCH的)な戻り範囲の指定方法に対応できるようにしよう」ということでした。

一方で、私は項目名で指定する方法の有用性を推しておりますので、そこも決して捨てはしません。

両方を自然に統合できる形を目指しましたので、以下内容をお読み頂き、ご意見・ご感想等頂けると嬉しく思います。

L_MLOOKUP

一瞬Geminiの「究極のLOOKUP」に乗せられて「L_Z_LOOKUP」と命名しようかと思いましたが、まだまだ究極とは言えないので思いとどまりました。(「究極に向けて」は別途後述します。)

そんなわけで「Multi」(多目的)や「Matrix」(二次元配列)から取って「L_MLOOKUP」です。

(ちなみに、以前紹介させて頂いた「L_Z_KANA」の「Z」は「究極」ではなく「全角」の「Z」です。念のため…。)

以下、概要・引数の説明・定義式と載せていきます。

【概要】
「検索値」が縦でも横でも、それに対する「検索範囲」が縦でも横でも対応可能な多目的LOOKUP関数です。

• 「検索範囲」は左端や上端でなくても問題ありません。

• 「戻り範囲」を直接指定可能です。

• 「戻り範囲」 の中から項目名で絞り込みができます(省略可)

• 「エラーの場合」を設定できます。(省略可)

「項目選択」を省略すると、戻り範囲の指定はXLOOKUPのような使い心地になります。(カンマを余分に打つ必要はありますが…)
そして「項目選択」を入力すれば項目名での指定も可能…と、自然に両立する形を実現しました。

※スプレッドシートの名前付き関数の仕様で、引数を省略する場合でもカンマは必ず入力する必要があります。

【引数と引数の説明】
引数:検索値,検索範囲,戻り範囲,項目選択,エラーの場合

1. 検索値
   検索する値、またはその配列を1列もしくは1行で指定します。

2. 検索範囲
   検索対象となる1列または1行の範囲を指定します。この範囲が1列なら自動的に垂直検索、1行なら水平検索として機能します。

3. 戻り範囲
   取得したいデータが含まれる範囲を指定します。項目選択が必要な場合は範囲の先頭(上端または左端)が項目名である必要があります。

4. 項目選択【省略可 ※カンマ必須】
   抽出したい項目名を値もしくは配列の形で指定します。指定した順番通りに並び替えて抽出されます。省略した場合は戻り範囲のすべてのデータを抽出対象とします。

5. エラーの場合【省略可 ※カンマ必須】
   検索値が見つからない場合に出力する値を指定します（空白にしたい場合は "" を入力）。完全に省略した場合は、標準のエラー（#N/A等）をそのまま返します。

【定義式】

=LET(
 項目指定なし, IFERROR(項目選択="", TRUE),
 有効項目, L_CROP_A(項目選択),
  
 検索値配列, L_CROP_A(検索値),
 有効検索範囲,L_CROP_A(検索範囲),
 有効戻り範囲,L_CROP_A(戻り範囲),
 
 縦横判定, COLUMNS(検索値配列)=1, 
 VL判定, COLUMNS(有効検索範囲)=1,
  
 参照範囲, IF(VL判定, HSTACK(有効検索範囲, 有効戻り範囲), VSTACK(有効検索範囲, 有効戻り範囲)),
  
 アイテム数, IF(VL判定, COLUMNS(参照範囲) - 1, ROWS(参照範囲) - 1),
  
 指数, IF(項目指定なし,
  IF(縦横判定, SEQUENCE(1, アイテム数, 2), SEQUENCE(アイテム数, 1, 2)),
   LET(
    検索ヘッダー, IF(VL判定, CHOOSEROWS(参照範囲, 1), CHOOSECOLS(参照範囲, 1)),
    整形項目, IF(縦横判定, TOROW(有効項目), TOCOL(有効項目)),
    ARRAYFORMULA(MATCH(整形項目, 検索ヘッダー, 0))
   )
 ),
  
 結果, IF(VL判定,
  ARRAYFORMULA(VLOOKUP(検索値配列, 参照範囲, 指数, 0)),
  ARRAYFORMULA(HLOOKUP(検索値配列, 参照範囲, 指数, 0))
 ),
  
 ARRAYFORMULA(IF(ISBLANK(エラーの場合), 結果, IFERROR(結果, エラーの場合)))
)

【出力される配列の形について】
解説の前に、少し出力される配列のイメージを整理させて下さい。
これは空間認識能力が高い方には「何を当たり前のことを…」思われてしまうことかもしれませんが、AIも勘違いしてドヤ顔で誤った指摘をしてくる程度にはイメージしづらいものなので、ご容赦下さい。

前回は検索値が縦に並び必要項目が横に並んでいる形のみについて考えていました。
今回は検索値が横・項目が縦に並ぶ形についても対応します。
この時「つまりHLOOKUPね」と思ってしまいがちなのですが、この時点ではVLOOKUP/HLOOKUPどちらが適当かはまだ判断できません。
(VLOOKUPである可能性も十分にあります)

VLOOKUPかHLOOKUPかは「引用元の表の形」で決まります。
つまり「検索範囲」が縦(垂直方向:Vertical)か横(水平方向:Horizontal)か、ということです。

以下に検索値が横のVLOOKUPと検索値が縦のHLOOKUPの例を載せておきます。

【検索値が横のVLOOKUP】

【検索値が縦のHLOOKUP】

イメージ大丈夫でしょうか？
「うん、当たり前だね」と思う方は非常に優れた空間認識能力やデータ構造の抽象化能力、論理的思考力をお持ちですね…！
私は「うん…だよね、うん…はい、そうそう…合ってる…ね、はい。」くらいです。

何にせよ、VLOOKUPやHLOOKUPは実はLOOKUPしながらTRANSPOSEするような使い方も可能ということだけ押さえて頂ければ良いかなと思います。

それでは以下定義式の上から順番に解説させて頂こうと思います。

【解説】

=LET(
 項目指定なし, IFERROR(項目選択="", TRUE),
 有効項目, L_CROP_A(項目選択),

まずは「項目指定なし」の判定です。
第四引数の「項目選択」が完全に省略されていたり、空白入力だったりエラーになっていると「指定なし」の判定になり、「戻り範囲」全体が抽出対象となります。

「L_CROP_A」はこちらの記事で紹介させて頂いた「動的クロップ」の名前付き関数です。
データが入っている最終行・最終列を特定して、有効なデータ範囲を切り抜く役割となります。
これにより、「今後指定する項目が増えることもあるかもしれないから、広めに選択しておこう」ということができます。

 検索値配列, L_CROP_A(検索値),
 有効検索範囲,L_CROP_A(検索範囲),
 有効戻り範囲,L_CROP_A(戻り範囲),
 
 縦横判定, COLUMNS(検索値配列)=1, 
 VL判定, COLUMNS(有効検索範囲)=1,

続いて怒涛の「L_CROP_A」ラッシュです。
検索値も検索範囲も戻り範囲も、増減を見越した雑な選択が許容される作りです。
私が雑なので。

「縦横判定」は検索値の形が縦か横かの判定です。
検索値の列数が1ならTRUEとなり、検索値が縦・項目が横と判定されます。

「VL判定」は引用元の表がVLOOKUPの形かHLOOKUPの形かの判定です。
検索範囲の列数が1ならTRUEとなり、VLOOKUPの形と判定されます。

前述の通り、これらは個別に判定されるものです。
(出力の形でVLOOKUPかHLOOKUPかは決まらないし、VLOOKUPかHLOOKUPかで出力の形が決まったりもしない)

 参照範囲, IF(VL判定, HSTACK(有効検索範囲, 有効戻り範囲), VSTACK(有効検索範囲, 有効戻り範囲)),

VLOOKUP/HLOOKUPの第二引数(範囲)に相当する「参照範囲」の作成です。

VL判定によりHSTACKかVSTACKかに分岐しますが、「検索範囲が左端(上端)じゃないといけない？じゃあ左端(上端)にくっつけてあげれば良いじゃない。」ということですね。

これは名前付き関数内に限らず普通にVLOOKUP/HLOOKUPを使用する際にも「検索範囲より左側(上側)の値を取得したい！」という時に使えるテクニックです。
(「そうまでしてVLOOKUP/HLOOKUP使わなくても、それこそXLOOKUP使えば？」と言われれば基本的に「それはそう」なんですが、場合にもよりますね…。)

 アイテム数, IF(VL判定, COLUMNS(参照範囲) - 1, ROWS(参照範囲) - 1),

次の「指数」の作成の準備です。
「項目選択」をしない場合、いくつの項目を返すのかを数えています。
VLOOKUPなら列数を、HLOOKUPなら行数を数えます。

 指数, IF(項目指定なし,
  IF(縦横判定, SEQUENCE(1, アイテム数, 2), SEQUENCE(アイテム数, 1, 2)),
   LET(
    検索ヘッダー, IF(VL判定, CHOOSEROWS(参照範囲, 1), CHOOSECOLS(参照範囲, 1)),
    整形項目, IF(縦横判定, TOROW(有効項目), TOCOL(有効項目)),
    ARRAYFORMULA(MATCH(整形項目, 検索ヘッダー, 0))
   )
 ),

少し長いですが「ここで『指数』(VLOOKUP/HLOOKUPの第三引数)を作成します」をひとまとまりとして考えたいので、まとめていかせて下さい。

まず、「項目指定なし」の場合「戻り範囲」のすべてを抽出対象とするので「アイテム数分の連番」となります。
1行目(1列目)は検索範囲ですので、SEQUENCEの開始値は「2」ですね。

ここで、「指数」の配列が「検索値」の配列と直交する形であることが二次元展開するための要となるため、「縦横判定」によって連番の方向を決定しています。
検索値が縦であれば横の配列(1行n列の連番)、逆なら縦の配列(n行1列の連番)です。

この連番がそのまま「項目指定なし」の場合の「指数」となります。

次に「項目選択」で項目を指定している場合についてです。
この場合ARRAYFORMULA-MATCHで指数の配列を作成することになります。

項目選択する場合には「戻り範囲」の先頭(上端もしくは左端)を項目名とすることを求めていますので、まずCHOOSEROWSやCHOOSECOLSで「参照範囲」から検索対象となる1行目(VLOOKUP)または1列目(HLOOKUP)を抜き出して「検索ヘッダー」を用意しておきます。

ここではまだ「指数の配列の形」=「ARRAYFORMULA-MATCHの配列の形」は決まりません。

ARRAYFORMULA-MATCHの配列の形を決めるのはMATCHの第一引数(検索キー)の配列の形になります。
「整形項目」でこの部分を処理しており、「検索値」と直交する形になるようにTOROW(検索値が縦)またはTOCOL(検索値が横)で強制的に整形しています。
これによって、例えば「項目選択」を直接記述する際は何も考えずに「{ , , ,}」の形で書けますし、設定シートのような別の場所に指定項目を用意しておく場合でも配列の方向を気にしなくて良くなります。

こうして、ARRAYFORMULA-MATCHで「項目選択あり」の場合についても「検索値」と直交する形の「指数」の配列が完成します。

 結果, IF(VL判定,
  ARRAYFORMULA(VLOOKUP(検索値配列, 参照範囲, 指数, 0)),
  ARRAYFORMULA(HLOOKUP(検索値配列, 参照範囲, 指数, 0))
 ),
  
 ARRAYFORMULA(IF(ISBLANK(エラーの場合), 結果, IFERROR(結果, エラーの場合)))
)

ラストです。

ここまでに用意した「検索値配列」「参照範囲」「指数」をそのままVLOOKUPまたはHLOOKUPに突っ込んで、完全一致(第四引数「0」)で実行すればまずは「結果」が得られます。

そして「エラーの場合」が完全に省略されている(ISBLANK)場合は「結果」をそのまま返し、何かしらが入力されていればエラーをそちらに置き換えて完了です。

エラーを空白(「""」)に置き換えたい場合も往々にして想定されるため、「ISBLANK」で明確に区別しているところがポイントですね。
「項目選択」部分の省略の設定とはそこが違います。

お疲れ様でした…！

【補足】
「縦横判定」を「検索値の列数=1」で判定しているということは、「検索値が1つのみで、項目が縦に並んでいる場合は不都合が起こるのでは…？」という疑問があるかと思います。
お察しの通り、この場合は「縦横判定」がTRUEとなり「指数」の配列がTOROWされるので、結果は縦ではなく横に展開されてしまうことになります。

この場合は、ちょっと裏技的ですが、「項目」の方を「検索値」として扱うことで解決します。
(項目を「検索値」として縦で選択し、「項目選択」に元検索値の値を入力する)

「検索値」と「項目」の役割を入れ替えても機能する…ということは、つまり実はLOOKUP系関数の検索とMATCH関数はやっていることが実質ほぼ同じであるということです。

冒頭で「XLOOKUPはINDEX-MATCHの一つの用法の特化版」と言いましたが、VLOOKUP/HLOOKUPもまたINDEX-MATCHの別の用法とほぼ同質の関数というわけですね。

「究極」に向けて

Geminiが「究極のLOOKUP関数！」と持ち上げてくれているもの(シコファンシー)を、頑なに「多目的LOOKUP」として扱っているのは謙遜でも何でもなく、これでは対応できない処理があるためです。

少なくともXLOOKUPの機能である「一致モード」と「検索モード」については対応させなければ「究極」とは言えないでしょう。

これを実装しようとすると、引数が増えすぎるという問題がありまして……

今回一応「項目選択」と「エラーの場合」については「省略可能」とはしているものの、内部で「空白の場合」などを判定しているだけで、公式の機能として完全に省略するということには現時点で対応していません。(必ずカンマを入力する必要がある)

となると、省略できる引数が沢山ある場合「全部省略する時はいくつカンマを打てば良いんだっけ…？(今何個め…？)」などの入力ストレスが発生します。

「オプション項目」のような一つの引数として用意し、配列の形(「{ , , , }」)で受け取るようにする方法もなくはないですが、それもちょっと複雑ですし、「引数の説明」の機能も活かしづらくなります。

全体的なバランスを考えると、今のところは「多目的」くらいで丁度良いのではないかなという結論です。

もし今後スプレッドシートの名前付き関数が公式で引数の省略に対応するようになったら、また考えてみたいですね。

その時はついでにARRAYFORMULA-XLOOKUPが二次元展開に対応してくれていると楽なのですが……。
(VLOOKUP/HLOOKUPを推しすぎて、もしかすると「アンチXLOOKUP」と思われているかもしれませんが、そんなことはないですよ…><;;)

ARRAYFORMULA-XLOOKUPでの二次元展開が実現できない場合にはMAPを使うか、CHOOSEROWS・CHOOSECOLSとXMATCHの組み合わせなどになるかなぁ……とぼんやり考えているところです。

おわりに

ここまで読んで頂きありがとうございました！

「多目的LOOKUP」でもVLOOKUP/HLOOKUPの二次元展開との相性の良さや「指数」を動的に作成する場合の取り回しやすさが光っていたかなと思います。
(繰り返しになりますが、アンチXLOOKUPではありません)

XLOOKUPは単体で利用する場合や、「戻り範囲」が1項目の場合には非常に便利に使えるのですが、今回のように二次元に展開させようとするとちょっと難しいですね。

次はまた別の名前付き関数の紹介になるかなと思っていますので、よろしければまたお読みいただけると幸いです。

【おまけ】
ドヤ顔で誤った指摘をするAI