XLOOKUP(エックスルックアップ)関数は、VLOOKUP関数とHLOOKUP関数の進化版です。この2つの関数を兼ねることができます。

セル参照した検索値を頼りに、マスタデータの検索列を指定します。次に、データを戻したい列を選択し、一致モードのタイプによって検索したデータを調べて出力します。

【構文】

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

【使用例】

  • =XLOOKUP(B2:マスタ!B:B,マスタ!D:D)
    セルB2のデータを頼りに、「マスタ」シートのB列より検索、第5引数の一致モードは省略しているので、セルB2と完全一致したデータを調べます。見つかった場合、「マスタ」シートのD列にある同じ行のデータを出力します。見つからない場合は、#N/Aを出力します。
  • =XLOOKUP(B2:マスタ!B:B,マスタ!D:D,"データなし")
    上の数式と同じで、見つからない場合は「データなし」と出力します。
  • =XLOOKUP(B2:マスタ!B:B,マスタ!D:D,"サービスなし",-1)
    VLOOKUP関数のTrueと同じ検索方法
    を行います。詳細はさえちゃんのワンポイントアドバイスをご参照ください。
  • =XLOOKUP(B2:マスタ!B:B,マスタ!D:D,"サービスなし",1)
    VLOOKUP関数のTrueと同じ検索方法を行いますが、出力するデータは切り上げた数値データになります。詳細はさえちゃんのワンポイントアドバイスをご参照ください。

はじめてこの関数の引数ダイアログを見たとき、引数が5つもあるよ! と愕然としたのですが、実質使うのは第3引数までです。

VLOOKUP関数やHLOOKUP関数は列番号・行番号を指定する必要があったので、マスタデータにデータ列が挿入されると固定の番号だったため、すぐに崩れて数えなおしが必要でした。列番号行番号を数えなくていいこのXLOOKUP関数は、今後活躍しますよね。

結局はデータ整理で使うから、あまり使いたくないのは変わりないけど……

 VLOOKUP関数を知らない人は先にVLOOKUP関数を学習してください。

関数説明

VLOOKUP関数と同じデータで説明します。

会員番号「N0007」の人を、「名簿マスタ」シートから探していきましょう。まず、答えを求めるセルにアクティブセルを置きます。

以下がマスタシートになります。ここから探しましょう。

[数式]タブ→[検索/行列]→[XLOOKUP]関数と追ってください。2019年の秋ごろに登場した関数で、Excel2019またはMicrosoft365でないと入っておりません。

引数ダイアログが出てきて、引数が5つあることを確認します。

検索値はVLOOKUP関数と同じです。この場合は、セルC5となります。

続いて、検索範囲。こちらは、「名簿マスタ」シートのA列から探す、という指令を出します。

そして、戻り範囲。こちらは、検索範囲からデータが見つかったら、どの列の同一行データを戻しますか? ということを聞いています。

C列を選択すると、ヒットしたA列の同一行となる名前のデータを出力することができます。

この場合、A列から「N0007」を探すことができたので、C列を指定すると「斎藤 美香」さんが抽出されます。

基本的にこれで完了です。

こういう設定方法になったので、VLOOKUP関数・HLOOKUP関数はともに検索する列または行は、必ず1列目・1行目という制約がありました。その制約がなくなったのですね。

素晴らしい。

ただ、このままだとVLOOKUP関数と一緒で、マスタにないデータが存在した場合は「#N/A」が出力されます。

VLOOKUP関数だとIFERROR関数とセットで使わなければいけなかったのですが、XLOOKUP関数には引数に「見つからない場合」という項目を設けてくれます。

このように "該当者なし" と追記すると……

1つの関数内でエラー対応をすることができました。

また、VLOOKUP関数で「False」または「0」といちいち指定して、検索方法を完全一致として数式を作成していましたが、VLOOKUP関数の「False」または「0」を利用する機会がほとんどのため、進化版となるこのXLOOKUP関数では、設定が逆になりました。

つまり、第5引数を省略(何も指定しない)して使うことで、検索値のデータに完全一致するデータを検索する範囲から調べることができるということです。

いちいち左から何列目、と数える必要がなくなったので、本当に楽になりました。

また、この使い方でいくと、HLOOKUP関数のように行方向からも取得できるので、そのためVとHを兼ねるクロス(X)なLOOKUP関数という感じでしょう。ただ、HLOOKUP関数は悪でしかないと考えている立場なので、こちらの説明は割愛します。

基本的な使い方は以上となります。

さえちゃんのXLOOKUP関数ワンポイントアドバイス

XLOOKUP関数の第5引数は絶対に使わないと思うのだけど、とりあえずご紹介しておきますね。もしかしたら使うシーンがあるかもしれない。いや、やっぱりないかもしれない。

VLOOKUP関数でTRUEを使うシーンは、

① 検索値が数値データであること
② ポイントカードのような検索方法であること

ということをご説明しました。この場合、検索値が「23」なので、VLOOKUPで抽出されるデータは来店ポイント「20」でサービスを受けることの出きる「大盛り無料」でした。

XLOOKUP関数で同じような探し方をすると、第5引数は「-1」を入力します。負の数をいれることで、検索方法はVLOOKUP関数のTrueと同じに出力結果になります。

また、正の数「1」を指定すると、繰り上げた結果となります。

近似値検索も、どちらのベクトルで検索するのか?

という追加機能があるため、XLOOKUPはこれまできっとクレームを受けたであろうVLOOKUP関数・HLOOKUP関数のすべてを網羅した関数と言えますねw

関数ステータス

関数ライブラリの種類

検索/行列

数式の構文

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

引数ダイアログ

Microsoft 公式サポート

XLOOKUP関数 - Office サポート