XLOOKUP（エックスルックアップ）関数の使い方

関数概要

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

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

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

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

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

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

動画解説

関数説明

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

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

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

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

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

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

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

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

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

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

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

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

素晴らしい。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

関数ステータス

関数ライブラリの種類

検索／行列

数式の構文

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

引数ダイアログ

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

Microsoft 公式サポート

XLOOKUP関数 – Office サポート

榊 裕次郎

Excel講師

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

引き続き、青森・秋田・岩手でのお仕事ご依頼、お待ちしております！