【XLOOKUP】Googleスプレッドシート新関数 検証 -4 列も行もいける二刀流！
これは本編のシリーズネタとは別で、旬の話題や Googleスプレッドシート、GoogleWorkspace関連でランダムに気になったことを書いていく 雑談記事です。といいつつ、こっちの方が最新ネタだからか人気ですが。。
可能な範囲で、土日に新しい記事を出していこうかなと思います。
前回の記事
【LAMBDA / XLOOKUP】Googleスプレッドシート新関数 検証 -3
2022年9月から使えるようになった 新関数と新機能。
先週までの 検証 1～3の記事で、LAMBDA の6つのヘルパー関数について書いてきました 。
今回は同じタイミングで ExcelからGoogleスプレッドシートに輸入された XLOOKUP を Googleスプレッドシートならではの視点で解説していきます。
XLOOKUPより先にLAMBDAを検証した理由
今回 Googleスプレッドシートの新関数の検証にあたり、先にLAMBDA、そしてヘルパー関数を紹介しました。
普通に考えれば、とっつきやすくて便利な XLOOKUPを先に紹介した方がよいのでは？と思うかもしれません。
なぜLAMBDAを先に検証し記事にしたのか？
それは単に LAMBDAの方が面白いからです。
ぶっちゃけ、XLOOKUPは便利ですが面白くはない、想像の範疇の関数 なんです。
もちろん、LOOKUP（検索）系において最強クラスの便利関数だとは思いますが、あくまでもVLOOKUPの改良、進化系というだけです。
検証の中で触れていきますが、一部は VLOOKUPに劣る部分もあるし。
LAMBDAが シート関数における イノベーション（革新）だとしたら、XLOOKUPは プログレス（進歩）ですね。
Appleだったら、ジョブズ時代の新製品が LAMBDAで、クック以降の製品が XLOOKUP みたいなもの。LAMBDAは「関数を再発明する」といえる イノベーティブな関数だったわけです。
そこが XLOOKUPとの大きな違いであり、LAMBDAを先に取り上げた理由です。
もちろん XLOOUP に対しては、「Googleスプレッドシートへ来てくれてありがとう！」「日本へようこそ XLOOKUPさん。」という気持ちはあります。
さらに 従来のLOOKUP系 からXLOOKUPでアップデート（進化）した部分はかなり多いので、結局1回で収まらず 全3回（予定）に分けての検証となります。
ただ、Excel側の XLOOKUP紹介記事で多くみられる
「XLOOKUPすごい」
「XLOOKUPだーいすき。」
「XLOOKUP 好きだな」
といった、ハズキルーペの CMのような ベタ褒め論調ではなく、若干 穿った 見方での XLOOKUP検証記事となっております。
その点はご了承を。
XLOOKUPの特徴（前半）
VLOOKUPが普通に使えるレベル 以上の人を対象に書いてます。
LOOKUP系で一番使われているのが VLOOKUPだと思います。
初級者 → ちょっと関数が使える人 の差も、
VLOOKUPが使えるかどうか？
が一つの基準になるじゃないでしょうか？
※VLOOKUP使えるといいつつ、IFが使えなかったり絶対参照の理解が足りないって人もいますが。。
XLOOKUPは、ざっくり言うと VLOOKUPの上位互換です。
だから VLOOKUPを使えるとは言えないかも・・・って人は、MS公式のページに ExcelのVLOOKUP 徹底解説があるので、そちらを先に読んでおくことをおススメします。（Googleスプレッドシートでもほぼ同じなので）
まずは単純にXLOOKUPの「特徴」を見ていきましょう。もちろん Googleスプレッドシートにおける使い方 という点でもチェックしていきます。
途中、説明が至らない部分があるかもしれません。
そこは公式の解説も参考にしてください。
引数が多いぞ XLOOKUP
XLOOKUP(検索キー, 検索範囲, 結果の範囲, 見つからない場合の値, 一致モード, 検索モード)
構文を見ればわかりますが、とにかく 引数が多いです。
6つの引数を取る関数って・・・
「ヤサイニンニクアブラカラメ」 みたいなマシマシ感。
特に一致モード・検索モードに関しては、
どれがどのモードだっけ？
ってなるんで、ヘルプ使わないと厳しいです。
でも、後ろの引数3つ
"見つからない場合の値" , "一致モード" , "検索モード"
は、普通に使うなら設定なしでも問題ないです。
必須は前の引数3つ、
"検索キー" , "検索範囲" , "結果の範囲"
これがわかれば基本はOK。
後ろの3つは特殊な検索をしたい人用の オプションだと思ってください。
今回は、「基本の使い方」 を検証していきます。
二刀流だぞ XLOOKUP
LOOKUP界ではいままで、
縦方向の検索 ・・・ VLOOKUP
横方向の検索 ・・・ HLOOKUP
というのが常識でした。
これを覆したのが、日本からやってきた ショーヘイではなく、EXCELからやってきた XLOOKUPです。
まさにスプレッドシート界の二刀流といって良いでしょう。
XLOOKUP 第2引数の 「検索範囲」 を
縦1列に指定すれば VLOOKUP、
横1行とすれば HLOOKUP、
このように切り替えが出来ます。
もう V なのか H なのか迷う必要はありません。
注意点としては XLOOKUPは 縦横自由自在に検索ではないです。
あくまでも指定した 縦1列、もしくは横1行での検索となります。
結果範囲を指定できるぞ XLOOKUP
VLOOKUP は 以下のような構文で、
VLOOKUP(検索キー, 範囲, 番号, [並べ替え済み])
検索範囲と結果範囲を まとめて1つの範囲とした上で、
・検索対象は 範囲の一番左の列
・結果として返す値は 第3引数に入れた 範囲内の 列番号 で指定
という縛り（記述方式）がありました。
※HLOOKUPだと 「列」の部分が「行」になります。
シンプルに検索キーが A列、欲しい結果が一つ右の B列なんて時は良いですが、検索キーより左の列に 欲しい結果がある場合や、セル結合を使った表で、欲しい結果が一つ下の行だったりする場合が困りものでした。（そんな表を作らせないことが大事なんですが）
XLOOKUPの場合は、結果範囲 を第3引数に指定できるので、この「検索キーは範囲の一番左」という縛りから解放されたのが大きな進歩です。
ただ、Excelだと VLOOKUPではお手上げだったこれらのケースも、 Googleスプレッドシートは範囲結合が可能だったので、昔から回避方法があったんですよね。
■検索列より結果列が左にあるケース
・XLOOKUP登場前の VLOOKUPでやる方法
=VLOOKUP(E2,{B2:B,A2:A},2,0)
・XLOOKUPだと ↓
=XLOOKUP(E2,B2:B,A2:A)
B列とA列を入れ替えた配列を生成して、それを範囲とすればよいだけですね。
範囲・配列結合に関しては、前回の REDUCEの検証でも使った { } を使う記述法です。
XLOOKUPの方が記述は短くわかりやすいですが、そこまで式の長さは変わらないかも。
■ヒットした行の一つ下の結果が欲しいケース
・XLOOKUP登場前の VLOOKUPでやる方法
=VLOOKUP(E2,{A2:A100,B3:B101},2,0)
・XLOOKUPだと ↓
=XLOOKUP(E2,A2:A100,B3:B101)
一つずれた範囲を横に結合した配列を生成すればOK。
注意点としては VLOOKUPの 配列 横結合でも、XLOOKUP の検索範囲と 結果範囲を設定する記述でも、どちらも サイズ（今回の場合は 行数）を同じにする必要があること。
だから 1つずらしの場合は最後を指定する必要があります。
{A2:A,B2:B} → これだと エラー
「関数 ARRAY_ROW のパラメータ 2 に一致しない行サイズがあります」
=XLOOKUP(E2,A2:A,B3:B) → これもエラー
「XLOOKUP の配列引数のサイズが異なります。」
この点は注意です。
配列の結合を使うテクニックをさらに工夫すれば お尻（最終行）を定めない記述も出来ますが、通常はそこまでやる必要はないでしょう。
■（余談）LOOKUP系は OFFSETできる（Googleスプレッドシート）
ちなみに LOOKUP系は 対象がバーチャルな配列ではなくセル範囲だった場合は、結果を OFFSETすることが出来ます。
Googleスプレッドシート の場合は、LOOKUP系全て OFFSET対応しているのですが、Excelの場合は XLOOKUPのみがOFFSET対応みたいです。
だから、上記の検索結果の一つ下を返す ケースは、OFFSETを使う方法でも対応可能です。
・XLOOKUP登場前の VLOOKUPでやる方法
=OFFSET(VLOOKUP(E2,A2:B3,2,0),1,0)
・XLOOKUPだと ↓
=OFFSET(XLOOKUP(E2,A2:A,B2:B),1,0)
OFFSETは セル範囲にしか使えないので、impotrange で取得した別スプレッドシートのデータや 数式から返される 配列を対象とする場合は、この方法は使えません。
■範囲内の列番号がわかりにくいケース
対象となる 表（データテーブル）が大きすぎて、検索キーは B列で、結果として欲しいのは AK列。こんなケースは VLOOKUPの場合だと
この列の番号ってなに？？
ってなりがちです。
単純に AK列 の列番号ではなく、範囲内の列番号なんで、この場合は Bを1として AKが何番目か？という番号なのも間違いやすいポイント。
もちろんXLOOKUPで解消はされますが、VLOOKUPでも間のデータが不要なら上記と同じように範囲を加工（途中の列を すっ飛ばしてB列とAK列だけに）するといった方法で解決できます。
■検索キーが AZ2に入ってるとして、検索列 B列、結果列 AK列の場合
・XLOOKUP登場前の VLOOKUPでやる方法
=VLOOKUP(AZ2,{B2:B,AK2:AK},2,0)
・XLOOKUPだと ↓
=XLOOKUP(AZ2,B2:B,AK2:AK)
ここでは触れませんが、例えば1行目がタイトル行となっている場合は、カラム名をMATCH関数で検索して列番号を取得 する方法もあります。
これだと可変にも出来ますしね。
こんな感じで、XLOOKUPで記述がシンプルにはなったけど Googleスプレッドシートでは 以前から出来ることだったんですよね。
だから Excel に XLOOKUP登場の時みたいに、「号外！ ついに 検索列の左側も結果を返せる！」「うぉぉぉぉー」と Excel職人たちが Xジャンプをしてしまうような 、そこまでの感動は無いのです。
■IMPORTRANGEを使って別スプレッドシートを直接検索するケース
IMPORTRANGEを絡めたケースなんかの場合は、XLOOKUPだと無駄に記述が長くなるので、 VLOOKUPの方が良かったりします。
・VLOOKUPの場合
=VLOOKUP(E2,IMPORTRANGE("シートID","シート1!A:B"),2,0)
・XLOOKUPの場合
=XLOOKUP(E2,IMPORTRANGE("シートID","シート1!A:A"),IMPORTRANGE("シートID","シート1!B:B"))
今はLAMBDAと INDEXを使うことで XLOOKUPでも IMPORTRANGEの繰り返しを無くすことはできますが、こんなケースだと VLOOKUPの方が記述がシンプルですね。
■実はすごいぞ 元祖 LOOKUP
余談ですが、先ほどの 行・列 どちらにも対応という二刀流、そして この 第3引数で 結果範囲を指定できるというのは、実は LOOKUPでも 可能なんです。
ただ、LOOKUPの場合は 「検索データが昇順でならべていることが前提での近似値一致検索」という縛りがあるので、気軽には使えないのが難点です。
それでも、ケースによってはLOOKUP も XLOOKUP以上にシンプルな記述ができることもあるので、使い分けができると良いですね。
結果範囲のスピル関連については、ここでは触れず XLOOKUP後編で掘り下げます。
基本が「完全一致」だぞ XLOOKUP
順番が前後しますが、これも基本ということで 先に 第5引数の"一致モード"（なにも設定しない時）について書きます。
地味に嬉しいのがこのアップデート。
いままで VLOOKUPやHLOOKUPを使う場合、普通の検索（いわゆる「完全一致」）で実行しようと思ったら、第3引数に FALSE（もしくは 0）を入れてあげる必要がありました。
第3引数の設定を忘れたり、「完全一致」という言葉から 勘違い をして TRUE（または 1）を入れちゃうと、上記のように 理解不能な結果が返ります。（バイナリ検索で引っ張ってくる動きらしい）
なんで 完全一致が FALSEなんだ？ と思うかもしれませんが、これはVLOOKUPのヘルプを見ればわかりますが「並べ替え済みですか？（昇順でデータは並んでますか？）」に対して、TRUE（はい。昇順でならんでます）、FALSE（いいえ。バラバラの順番です） だと思ってください。
LOOKUP関数だと、そもそもこの引数すらとれず 昇順でデータが並んでないと使えません。
XLOOKUPは基本（省略時のデフォルト）が、0（完全一致）検索となったので、普通に検索する時は設定不要。気にしなくてよくなりました。
これは地味ですが大きな進歩です。
ただし、この「一致モード」を変えたい時は注意が必要です。
感覚的に引数の場所が 一つ後ろにズレたのでVLOOUPで慣れていると、「結果範囲」の後ろが「一致モード」だっけ？と、第4引数の場所に 1とか 2と一致モードの設定数値を入れちゃうことが多い・・・。
他の一致モードに切り替えた場合の「特殊な検索」については、次回触れていきます。
IFERROがいらないぞ XLOOKUP
「一致モード」をうしろ（第5引数）においやって、第4引数に飛び込んできたのが 「見つからない場合の値」。
VLOOKUPで完全一致検索をした場合、検索キーと合致するものが見つからない場合は、#N/A といエラーを返します。
これを回避したい場合は、IFERRO関数でVLOOKUPをくくることで、 エラーの場合は こっちの値（もしくは 空欄を返す）という記述にする必要がありました。
XLOOKUPだと、このエラー処理が第4引数で設定できるようになったのです。記述がシンプルになりますね。
一番多いであろう、見つからない場合は 空欄とする場合は、
=XLOOKUP(E2,A2:A20,B2:B20,)
もしくは
=XLOOKUP(E2,A2:A20,B2:B20,"")
これでOK。
余談ですが、Googleスプレッドシートの場合の空白を返すは、""を使わない方がおススメです。
""で返した空白は Excelの空白と同じ扱いで ISBLANKで拾えず FALSE扱いですが、""無しだと完全なる空白で ISBLANKで TRUEとなります。
※ Excelだと""なしの記述だと 0 が入ってしまいます。
■第4引数で ダブル XLOOKUP
少し難易度が高くなりますが、この 「見つからない場合の値」も、実は セル参照や数式を入れることが出来ます。ですから、見つからない場合に、別の式を走らせることもできるのです。
※入れられるものに制限はあります
=XLOOKUP(E2,A2:A20,B2:B20,
XLOOKUP("*"&E2&"*",A2:A20,B2:B20,"本当に見つかりません",2))
たとえば、こんな感じで 見つからない場合の値（第4引数）に、再度XLOOKUP（一致モード 2で「含む」検索）を入れることで、完全一致で見つからない時だけ、部分一致で再検索。
※一致モード 2 のワイルドカード利用 については 次回の検証で触れます。
こんな処理がXLOOKUP内だけで完結できます。
最初から 部分一致で検索してしまうと、完全一致よりも上に部分一致セルを見つけてしまった場合、そちらが結果として返ります。
完全一致があればそちらを優先しつつ、見つからない時だけ部分一致という検索がしたい場合は、上記のような記述になります。
もちろん、この処理も XLOOKUP無しでもIFERRORを使って出来るんですが、よりコンパクトに実現できるようになったわけです。
XLOOKUPの機能豊富さは、なんだか平成初期頃 の世界最軽量とか最薄とかをウリにしてたり、やたら多機能でボタンいっぱいある家電やAV機器を作っていた「モノづくり大国 日本」っぽい感じも・・・。
XLOOUPに限らず 最近 Excelに追加された 配列操作系の関数や TEXTSPLITなんかも引数モリモリだったり、わざわざそれをやる為の関数が登場したりと、過剰サービス傾向が強めです。
便利でありがたいけど 関数がどんどん増えるし引数も増えるしで、既存関数を組みあわせて出来ることをなんで？とも思ってしまいます。サティア・ナデラ さんの方針（人柄）でしょうかｗ
今回はXLOOKUPの「基本の使い方」を検証しました。
■XLOOKUPの特徴（前半）でわかったこと。
XLOOKUPは 引数が多い
XLOOKUPは VLOOKUP、HLOOKUPどちらの代わりも出来る
XLOOKUPは 結果範囲を指定して 様々なケースに対応できる
XLOOKUPは ケースによっては 記述が長くなることも
XLOOKUPは 普通に検索する時は 検索モードを気にしなくてよい
XLOOKUPは IFERRORいらず
次回の特徴・後半では、残りの引数で設定する 少し難しい特殊な検索を取り上げていきましょう。はたしてXLOOKUPは本当に検索系関数最強なのか？
それはまだ……混沌の中。
それが……XLOOKUP！
2022/10/18 OFFSET対応の記述を追記