【XLOOKUP】Googleスプレッドシート新関数 検証 -6 縦横スピらない弱点！？
これは本編のシリーズネタとは別で、旬の話題や Googleスプレッドシート、GoogleWorkspace関連でランダムに気になったことを書いていく 雑談記事です。
可能な範囲で、土日に新しい記事を出していこうかなと思います。
前回の記事
【LAMBDA / XLOOKUP】Googleスプレッドシート新関数 検証 -5
2022年9月から使えるようになった 新関数と新機能。
検証記事の 1～3で LAMBDA・ヘルパー関数を、そして前回、前々回の4,5では XLOOKUPの特徴を 基本編、応用編に分けて 検証・解説してきました。
前回の XLOOKUP検証でわかったこと
とりあえず前回のおさらいです。
■前回のXLOOKUPの特徴（後半）でわかったこと。
XLOOKUPは 一致モードが4つもある
XLOOKUPは 並びがバラバラでも近似値一致が使える
XLOOKUPは VLOOKUPにはない 近似値モード 大 がある
XLOOKUPは 一致モードを切り替えて「あいまい検索」が出来る
XLOOKUPは 検索モードも 4つある
XLOOKUPは バイナリ検索でスピードアップ（要並び順）
XLOOKUPは 下（右）から検索が出来て便利
応用ケースでの操作を通じて、上記のことがわかりました。
データの並びがバラバラでも使える 近似値一致 大・小 検索、下から上（または右から左）といった逆からの検索など、色々と便利機能がありますね。
今回はXLOOKUPシリーズ最後です。
XLOOKUPで出来ること、出来ないことを 中心に検証していき、XLOOKUPは 本当に検索系最強関数なのか？ を考えてみましょう。
XLOOKUP スピル（配列）対応
これまでのXLOOKUPの検証では、1セルだけを結果として返す使い方をしてきましたが、実は XLOOKUPはArrayformulaなしで結果を配列で返せます。
要は XLOOKUPは Arrayformulaなしでも スピるんやないかーい ！です。
EXCEL側では「もんげー」って話題になってましたね。
XLOOKUP 基本のスピらせかた
特に難しいことはありません。
複数列を返す 横にスピらせる場合
=XLOOKUP(F10,A:A,B:D)
複数行を返す 縦にスピらせる場合
=XLOOKUP(L10,F1:1,F2:4)
こんな感じで、結果範囲を複数列（複数行）に指定してあげるだけ。
同じことは他のLOOKUP系関数だと、Arrayformulaがないと出来ません。
シンプルで使い勝手がいいですね。
ちなみに、
=XLOOKUP(F10:F20,A:A,B:B)
この式では スピりません。
XLOOKUPは 出力結果を スピらせることは出来ますが、複数の検索キーに対して 個々の結果を返す動きは Arrayformulaが必要みたいです。
それでは 複数キーをまとめて XLOOKUPして、さらに結果も複数セルを返すケース、Arrayformulaと組み合わせて 縦・横両方にスピらせることは出来るのでしょうか？
XLOOKUPは 縦横同時スピルが出来ない！
小見出しを「はがない」風にしてみましたｗ
残念ながら、XLOOKUPは 縦横両方への同時展開が出来ません。
=ARRAYFORMULA(XLOOKUP(F4:F9,A:A,B:D))
↑ この式を入れて縦横両方向のスピルを期待しましたが、縦方向への展開が優先され、結果を横にスピらせる部分は機能しませんでした。
縦横にはスピらんのかーい！です。
これは EXCEL のXLOOKUP も同じみたいですね。
これには XLOOKUPさん「がっかりだよ！」（懐かしの桜塚やっくん）
単体の検索キーで「普通に検索」して、 結果を配列で得たいって場合は FILTER関数 が便利なんですよね。
FILTER関数の検索の自由度（カスタム性）、そして条件に該当する結果を全て返せるという点は、LOOKUP系と比較になりません。圧勝です。
LOOKUP系の魅力は、大量データの時の検索スピードと 複数キーに対して Arrayformula と組み合わせた時に発揮されるのに～。
検索系で縦横スピらせたい場合は、結局 VLOOKUP
じゃあ、一つの式で 縦横に展開させる検索はどうすればよいか？
Googleスプレッドシートの場合は、結局以前から使えてた手法
Arrayformula + VLOOKUP
これを使うことになります。
↓ 上の画像だと 以下のように使っています。
=ARRAYFORMULA(VLOOKUP(F14:F19,A:D,{2,3,4},FALSE))
横方向の展開部分は、 {2,3,4} このように
出力する列番号を 配列にすれば良いってことですね。
3列くらいなら問題ないですが、A列をキーとして 3~15列まで（C~O列）を出力したい、なんて時は
{3,4,5,6,7,8,9,10,11,12,13,14,15}
だとちょっと大変ですね。
こんな時は SEQUECE で連番の配列を生成してあげれば良いです。
2列目から4列目までを取得
SEQUENCE(1,3,2)
→ {2,3,4}
3列目から15列目までを取得
SEQUENCE(1,15-3+1,3) = SEQUENCE(1,13,3)
→ {3,4,5,6,7,8,9,10,11,12,13,14,15}
ここにきて、新人XLOOKUPに対して ベテランの VLOOKUPパイセンが意地を見せたって感じでしょうか。
ARRAYFORMULAとの相性という点では VLOOKUPの勝ちです。
【余談】EXCEL の場合は INDEX + MATCH(XMATCH)
余談ですが、EXCELの場合も同じなのか？というと、
残念ながら EXCELでは縦横スピルに VLOOKUPは使えません。
EXCELでの VLOOKUPのスピり方は XLOOKUPと一緒で、縦・横どちらかのみスピる動きとなります。
つまり、上の画像で表示されている通り、
=VLOOKUP(F13,A1:D34,{2,3},FALSE)
↑ こう書けば 横にスピるんですが、
=VLOOKUP(F18:F25,A1:D34,{2,3,4},FALSE)
↑ このように 複数キーに対して結果を返す 縦のスピルも入れると、縦が優先され 横へはスピらなくなってしまいます。
xlookupと一緒です。
では、EXCELでの縦横スピル検索はどうすればよいか？
上のリンク先「エクセルの神髄」さんでも記載がありますが、
INDEX + MATCH (XMATCH)
で対応します。
ここはせっかくなんで 新関数の XMATCH を使いましょう。XLOOKUPと同じ一致モード、検索モード も使えますし デフォルト完全一致だし。
=INDEX(A1:D34,XMATCH(F2:F9,A1:A34),{2,3})
↑ 上の画像のケースだと、こんな感じで式を組んでいます。
カラム名も XMATCHで検索する 以下のような書き方でも良いです。
=INDEX($A$1:$D$34,XMATCH(F2:F9,$A$1:$A$34),XMATCH(G1:H1,$A$1:$D$1))
注意点として、INDEXを縦方向にスピらせた場合は必ず列番号を指定する必要があります。
単体行に使う時は列番号指定なしで 丸ごと返せるのですが、スピらせた時は列番号 要指定になるみたいですね。
残念ながら このINDEXを使う方法は、Googleスプレッドシートでは使えません。 スピル時の INDEX関数の挙動が EXCELと違うんですよね。
Googleスプレッドシートでは、 INDEX と ARRAYFORMULA は食い合わせが悪い と覚えておきましょう。
というわけで、XLOOKUPで対応できない縦横スピル検索の「まとめ」です。
複数の検索キー に対して、個々の結果を複数列で返す処理がしたい
=> 縦横にスピらせる 検索 がしたい
■Googleスプレッドシートの場合
ARRAYFORMULA + VLOOKUP
■EXCELの場合
INDEX + XMATCH
うーん、これが XLOOKUPで出来たらスッキリだったんですが。。
見つからない場合は 自動でスピらない
XLOOKUPのスピらない話 をもう1つ。
第4引数の 「見つからない場合」の値ですが、ここも自動ではスピりません。
スピらんな～ｗ
=XLOOKUP(F4,A:A,B:D,{"なし","なし","なし"})
上記のように 見つからない場合の値として、
{"なし","なし","なし"}
という横3列に展開する配列をセットしても、最初の1つしか入りません。
XLOOKUPの基礎編で、この第4引数に数式を入れることが可能で、再度XLOOKUPで 部分一致検索させるテクニックを紹介しましたが、もし第4引数に スピらせた式を入れる場合は注意が必要です。
エラー時に配列を返したい場合は、画像のように 第4引数での処理を 諦めて IFERROR を使うか、もしくは ARRAYFOMRULAを組み合わせてスピらせましょう。
XLOOKUP 出力結果の可変対応
スピルに続いて 出力結果の可変 について考察しましょう。
出力する結果をカラム名に応じて変動させたい場合、つまり以下のようなケースです。
結果が1カラムで可変の場合
=XLOOKUP(F4,A:A,INDEX(A:D,,XMATCH(G3,A1:D1)))
=VLOOKUP(F11,A:D,XMATCH(G10,A1:D1), FALSE)
XLOOKUP と VLOOKUP それぞれの式です。
長さはさほど変わらないですが、XLOOKUPの方が XMATCHで見つけてからの INDEXという処理で煩雑 な印象ですね。
結果が複数カラムで可変の場合
横にスピらせた（複数列）の結果を、さらに可変にする場合は少し複雑です。
//XLOKKUPの場合
=ARRAYFORMULA(XLOOKUP(G3:H3,A1:D1,XLOOKUP(F4,A:A,A:D)))
//VLOOKUPの場合
=ARRAYFORMULA(VLOOKUP(F11,A:D,XMATCH(G10:H10,A1:D1),0))
XLOOKUP と VLOOKUP 、今回も長さは変わらないですね。
XLOOKUPの方は、VLOOKUP的な動きで 検索値 8 で検索した（横にスピルする）結果
8 ひつじ ウール系 18
を取得し、これを 結果配列として 再XLOOKUP（今度は HLOOKUP的な動き）をしています。再XLOOKUPの方は検索キーが配列なので、ここでArrayformulaが必要となります。
この方法だと 画像の通り、左が 「属性」（列3）で右に「商品」（列2）だったり、「商品」（列2）、「数量」（列4）といった、飛び飛びや順序逆といった 元の並びを気にせず自由に取得できます。
一方、VLOOKUPは 1カラムの時と同じ処理をArrayformulaでスピらせるだけで同じことが可能です。こっちの方がわかりやすいかも。
さらに、以下のように 検索キーの部分を配列にするだけで
=ARRAYFORMULA(VLOOKUP( F11:F18 ,A:D,XMATCH(G10:H10,A1:D1),0))
このように縦にスピらせつつ、結果カラムを可変にすることも出来ます。
これはXLOOKUPには出来ないです。
XLOOKUP 厳密な検索対応
残念ながら XLOOKUP では 「厳密な一致」での検索は出来ません。
2024年10月追記
XLOOKUPを割と厳密な一致で利用する魔法（ハックネタ）をnoteにまとめました。 ↓
Googleスプレッドシートの「一致」について
「XLOOKUPでは」という表現は適切でないですね。
VLOOKUPやその他関数で検索する際に ハマった人もいるかもしれませんが、そもそも Googleスプレッドシートの「一致」は 適当な 幅が広い んです。
上記の「田中表」は、 =$A2=B$1 をフィルして 文字の一致を TRUE,FALSEで表しています。
漢字の「田中」は別として、 Googleスプレッドシートでは
日本語表記は 平仮名、カタカナ、半角カタカナは 区別されず 全て一致
英語表記は 大文字、小文字、半角、全角 が区別されず 全て一致
※文字列の場合 半角数字と全角数字も一致
という扱いなのです。
COUNTIFやLOOKUP系での「一致」判定は、この イコール一致となります。
田中表の下に幾つか式を入れてますが、COUNTIFで A2:A9 の「たなか」をカウントすると、[タナカ」と「ﾀﾅｶ」も拾ってしまうので 3となっています。
同じく VLOOKUP や XLOOKUP で「ﾀﾅｶ」を検索すると一番上の「たなか」にヒットしてしまうのがわかります。
これ、会社名とか扱う時に結構困るんですよね・・・。
Googleスプレッドシートでの 厳密な一致・比較に関しては、「いきなり答える備忘録」さんも過去に 検証されてます。
ちなみに EXCELだと アルファベットの大文字・小文字の判別は出来ませんが、半角・全角や 平仮名・カタカナは しっかり区別してくれます。
でも、EXCEL側でも大文字・小文字を区別して検索したいって要望もあったでしょうに・・・。
XLOOKUPの 一致モードに 「厳密モード」を入れて欲しかった！
という不満です。
厳密一致での検索方法
では、厳密一致での 検索はどうすればよいのか？
EXACT関数を使う方法があります。
上の画像は 田中を表を イコール一致ではなく、EXACT一致で判定したものです。見事に FALSEを返しており、厳密に一致のチェックが出来ているのがわかります。
これをXLOOKUPで使う場合は一工夫必要です。
FILTER関数で厳密一致するケースと比較しながら確認してみましょう。
XLOOKUP での厳密一致検索
=ARRAYFORMULA(XLOOKUP(TRUE,EXACT(E2,A2:A10),B2:C10))
FILTER での厳密一致検索
=INDEX(FILTER(B2:C10,EXACT(E6,A2:A10)),1,)
どちらも 検索キーである「ﾀﾅｶ」の正しい結果が返ってますね。
XLOOKUPで使う場合は、A列に対して EXACT(E2,A2:A10) とすることで、TRUEとFALSEのみの 1列の配列を生成し、これを検索列として使います。
この部分の配列をスピらせる為に Arrayformula が必要となります。
厳密に一致した箇所が TRUEになっているので、TRUEを検索キーとしてXLOOKUPすることで、一致した行の B,C列を返すという処理にしています。結構複雑ですね。
VLOOKUPでも同じようなことは出来ますが、EXACTで検索列を生成したものを B,C列と結合させて範囲にするという手間がかかります。
一方 FILTER関数の場合は、ARRAYFORMULA無しで配列処理が出来るのと、絞り込み条件に数式が使えるので、EXACAT関数をそのまま条件に使えます。複数行ヒットした場合に 1件目のみを出力させる（XLOOKUPに合わせる）為に INDEXを組み合わせてますが、普通に使うならこれは不要です。XLOOKUPに比べシンプルに記述できてます。
この 厳密一致での検索を 複数の検索キーに対して 縦スピルさせようとすると、かなり難しくなります。
厳密一致での検索を縦スピルさせる方法
=ARRAYFORMULA(VLOOKUP(BYROW(E2:E9,LAMBDA(row,
XMATCH(true,EXACT(row,A1:A10)))),{ROW(A2:A10),B2:C10},{2,3},false))
縦スピル（Arrayformula）なので、XLOOKUPではなく最終的にVLOOKUPを使います。
BYROW(E2:E9,LAMBDA(row,XMATCH(true,EXACT(row,A1:A10))))
↑ 検索キー E2:E9 を一つずつ取り出し、EXACTで A1:A10 と厳密一致したTRUE,FALSE配列から TRUEを検索キーとして XMATCHで行番号を 取得しています。これをBYROWで 繰り返し処理し 厳密一致の行番号 配列を生成しているのがこの部分です。
新関数無しでも対応出来ますが、かなり複雑で長い式になります。便利な最新関数 LAMBDA / BYROWを使っときましょう。
EXCELなら この行番号配列を INDEXに渡せばよいのですが、Googleスプレッドシートの場合は INDEXはArrayformulaがきかないので、VLOOKUPで処理できる形にしなければなりません。
その為、対象 範囲として {ROW(A2:A10),B2:C10} という 一番左に 行番号をもってきた配列を用意して {2,3} でB、C列の値を取得しています。
※これだけで 1回分の記事書けるくらいの内容なので、説明はかなり端折っています。
うーん、XLOOKUPが 縦横スピル、厳密一致 に対応していれば・・・。
XLOOKUPまとめ
新関数 XLOOKUPの検証は以上となります。
XLOOKUPの記事の最後は、 XLOOKUPで出来ること・出来ないこと を 検証しました。
■出来ること・出来ないこと XLOOKUP 検証 でわかったこと。
XLOOKUPの結果は Arrayformulaなしで スピる
XLOOKUPは Arrayformulaと組み合わせても縦横両方にはスピらない
縦横両方スピらせるには結局VLOOKUP（Googleスプレッドシートの場合）
XLOOKUPの 見つからない場合の値は 自動ではスピらない
XLOOKUPは 可変対応できるが 少し煩雑
XLOOKUPで複数カラム の可変は、結果を 再度 XLOOKUP
Googleスプレッドシートの イコールは かなりアバウト
XLOOKUPは 厳密検索において優位性はない
ちょっと今回はXLOOKUPには 厳しめの検証でしたね。
オマケ：XLOOKUPを無理やりスピらせる技（解説無し）
余談ですが、ExcelのXLOOKUPの記事を色々みていたら、 EXCELの神髄 さんが、無理やり新関数を使って XLOOKUPの縦横スピル式を作ってました。
mirは別の方法でやってみました。
REDUCEに VLSTACK を組み合わせた 配列プッシュ 方式です。
=REDUCE("",F2:F10,LAMBDA(pv,cv,LET(x,XLOOKUP(cv,A:A,B:D,""),IF(INDEX(pv,1,1)="",x,VSTACK(pv,x)))))
Googleスプレッドシートだと VSTACKないのと 少し挙動が違うんで
=REDUCE(,F2:F10,LAMBDA(pv,cv,LAMBDA(x,IFERROR({pv;x},x))(IFERROR(XLOOKUP(cv,A:A,B:D),{"","",""}))))
まあ、どっちにしろ不要なシロモノというか 自己満式 なんですがｗ
※さらに言えば 2022年12月からは BYROW、MAPが配列を返せるようになった為、REDUCEで書く必要はありません。
XLOOKUPは普段使いには良い ユニクロ的 関数
3回にわたる検証を通じて感じた XLOOKUP の印象は、器用（使い勝手よく）、そして 便利。でも、絶対XLOOKUPじゃなきゃダメって場面は少なそう。って感じです。
importrange と組み合わせた処理などでは、 VLOOKUPやHLOOKUP を使った方がシンプルに記述できそうだし、そもそも縦横スピルしたいときは XLOOKUPで対応できないわけで、まだまだ VLOOKUPの出番も多そうです。
複数条件、複雑な条件での絞り込み・検索 は、やっぱり FILTER関数 の方が 強いんで そっち使っちゃうかなー。
あ、でも1行カレンダーの時にも登場した、EXCELの新関数 WRAPROWS/ WRAPCOLS のGoogleスプレッドシートでの代替えは、XLOOKUPとSEQUENCE の組み合わせがベスト だと思います。
ファッションで言えば、まさに XLOOKUPは ユニクロですね。
特別感はないけど普段着るには 安くて快適で、とても使いやすい。
結論としては、XLOOKUPは 最強検索関数とまでは言えないけど、普段の検索処理には とっても使える。ってことで、今後 式を作る時には積極的にVLOOUPから 切り替えていこうと思います。
このような 最新の関数、最新機能を みんなが使える状態ってのが Googleスプレッドシートの魅力ですね。
EXCELだと、やれバージョンはなんだ？とか、365なのか？買い切り版なのか？とか 気にすること多すぎｗ
以上で、全6回（その前の 新関数 考察シリーズ 2回も入れると 全 8回）に渡って書いてきた、【LAMBDA / XLOOKUP】Googleスプレッドシート新関数 シリーズは終了です。
理解に加えて、「こんな時に使えそう！」「こんな活用があったのか！」といった、驚きや発見に繋がる内容になっていたでしょうか？
触りだけしか紹介していない 「使えるケース」については、今後徐々に取り上げていきたいと思います。
え？ XMATCHにほぼ触れてない？？
確かに今回のXLOOKUPの記事で普通に使ってましたが、XMATCHも新関数 の一員でしたね。
まぁ・・・。特に解説なくても大丈夫でしょう。
単独というよりは、今回のように XLOOKUPや他の関数との組み合わせで力を発揮する関数なので、今後も登場すると思います。
「XLOOKUP と XMATCH はずっと友達」（完）
※最後もドロヘドロネタで
次回は、せっかく新関数増えるなら、これ欲しかったのにぃ～。
というテーマで書きます。
2022/11/04 追記
絶対XLOOKUPじゃなきゃダメって場面は少なそう
と書きましたが、以下のような例だと XLOOKUPがベストかなと。
データバラバラでも近似値一致 が出来る機能を さらに Arrayformula でスピらせるのは、他の関数の組み合わせだと面倒そう。
■次のシリーズの記事
この記事が気に入ったらサポートをしてみませんか？