［Excel］XLOOKUP は VLOOKUP の上位関数で使える？

2019年の８月末頃、Excel の最新版には XOOKUP 機能が追加されています。
LOOKUP 関数で有名どころ、よく使われるのが VLOOKUP ですが、VOOKUP や HLOOKUP を１つにまとめで応用できるようにした関数が XLOOKUP 関数になります。
このページは、今のところ Office 365ユーザー向けになります。
現在のところ、Office 2019 の Excel には XLOOKUP 関数は搭載されていませんが、Office 365 の Excel には搭載されていて、今後はこの関数だけ使うこなせれば脱初中級者と言われることが間違いないものなので、今のうちに勘所を押さえておきましょう。

XLOOKUP の勘所をチェック

というのが押さえポイントです。さらに、省略可能な引数として

XLOOKUP(検索値,検索範囲,戻り範囲,［見つからない場合］,［一致モード］,［検索モード］)

があります。要するに、

XLOOKUP(検索値,検索範囲,戻り範囲,［見つからない場合］,［0,-1,1,2］,［1,-1,2,-2］)

ということです。
第４引数までは普通に使います。第５引数も、ほぼ普通に使うと思いますので、

セルが返るというのは、初級者は軽く見がちですが、脱初級者になると普通に「値」を返すだけの関数は能無しに感じてきます。心の片隅にでも、セルが返るということをとどめておいてください。

ようするに、セルが返るということは値だけでなく、行番号や列番号などのセルに設定された情報を取り出せるということです。例えば、応用したら漢字の名前のフリガナを取り出したりできるわけです。ちなみに、VLOOKUP や HLOOKUP は値しか返してくれません。さらに、セルが返るわけですから、返り値をそのまま範囲として扱うなどの応用ができます。

初心者的 XLOOKUP 乗り換え用法

VLOOKUP なんて使えない的に主張して振る舞う方が、中上級者風に見えるのですが、XLOOKUP を VLOOKUP とさして変わらない使い方をすることもできます。
知ってるつもりを押し通してのし上がるためには、この用法は徹底的にマスターしておきましょう。

図の例では、表の型番から価格とモデル名を拾ってくるようになっています。

=XLOOKUP(A13,G3:G6,I3:I6)

この式はA13と同じ値のセルを範囲G3:G6から探し、その同じ行番号の値を範囲I3:I6から拾って表示せよという意味です。
VLOOKUP の時は参照する表の左端が、検索する範囲になっている必要がありましたが、XLOOKUP ではこの制限がなくなります。例のように、右にある型番データを検索して、左端の値を返すというような使い方もできます。
おそらく、考える必要も覚える必要もないほど簡単に使えるので、今後はこの関数の初級用法が使えない人はドロップアウトになる可能性が高いといえます。

ところが、今までもこんなことは、他の関数を組み合わせて中級者は同様のことを実現してきました。そのため、中級者にとってはこの程度の用法では満足感がありません。

検索範囲と参照範囲を別々に指定できる

滅多に使わない HLOOKUP の機能も含めてしまったのが XLOOKUP 機能です。

まず、図の例を見てください。
A4:I8までが参照する表（参照範囲：赤い部分）です。
１列目と２列目に、モデル名を選べばCPUやOSなどのスペックを参照する表から横に拾うようにしたいと思います。中級者だと、A4:I8の範囲に名前をつけて扱うところですが、本例では範囲を直書きします。
まず、B2にはA2に対応するCPUの値を拾ってきたいので、単純に
=VLOOKUP($A$2,$A$5:$I$8,2,FALSE)
とやります（FALSE は完全一致の意味）。
引数に「$」をつけて絶対参照にしているのは、このセルを右に（C2、D2...I2）にコピーするつもりだからです。絶対参照にしておかないと、コピーしたときにA2を参照したいのに、コピーするたび B2、C2と横にずれてしまいます。

ところが、このままだとコピーのたびに参照範囲の常に２列目の値を拾ってくるというマヌケなコピーになるので、小細工を施します。

=VLOOKUP($A$2,$A$5:$I$8,COLUMN(B1),FALSE)

B2のセルをC2にコピーしたら、第三引数はプラス１して３になってほしいわけです。ここでは、COLUMN 関数を使って、自身のセルの列番号を取得します。これで、B1をC1にコピーしても、自分のセルの列番号を返すので、ずれた分を補正できるということになります。

ここで XLOOKUP 関数を使うとどうなるかをチェックです。

=XLOOKUP(A2,A5:A8,B5:I8)

第１引数は検索したいセル、第２引数は検索してくる範囲、第３引数は返したい範囲を指定するだけです。
これで、今までの無理矢理感のある複数の関数の組み合わせなんてことをしなくても、一気に片付くようになりました。

ですが、これはかなり気持ちの悪い用法になります。データ如何（セルがガラ空きでない場合など）によっては望まない結果を返すかもしれません。

念のため、絶対参照を使わない場合は、第２引数と第３引数はテーブルにして、それを使う場合に限るようにした方が安心です。

範囲に名前をつけない場合は、

=XLOOKUP($A$2,$A$5:$A$8,B5:$I$8)

として、絶対参照と相対参照を組み合わせるのが、セルコピー用としては正解です。ここでは、第３引数に B5:$I$8 として、相対参照と絶対参照を組み合わせた小細工をしているのがコツです。絶対参照部分はコピーによるセルズレがありませんが、相対参照部分の B5 の部分はコピーによって都合良くズレてくれます。この特性を利用しています。

でも、まだ少しダサさを感じますね。テーブルを使う方が見栄えも応用もスマートな感じのようにも思います。

XLOOKUP で２つの検索値を同時に満たす行を表示させる

上の例もモデル名を直接検索するのではなく、CPUとメモリの値を同時に満たすモデル名と価格を表示させたいケースです。マルＡとマルＢを同時に満たすマルＣとマルＤに出したいわけです。XLOOKUP は第１引数と第２引数には２つ（複数）のセルをまとめて扱うため「＆」でセルをつなげておきます。
A16 はどうやっているかといえば、B16（CPU）と C16（メモリ）を「＆」でつなげて、第１引数に渡します。第２引数は CPU とメモリの値を引いてくる範囲を「＆」で連結してして指定します。第３引数は表示させたい範囲です。モデル名なら A5:A10、価格なら I5:I10 を指定します。これで、B16 と C16 を同時に満たすものを拾ってくれます。
XLOOKUP では「＆」連結程度でも、かなり効果的な検索結果を拾ってこれますので、積極的に使いたくなりますね。

＆はセルをまとめて扱う（連結）という意味です。VLOOKUP と他の関数合わせて行うことも当然できますが、式の見やすさ、解釈の簡単さがメリットになります。

無いデータの場合の#N/Aエラー処理が簡単

XLOOKUP 関数の第４引数は、データが見つからない場合、何を表示するかを自由に指定できます。ダサい IFERROR や ISERROR などの関数を組み合わせなくていいので、式が見やすくなりました。
式を組み合わせるのは EXCEL の醍醐味で、自己満足の極みなのですが、重大な問題があります。それは、作成者以外がその組み合わせの式を編集した場合（担当者が変わった場合など）、式を解釈するのに違いが生じたり、誤って解釈されたり、はたまた解釈してもらえなかったりして、データ引き継ぎのトラブルの元になる、爆弾になるリスクが高いと言うことです。
特に、複雑な式になったり、長くなりすぎた式は、多くの人が読み込んで解釈してたいとは思わず、書きなおすということを誘導しがちです。XLOOKUP のような新関数はそういったリスクを激減させてくれますので、歓迎すべきところでしょう。

第５引数［一致モード］は第４引数とセットで使う

第５引数［一致モード］は完全一致で検索するかしないかを指定します。与えるオプションは、0、-1、1、2があるのですが、原則は完全一致で検索することが多いと思いますので、「0」を指定する癖をつけておきましょう（指定しなくても、これがデフォルトの設定です）。もし検索値が見つからない場合は、第４引数で指定したものを返します。第４引数には数式やセルを指定することもできます。

完全一致には「0」と「-1」と「1」がありますが、「0」の場合は文字通りの完全一致になります。文字数が少なくても多くても、似ていても完全一致しないものはアウト（#N/A）です。一方「-1」を指定すると、見つからない場合は、次の小さなアイテムを返します。「1」を指定すると、見つからない場合は、次の大きなアイテムが返されます。「2」を指定すると、「*」が任意の数の文字、「?」が任意の１文字、「~」が「*?~」の意味を持つワイルドカードの一致になります。

テーブルを使ってタテ列で扱う例

PCのスペックなどを比較するときは、タテ列で見比べた方がわかりやすくなるので、私的によく使う方法です。

XLOOKUP は HLOOKUOP の機能も踏まえていますので、普通に手軽にタテ列表示が行えます。

セル G7 の数式は以下のように書いています。意味はこれまでのものと変わりません。

=XLOOKUP(G3,B3:E3,テーブル8[[#すべて],[列2]:[列5]])

XLOOKUP は確かに使える新関数！

私的には Office 2019 を常用しているのですが、Office365 ProPlus は今回一時的に使用する機会があったのため、流行の XLOOKUP 関数を試してみました。この関数が使いやすいからといって、使いまくったファイルは誰でも開けるわけではないというのも、実務ではしっかり押さえておくポイントです。
現状でチームワークに参加する人は、可能な限り XLOOKUP は使わないようにしましょう。自分のことを考えても、そもそも Office365 ProPlus がインストールされていない PC では関数が実行されないので、あまり勝手はよくありません。旧式 Office が型遅れになって誰も使わなくなっていれば、こればかり使いたくなる実用関数には違いありませんね。

それでも、XLOOKUP は使いやすく、応用が効くこともあって、もはや VLOOKUP はごみ同然の不要の長物に感じてしまうことも事実です。上位互換どころか、カートと F1 ぐらいの大きな差があるかもしれません。互換性さえ無視できれば最強の関数であると断言しますが、一人ビジネスでもしてない限りは、しばらく能ある鷹は爪隠すと心得ておくのが無難です。私的にはバリバリ使う関数になってきています。