RPA開発歴4年 / MICHIRU RPAエキスパート認定 / 業務改善アドバイザー
RPA導入代行サービスCoreBeeを運営しており、RPA開発に取り組む他、業務改善アドバイザーとしてあらゆる業種の業務改善に携わっている。
社内のエクセルマスターとしての地位を確立しており、今回その知識を最大限生かし執筆しています。
XLOOKUP関数とは？
XLOOKUP関数は、ある範囲内に存在するデータを検索して表示させることができる関数です。
同様の機能はVLOOKUP関数やHLOOKUP関数にもありますが、XLOOKUP関数はさらにこの検索機能が強化された関数と考えるとわかりやすいでしょう。
XLOOKUP関数をマスターすることで、VLOOKUP関数やHLOOKUP関数よりも詳細に範囲内のデータを検索する機能をEXCELの表に持たせることができるようになります。
VLOOKUP関数との違いは？
XLOOKUP関数の基本的な機能は、指定範囲内に存在するデータを検索するというものです。
この機能は、VLOOKUPと同様といえます。
しかしながら、VLOOKUP関数と比較してXLOOKUP関数には以下のような違いがあります。
VLOOKUP関数
- 結果範囲の指定が【列番号】
- 見つからない場合【TRUE】【FALSE】で指定
XLOOKUP関数
- 結果範囲を直接指定 ※【戻り範囲】
- 見つからない場合のテキスト指定が可能
XLOOKUP関数にしかない追加機能
- 【一致モード】の指定
- 【検索モード】の指定
上記のように、XLOOKUP関数はVLOOKUP関数よりも検索機能を強化した関数とみることができます。
XLOOKUP関数が使える環境にありXLOOKUP関数の記述方法をマスターできた場合には、VLOOKUP関数よりもXLOOKUP関数を利用するのがおすすめです。
XLOOKUPが使えない？バージョンを確認しよう
詳細に検索ができるという便利な機能をもつXLOOKUP関数ですが、XLOOKUP関数はEXCELの2020年2月のアップデートから実装された機能となります。
そのため、2019以下のバージョンのEXCELでは利用することができません。
“Announcing XLOOKUP”
https://techcommunity.microsoft.com/t5/excel-blog/announcing-xlookup/ba-p/811376
現在、企業や個人で利用されているケースが多いMicrosoft 365・Excel2021では、EXCELの【アカウント】情報の右側にある【Excelのバージョン情報】よりバージョンを確認することができます。
その他のEXCELバージョンの場合は、以下のように確認できます。
- 2019 / 2016 / 2013 - リボンから【ファイル】→【アカウント】→【EXCELのバージョン情報】
- 2010 – 【ファイル】→【ヘルプ】→【Microsoft Excelのバージョン情報】
- 2007 – 【Officeボタン】→【Excelのオプション】→【Microsoft Office Excel 2007のバージョン情報】
この記事で解説している【XLOOKUP関数】が利用可能なのはExcel 2021からです。
永続版ライセンスの場合は、【Excel2021】の表示が確認できれば利用できます。
画像のようなサブスクリプション型の【Microsoft 365】を利用していて、XLOOKUP関数が利用できない場合にはアップデートを行うことで利用可能となります。
アップデートは、同じ画像の中にある【Office更新プログラム】のボタンから行うことができます。
XLOOKUP関数の使い方
XLOOKUP関数を利用するには、VLOOKUP関数と同様の考え方で式を組み立てていきます。
VLOOKUP関数との違いも確認しながら式を理解していきましょう。
まず、上記のような表があるものと想定します。
青色のセルに商品IDを打ち込むと、対応する商品名と価格が緑色のセルに自動的に反映するように式を組み立てます。
式の記載方法は、【=XLOOKUP（検索値, 検索範囲, 戻り範囲, 見つからない場合, 一致モード, 検索モード）】というように記載します。
ひとつひとつ解説してみましょう。
- 検索値 – どのデータで検索するのか
- 検索範囲 – どの部分を検索するのか
- 戻り範囲 – どの部分のセルの値を表示させるのか
上記3つの引数（ひきすう）を指定するだけでもXLOOKUP関数は利用できます。
さらに、以下の値も指定することで、さらに詳細な検索が可能です。
- 見つからない場合 – 検索値が見つからなかった場合に何を表示させるか
- 一致モード – 完全一致で検索するか、近似値も検索させるのか
- 検索モード – どの順序で検索させるか（先頭から末尾に向かうのが初期値）
まずこの記事では、基本的な3つの引数だけで式を組み立ててみましょう。
この表においては、まず【検索値】は【商品ID】で検索を行うため、A3に入力された値で検索を行うように指定します。
次の【検索範囲】ではA列に入力された商品IDを検索するので、【A5:A24】※A列の表末尾まででもOK を指定します。
最後の【戻り範囲】は、結果としてどの値を返すのかという指定をします。
このためB3セルに入力する式の場合は【B5:B24】となり、C3に入力する式の場合は【C5:C24】となります。
【B3の記載】
【C3の記載】
この式を確定すると【#N/A】のエラーが発生しますが、これは【検索値】であるA3の値が入っていないためですので心配はありません。
では実際に、検索を利用してみましょう。
今回は例として、【商品ID】が【D1006】の商品について問い合わせを受けたと想定します。
A3セルに【D1006】を入力して確定すると、対応する【商品名】と【商品価格】が自動で表示されました。
さて、同様の機能を持つVLOOKUPではどのような式が必要となるでしょうか。
VLOOKUPでは、以下のような式で記述します。
【=VLOOKUP(検索値,検索範囲,列番号,検索型）】
XLOOKUPが大きく異なるのは詳細検索についてももちろんですが、ポイントは【列番号】の部分です。
VLOOKUPでは【表示する列の番号】として、左から何番目の列の情報を表示するかという式の組み立て方をします。
このため、表示させる答えは常に【列】でなければなりません。
この点XLOOKUPは列ではなく具体的な範囲で指定をするため、たとえば横方向に作られた表であっても検索することができます。
また、表の一部分だけを検索することも可能です。
たとえば上の表にあるように、C1001以降の暗くなっている部分は【在庫切れ】であるとします。
このような場合、VLOOKUP関数ではたとえ在庫切れであったとしても検索すれば表示されてしまいます。
XLOOKUP関数では検索範囲を直接指定できるため、以下のように式を変更すれば在庫切れの商品を検索対象から外すことができます。
この状態で検索を行うと、【D1006】の商品IDは在庫が切れているため【#N/A】つまり【検索範囲に見つからない】という結果となります。
さて、ここでXLOOKUP関数の詳細検索機能である【見つからない場合】の値を変更してみましょう。
【戻り範囲】の次に、【見つからない場合】の値を書き加えます。
文字列を表示させたいときは、【” ”】※ダブルクォーテーション で囲みます。
これをB3・C3セルに反映させた場合、以下のような表示となります。
在庫切れの商品を検索した場合、よりわかりやすい表示となりました。
応用は基本的にVLOOKUPと同じ！
応用やエラー対処に関しては、基本的にVLOOKUPと同様に利用することができます。
- エラーの対処
- 別シートの参照
- 複数の検索範囲指定
- 文字列の検索抽出
これらに関してはVLOOKUPVLOOKUPでの使い方がそのまま利用可能です。
エラー対処や応用テクニック詳細を確認する↓
XLOOKUP関数の使い方・応用まとめ
XLOOKUP関数の基本的な使い方ができ、さらに応用ができるようになると、EXCELを使って簡易的な検索システムすら作ることが可能です。
社内外からの問い合わせや打ち合わせなどの際に迅速に在庫状況を検索することができるなど、メリットはかなり多いためビジネスの場面で役立てられるシーンも多いでしょう。
これまでページ内検索や目視で表を使っていたという方は、XLOOKUP関数をぜひ活用してみましょう。
エクセルでよく使う必須の関数を短時間でマスターする！↓
