【XLOOKUP】Googleスプレッドシート新関数 検証 -5 近似値一致に逆引きで自由自在！
これは本編のシリーズネタとは別で、旬の話題や Googleスプレッドシート、GoogleWorkspace関連でランダムに気になったことを書いていく 雑談記事です。といいつつ、こっちの方が最新ネタだからか人気ですが。。
可能な範囲で、土日に新しい記事を出していこうかなと思います。
前回の記事
【LAMBDA / XLOOKUP】Googleスプレッドシート新関数 検証 -4
2022年9月から使えるようになった 新関数と新機能。
検証記事の 1～3で LAMBDA・ヘルパー関数を、そして 先週の4では XLOOKUPの基本動作を検証・解説してきました。
今回は XLOOKUP の特徴・応用的な使い方について書いていきます。
前回の XLOOKUP検証でわかったこと
■前回のXLOOKUPの特徴（前半）でわかったこと。
XLOOKUPは 引数が多い
XLOOKUPは VLOOKUP、HLOOKUPどちらの代わりも出来る
XLOOKUPは 結果範囲を指定して 様々なケースに対応できる
XLOOKUPは ケースによっては 記述が長くなることも
XLOOKUPは 普通に検索する時は 検索モードを気にしなくてよい
XLOOKUPは IFERRORいらず
とりあえず前回までのおさらいです。
基本操作を通じて、上記のことがわかりました。やっぱ便利ですね。
今回は、もう少し特殊なケースでXLOOKUPを使ってみましょう。
XLOOKUPの特徴（後半）
XLOOKUP(検索キー, 検索範囲, 結果の範囲, 見つからない場合の値, 一致モード, 検索モード)
途中、説明が至らない部分があるかもしれません。
そこは公式の解説も参考にしてください。
4つの一致モード で モードチェンジだ！
XLOOKUP(検索キー, 検索範囲, 結果の範囲, 見つからない場合の値, 一致モード, 検索モード)
第5引数は「一致モード」。ここの引数に応じてモードチェンジをします。
モードチェンジといっても、「モードエレガント」で豪華なロングドレス姿に変身するわけではありません。
前回紹介した 省略時のデフォルト（完全一致）を含め 一致モードが4つもあるのが XLOOKUPの特徴です。
0 完全一致モード （省略時のデフォルト）
1 完全一致または検索キーより大きい次の値 （近似値モード 大）
-1 完全一致または検索キーより小さい次の値 （近似値モード 小）
2 ワイルドカード利用可能で 一致する値 （ワイルドモード）
完全一致モードは前回の記事で触れたので、それ以外のモードを検証していきましょう。
並べ替え不要で近似値一致だぞ XLOOKUP
XLOOKUPの 一致モード の 1 と -1 は近似値一致です。一致モード -1の （近似値モード 小）ですが、
VLOOKUPで 第4引数を TRUE とした時と 同じ動きなのか？
というと、そうではありません。
VLOOKUP の近似値一致は データが昇順で並んでいることが前提（必須条件）ですが、XLOOKUPは 並び順 は不問 なのです。
要はXLOOKUPは、データの並び順がバラバラの状態でも検索値に一番近い（次に大きい、または次に小さい）値を見つけてくれるってことです。
ここが大きな違いで、XLOOKUPの便利な点です。
上の画像のケースだと、Code 29 が検索キーですが、 A列（検索列） A11セルの 29を 加工しているため、完全一致ではヒットしません。
また、A列（検索列）のCode（数値）は並び順がバラバラの為、VLOOKUPの モード TRUEでは、なぜか 9の うし がヒットしています。これは求めている結果ではありません。
つまり、VLOOKUPは並びがバラバラのデータでは近似値一致が出来ないってことです。
一方、XLOOKUPは、
モード1 （近似値モード 大） ・・・ 30 の「すぷれっどしーと」
モード -1 （近似値モード 小） ・・・ 28 の 「めだか」
と 求めている結果が返ってきています。
XLOOKUPでは データの並びを気にせず、検索値 29 が見つからなかった場合は、29に 最も近い（近似値の） 大きい 、または 小さい 値が取得できるということです。
■近似値一致は 数値で使うのが一般的
近似値一致は、数値以外でも一応出来ます。
上記は B列の 商品名（ひらがな）を対象に B列には存在しない「テスラ」を近似値一致で検索した結果です。
昇順で並んでいないデータなので、当然こちらも VLOOKUPでは近似値一致が出来ません。
一方、XLOOKUPでは近似一致で 大・小 が取得できています。
B列を SORTしたデータを見ればわかりますが、「テスラ」が入る場所の上下のセル の文字「だんぼ」と「とうきょう」を返してるのがわかります。文字列でも近似値一致が出来ています。
ただ、名前などの 漢字を含むデータでは、基本が 音読みのGoogleスプレッドシートでは 思うような動作をしません。
数値以外で近似値一致を利用することはあまり無いでしょうが、 漢字を含むデータで利用する時は注意が必要です。
■近似値一致の使いどころは限定的だけど便利
数値でも データバラバラでの近似値一致って、使える場面は限定的だと思います。ただ 降順データでの利用 ケースは、お仕事によっては あるんじゃないでしょうか？
例えば学校の先生が、テストの点をベースに成績ランクをつけるとします。
点数に応じた成績ランクの基準表を用意する場合、通常は こんな感じで 上の成績（点数がいい方）から ランク表を作成すると思います。
この基準表だと、点数が 降順なので VLOOKUPの近似値一致が使えません。
=XLOOKUP(F3,$A$3:$A$9,$C$3:$C$9,,-1)
モードチェィンジ -1 近似値モード小！
でも XLOOKUPであれば、この表をそのまま使って近似値一致 小 で検索ができます。（下にオートフィルすることを想定して、範囲は 絶対参照にしましょう）
結構便利かも。
もう一つ別の例、こんどは近似値一致 大を使うケースです。
こんな感じの所得税率の一覧表をベースに、各社員の年間所得から 税率を関数で取得したい時があったとします。
※ 一覧表の 一番下は 通常は 4,000万円以上という表記ですが、「以下」で揃える必要があるので、そこだけ 10億以下という 形に修正しています。年収10億以上の人は、すいませんが対象外ｗ
一覧表の 所得の金額は 昇順となっていますが、〇〇万円以下という区分けなので、例えば 年間所得 500万の人は 近似値 小 の 330の行 10%ではなく、近似値 大 の695の行 20% の方を参照しなければなりません。
残念ながら 近似値一致 大を取得したい場合は、並び順にかかわらず VLOOKUPでは対応できないのです。
=XLOOKUP(F3,$A$3:$A$9,$C$3:$C$9,,1)
モードチェィンジ 1 近似値モード大！
でも、XLOOKUPだとモードチェンジ 1でサクッといけちゃうというわけです。
これはさすがに「XLOOKUP だーいすき。」って言っちゃうかもｗ
■VLOOKUPでも無理すれば出来なくもない
でも Googleスプレッドシート職人としては、
Excelから「私が来た。」
って感じで オールマイトのごとく XLOOKUPが登場して 全て解決ってのは、No2ヒーローのエンデヴァー と同じく 気持ち的には 面白くないわけです。
XLOOKUPがないと 出来ない。（出来なかった）とは言えませんｗ
VLOOKUPでも 「やればー、できる！！」
じゃあ、どうやるか？
前回もVLOOKUPでも出来るもんで活躍した、Googleスプレッドシートならではの 範囲加工、範囲結合を駆使すれば 良さそうです。
これで、さっきは「だーいすき」って言っちゃったけど・・・。
べ、別にアンタ（XLOOKUP）がいなくても、平気なんだから。
VLOOKUPがあれば対応できるんだからね。勘違いしないでよね！
とツンデレ返しが出来ますねｗ
成績のケースをVLOOKUPでやる場合
=VLOOKUP(F12,SORT($A$3:$C$9),3,true)
たとえば 成績のケースであれば、基準表 の 検索キー（A列）が昇順であればよいわけですから、式の中で SORT関数で 並び替えしたものを 範囲としてしまえばVLOOKUPで対応可能です。
SORT関数は、正しくは SORT($A$3:$C$9,1,true) と書く必要がありますが、1列のデータを単純に昇順にする場合は、簡略形で記述できます。
所得税率のケースをVLOOKUPでやる場合
=IFERROR(VLOOKUP(F12,$A$3:$C$9,3,FALSE),
VLOOKUP(F12,{{0;$A$3:$A$8},$C$3:$C$9},2,TRUE))
所得税のケース（近似値一致 大）は、ちょっと複雑です。
表が昇順になってるので、VLOOKUPの近似値一致 小で ヒットした 一つ下を返せば良さそうってことで、前回みたいな 範囲を一つずらして結合すればいけるかなと考えます。
でも、それだと 検索値が 120 や 90といった 表内の最小値 195 以下の場合は #N/A(見つかりません）を返してしまいます。
そこで {0;$A$3:$A$8} として、A3からA8 （表の 195～4,000）の上に 0を付けた 1列の配列を作成した上で、さらに $C$3:$C$9 と横方向で結合させ
{ { 0 ; $A$3:$A$8 } , $C$3:$C$9 }
という配列を作成します。
つまり式の中で以下のように表を加工してるわけです。
これだと 年間の所得が100とか 150の人は 近似値で 0を参照して 5%を返せますね。
これを使えば VLOOKUPの近似値一致 小 でいける！？と満足しそうですが、表の数値との完全一致のケースを考慮しなければなりません。
〇〇円以下 となっているので、年間所得が 330ちょうどの人は 作成したバーチャル配列（加工表）でVLOOKUPすると 20%ですが、正しくは 10%を返す必要があります。
そこで、一旦 完全一致で VLOOKUPした上で、（完全一致で見つからない）エラーの時だけ上記のバーチャル配列を使った近似値一致で再度VLOOKUPという処理にしています。
出来はしたけど、ちょっと面倒ですね。
やっぱ アンタ（XLOOKUP）がいないとダメかも・・・。
と再びデレになってしまいそうですｗ
上記のケース、XLOOKUPは もちろん Arrayformula の記述にできますが、VLOOKUPでの代用式も Arrayformulaが使えます。
また、今回は例として VLOOKUPで代用のきく 縦方向 の検索でしたが、横方向の場合は HLOOKUP で同じように代用できます。
※ただし、横方向だと並び替えがより手間がかかります。
XLOOKUPの代替式としては、FILTER関数とSORT関数 を組み合わせる方法もあるのですが、Arrayformulaでスピル対応出来る VLOOKUPでの方法を紹介しました。
もちろんXLOOKUPが使える今は、無理に代替式を使う意味はありません。
ワイルドだろー？ XLOOKUP
モード2 ワイルドカード利用可能で 一致する値 （ワイルドモード）
4つのモードの最後、ワイルドモードとはなんでしょうか？
※語感が面白いので、mirがワイルドモードと勝手に言ってるだけです。公式ではワイルドモードという言葉は使われていません。
こちらは、記載の通りワイルドカードを使った「あいまい検索」（部分一致）が使えるモードになります。
〇〇を含む、もしくは〇〇で始まる（〇〇で終わる）セルを検索したい、つまり完全一致ではなく 部分一致検索をしたい時に 活躍します。
ワイルドカードとして使える 特殊文字 は2種類あって、
* 任意の文字列
? 任意の一文字
となります。これは知ってる人も多いかと。
あいまい文字の数が 3文字とか 4文字と決まっていて、それで絞りたい場合には ?が有効ですが、滅多にないケースですね。
実践では * は結構使うけど ? はあまり使わない かも。
使い方としては、検索キーを A1セル参照とした場合は、
A1の文字列を含む "*"&A1&"*"
A1の文字列から始まる A1&"*"
A1の文字列で終わる "*"&A1
A1の文字列の前に なにかしら 3文字がつく "???"&A1
このように活用します。
■VLOOKUPでも出来る 部分一致検索
でも、これって VLOOKUP時代から使える機能なんですよね。
モードを指定する以外は、VLOOKUPもXLOOKUPも使い勝手は一緒です。
詳しくは、VLOOKUPの解説サイトを参考に。
じゃあ、わざわざモード切替とかしないで、
VLOOKUPと同じようにモード指定なしでXLOOKUPも 使えればよかったんじゃね？
って思うかもしれませんが、ワイルドカードとしてではなく 普通に * や ? を含む文字列を検索したい時に困るのです。（滅多にないケースですが）
無理やり、これに該当する事例を用意しました。
例えば左の「おそ松 表」から、 *松（あすたりすくまつ）や ?松（くえすちょんまつ）を検索して続柄を取得したい！という要望があったとします。
E列の式で 普通に VLOOKUPで完全一致検索をした場合は、* や ? がワイルドカードとして扱われてしまいます。そのため、
*松 ・・・ 松で終わる文字列 → 一番最初に 「おそ松」がヒット
?松 ・・・ 1文字 + 松 の文字列 → 一番最初に「一松」がヒット
となってしまい、欲しい結果が返ってきません。
一方 F列の式、XLOOKUPで モードを指定しない場合は、通常の完全一致モードでワイルドモードはONになっていない為、* や ? も通常の文字列として扱われ 求めていた結果が得られます。
■VLOOKUPでもワイルドカードはエスケープできる
ただ、VLOOKUPでは 回避策はないのか？ ワイルドカードを文字列として扱えないのか？ というと、そんなことは無くて、上記画像の G列の式のように
=VLOOKUP("~"&D2,$A$2:$B$9,2,false)
と、* や ? の前に ~（チルダ）をつけることで、エスケープさせる（ワイルドカードとして扱わない）ことが出来ます。
ちなみに、 ~ が検索文字列に含まれる場合は ~~ とする必要があります。
これらのワイルドカード、エスケープは Googleスプレッドシートならではというわけではなく、昔から Excelで使える手法 です。
エスケープが必要なケースは めったに無いでしょうし、ワイルドモードに関してはVLOOKUPからの大きな進歩はないかなと。
第5引数、4つの一致モードの特徴・使い方は以上となります。
検索を逆からいけるぞ XLOOKUP
XLOOKUP(検索キー, 検索範囲, 結果の範囲, 見つからない場合の値, 一致モード, 検索モード)
最後の 第6引数は「検索モード」となります。
1 先頭から末尾に向かって検索 （省略時のデフォルト）
-1 末尾から先頭に向かって検索
-2 バイナリ検索 (範囲は昇順である）
2 バイナリ検索 (範囲は降順である）
バイナリ検索は、二分探索というやつですね。
大量データを検索する際、検索スピードが改善されるようです。
数万行くらいじゃ速度の違いはピンとこないです。数百万行とかそのレベルの大量データだと違ってくるのかも。
ただ、正直Googleスプレッドシート上でそんなに大量データを検索することがあるかなー？という印象。
もちろん大量データをGoogleスプレッドシートで扱っていて、普通にXLOOKUPを使った時の検索時間に頭を悩ませている人には、このオプションが効果を発揮すると思います。
範囲は 昇順（または降順）である、という前提条件があるので注意。
バイナリモードについては、特に検証は不要でしょう。
そうすると、検証すべきは
-1 末尾から先頭に向かって検索
これだけですね。どんなメリット・活用方法があるんでしょうか？
■最新データは たいてい下か右に追加されていく
Googleフォームから出力したデータや、日々更新されていくデータなどは、基本的に新しいものほど下（右）に追加されていきます。
たとえば上のような、営業毎のその日までの売上実績を日々更新していくようなデータがあった場合、田中 の 直近の実績は Ｂ列に複数ある中の 一番下の田中の右の数字になります。
普通に =XLOOKUP(E3,B2:B,C2:C) としてしまうと、一番上の田中にヒットしてしまい、150という実績が返ります。これは、デフォルトが上から下への検索だからです。
これは求めている直近の田中の実績ではありません。
これを検索モード -1 で逆からの検索とする式
=XLOOKUP(E3,B2:B,C2:C,,,-1)
こうすれば、下から検索して一番最初に見つかる = 一番下の 田中（2022/10/14）の実績 である1,220 という正しい結果が得られます。
こんな感じで、直近データの取得に使えますね。
■その昔、下から検索をVLOOKUPでやっていた時
Googleスプレッドシートの場合は、SORT関数で上下を逆にして検索という手が使えます。
=SORT(B2:C,ROW(B2:B),0)
SORT関数は 並べ替えの対象範囲と同じサイズの 1列の配列であれば、なんでも 並び替え条件に使えます。
また、FILTERやINDEXと同じく Arrayformulaなしで 自動スピる関数なので、配列をそのまま引数に使えます。
今回のような 上下を反転させたい時は、ROW関数で得られる行番号を 並び替え条件の列として、0(FALSE) で降順を指定すれば OK。
この並び替えしたデータを対象範囲としてVLOOKUPすることで、XLOOKUPの下からの検索と同じことが出来ます。
■XLOOKUP 逆から検索の活用例：一番下（右）の値を取得
XLOOKUPの 検索モード -1 の活用例です。
縦方向ばっかりだったので、横方向の例にしてみましょう。
こんな形の 備品貸出表があったとします。
最後に借りた人を C列に出力させたい場合、各行毎の 一番右側の空白じゃないセルの値となります。
※ただし *で直接拾えるのは文字列限定。数値、日付などは一工夫必要です。
「空白セルじゃない」という検索 → 一致モード 2（ワイルドモード）利用
一番右側の → 検索モード -1 （逆から検索）利用
=XLOOKUP("*",$D2:$AI2,$D2:$AI2,,2,-1)
同じように、ある列の 一番下の空白じゃないセル といった検索も可能です。
XLOOKUP登場前までは、この一番右（下）の空白じゃないセルの取得は結構面倒で、FILTER と INDEXを使ったり、範囲を反転させて VLOOKUPといった処理をしてましたが、XLOOKUP登場でシンプルに 対象の行の 一番右の値を取得できるようになりました。
これらは残念ながら 検索キーではなく、検索範囲・結果範囲がスライドしていく処理なので Arrayformulaが使えません。
ただし、LAMBDA BYROWなら一つの式でスピル可能です。簡単な式なので、LAMBDAを使いこなしたい人はチャレンジしてみてください。
検索モードの検証は以上になります。
今回の後半 応用編では XLOOKUPの特徴の中でも、特殊なモード（設定）の活用を 検証しました。
これで、すべての引数、すべてのモード についての検証が完了です。
■今回のXLOOKUPの特徴（後半）でわかったこと。
XLOOKUPは 一致モードが4つもある
XLOOKUPは 並びがバラバラでも近似値一致が使える
XLOOKUPは VLOOKUPにはない 近似値モード 大 がある
XLOOKUPは 一致モードを切り替えて「あいまい検索」が出来る
XLOOKUPは 検索モードも 4つある
XLOOKUPは バイナリ検索でスピードアップ（要並び順）
XLOOKUPは 下（右）から検索が出来て便利
「特徴」に関しては以上となります。
次回は XLOOKUP検証の最後、XLOOKUPで出来ること・出来ないこと、そしてそれをどう対処すればよいか？を検証していきます。
XLOOKUPは本当に検索系関数 最強なのか？
それはまだ……混沌の中。
それが……XLOOKUP！