Udemyで講座を受講しよう!
Excel

XLOOKUP(エックスルックアップ)関数の使い方

Yujiro Sakaki

関数概要

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と同じ検索方法を行います。詳細はさえちゃんのワンポイントアドバイスをご参照ください。

[char no=”1″ char=”さえちゃん1”]はじめてこの関数の引数ダイアログを見たとき、引数が5つもあるよ! と愕然としたのですが、実質使うのは第3引数までです。※ 第6引数に気づかなかったのは内緒ですw

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

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

 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関数では、省略可能な「1」でも完全一致を取得できるようになりました。

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

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

また、この使い方でいくと、HLOOKUP関数のように行方向からも取得できるので、そのためVとHを兼ねるクロス(X)なLOOKUP関数という感じでしょう。

ただ、HLOOKUP関数は悪でしかないと考えている立場なので、こちらの説明は割愛します。

基本的な使い方は以上となります。第6引数の検索モードに関しては、現段階では気にしないで大丈夫ですので割愛します。

動画では説明を加えましたので、10分20秒以降をご確認ください。

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

[char no=”4″ char=”さえちゃん3”]XLOOKUP関数の第5引数は絶対に使わないと思うのだけど、とりあえずご紹介しておきますね。もしかしたら使うシーンがあるかもしれない。いや、やっぱりないかもしれない。[/char]

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

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

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

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

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

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

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

関数ステータス

関数ライブラリの種類

検索/行列

数式の構文

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

引数ダイアログ

※検索モードはスクロールバーを下に移動する表示されます。

Microsoft 公式サポート

XLOOKUP関数 – Office サポート

Advertisement

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

ABOUT ME
榊 裕次郎
榊 裕次郎
Excel講師
1981年10月生まれのてんびん座、東京都出身。趣味は、旅行と料理とワイン。2024年は、佐賀県に行って「呼子のイカ」を思いっきり食べたいです。

引き続き、青森・秋田・岩手でのお仕事ご依頼、お待ちしております!
記事URLをコピーしました