Excel（エクセル）を使った業務をしていると、一度はExcelファイルの中の値を探したり、取り出す業務をしたことがあるのではないでしょうか？
そのような業務の中で多く使われる関数にはVLOOKUP関数やHLOOKUP関数がありますが、さらに便利に使える、一つの関数で縦横方向のデータが検索可能になった「XLOOKUP関数」が2020年１月にリリースされました。
今回は、VLOOKUP関数とHLOOKUP関数の後継として、圧倒的に使いやすく便利になった「XLOOKUP関数」の使い方のポイントをご紹介します。
監修：みずき さん
EXCEL女子の研修講師をしています。Excel研修では「覚える」だけではなく「理解する」ことで、自身の力としてExcelスキルを身につけられる研修を実施しています。研修をするたびにExcelが大好きになり、Excelってすごい！と実感しています。Excelの他には写真が好きで食べ物や風景を撮影しています！
まず、XLOOKUP関数の数式は下記の通りです。
=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
範囲または列ごとに情報を検索し、該当するデータを取り出す関数です。6つの引数のうち、検索値、検索範囲、戻り範囲の3つは必須、[見つからない場合]、[一致モード]、[検索モード]の3つは省略可能な引数です。
| 設定値 | 説明 |
| 0または省略 | 完全一致。見つからない場合は「#N/A」が表示されます。（既定の設定） |
| -1 | 完全一致。見つからない場合は次に小さい項目が表示されます。 |
| 1 | 完全一致。見つからない場合は次に大きい項目が表示されます。 |
| 2 | *、?、~（チルダ）などのワイルドカードとの一致。 |
| 設定値 | 説明 |
| 1または省略 | 先頭から末尾に向かって検索します。（既定の設定） |
| -1 | 末尾から先頭へ逆方向に検索します。 |
| 2 | 昇順に入れ替えられた検索範囲を使ってバイナリ検索します。（※） |
| -2 | 降順に入れ替えられた検索範囲を使ってバイナリ検索します。（※） |
（※）バイナリ検索とは、真ん中から半分に分けて条件に合わない方を消す、という作業を複数回繰り返すことで候補を絞り込んでいく検索方法です。計算量のデータが膨大なときに使用します。
それでは次に、VLOOKUP関数と比較しながら、XLOOKUP関数のおさえておきたいポイントを確認していきましょう。
XLOOKUP関数にはVLOOKUP関数にはない様々なポイントがあります。VLOOKUP関数では実現するのに手間がかかっていた事やできなかった事も、XLOOKUPでは簡単に実現できるようになりました。
以下５つのVLOOKUP関数との違いや使い方のポイントを押さえ、XLOOKUP関数を業務に活用してみてください。
VLOOKUP関数は縦方向のみを検索します。またVLOOKUP関数は検索範囲が左端にないと検索ができません。XLOOKUP関数は縦横指定なく、検索範囲を自由に指定することができます。そして、検索範囲が左端にない場合でも検索範囲を指定することが可能です。
VLOOKUP関数では検索値が見つからなかった場合、#N/Aエラーを返します。エラー表示をしたくない場合IF関数やIFERROR関数などを組み合わせて使用する必要があります。一方、XLOOKUP関数の場合は、引数の[見つからない場合]に表示する値を指定することができるため、一つの関数で完結させることができます。
VLOOKUP関数は、完全一致で検索したい場合、引数の「検索の型」にFALSEを指定する必要があります。省略するとTRUE型となり近似値一致での検索となります。一方、XLOOKUP関数は完全一致が既定の設定となっており、引数の「一致モード」を省略しても完全一致で検索することができます。
例えば以下の表のようにいちごの価格を検索する場合、一致するデータが複数あるとVLOOKUP関数では検索できません。そんな時は「&」を使って商品番号と商品名のふたつの列を連結させた値で検索し、検索範囲も連結させて検索します。複数列にわたる条件で検索することができるところもXLOOKUP関数の大きな特徴です。
データを取り出す際、VLOOKUP関数は左から数えた「列番号」を指定しています。そのため、元データの列数が増減してしまうと列番号（指定列）が変わり、元データに変更があった場合は、その都度列番号を指定し直す必要があります。一方、XLOOKUP関数はセル範囲で指定するため、元データに変更があっても関数を修正する必要はありません。
以上、5つのポイントについてご紹介しました。このポイントを踏まえたうえで、さっそくXLOOKUP関数を使用してみましょう。
まず、XLOOKUP関数を使用する前に、ご自身が使用しているExcelがXLOOKUP関数を使用できるバージョンかどうか、確認しましょう。
XLOOKUP関数は、Microsoft365またはExcel2021でのみ使用することができます。永続ライセンス版のExcel2016やExcel2019では使用することができませんが、永続ライセンス版のExcel2021では使用可能です（2023年4月現在）また、使用できるはずのMicrosoft365やExcel2021で、数式バーに「=X」と入力してもXLOOKUP関数が表示されない場合は、プログラムを更新する必要があります。
（1）Excel《ファイル》から《アカウント》をクリックする
（2）《更新オプション》から《今すぐ更新》をクリックする
更新が完了したら、XLOOKUP関数が使用できるようになります！
※企業によってはアップデートが配信されていない場合もあります。
こちらは商品の注文表です。商品No.を入力して、該当する商品名を商品リストから検索するXLOOKUP関数を作成します。今回はセル【C3】に入力された商品No.「P0008」の商品名を検索し、セル【C4】に入力させます。
（1）セル【C3】に「P0008」と入力する
（2）セル【C4】の数式バーに「=XLOOKUP(C3,G4:G13,E4:E13,"該当なし",0,-1)」と入力する
（この数式は、セル【C3】に入力された値をG列から探し、その商品名の商品No.を探して表示して下さい。ただし、下から数えて最初に一致するものとします。という意味です。）
XLOOKUP関数では、［検索範囲］と［戻り範囲］で指定するセル範囲は、同じ高さに指定しないと数式の結果がエラーとなります。
VLOOKUP関数のように左から何列目かなど数えることなく、簡単に検索結果を表示することができます。
先ほどは必須の引数のみを使用して作成しましたが、省略可能な引数も使用して検索してみましょう。こちらのシートには「P0010」までの商品No.しか存在しませんが、存在しない商品No.「P0011」を入力した時、セル【C4】に「該当なし」と表示させるよう指定してみましょう。
（1）セル【C3】に「P0011」を入力しておく
（2）セル【C4】の数式バーに「=XLOOKUP(C3,E4:E13,G4:G13,"該当なし")」と入力する
商品No.「P0011」はこの表には存在しないので、「該当なし」と表示されました。
IFERROR関数とVLOOKUP関数を組み合わせた数式にしなくても、XLOOKUP関数一つで検索値が見つからなかった場合の表示方法も指定することができます。
次は、先ほどと同じ表を使用して商品名を入力し、商品No.が表示される数式を省略可能の引数も取り入れて作成してみましょう。ここでは複数存在する商品名「プリンター」の中で一番下にある「P0010」がセル【C4】に表示されるようにすることとします。
（1）セル【C3】に「プリンター」と入力しておく
（2）セル【C4】の数式バーに「=XLOOKUP(C3,G4:G13,E4:E13,"該当なし",0,-2)」と入力する
（この数式は、セル【C3】に入力された値をG列から探し、その商品名の商品No.を探して表示してください。ただし、下から数えて最初に一致するものとします。という意味です。）
セル【C4】には「プリンター」の中で1番下に入力されている「No.0010」が表示されました。
今回は、省略可能な引数も全て使用して作成しました。省略可能の3つの引数のどれか一つでも使用する場合はXLOOKUP関数の構文に沿って、すべて順に入力する必要がありますのでお気をつけください。
今回は、Microsoft365とExcel2021（2023年4月現在）で使用可能な「XLOOKUP関数」についてご紹介しました。
XLOOKUP関数は、VLOOKUP関数の複雑な部分が改善されたとてもシンプルでわかりやすい関数です。XLOOKUP関数を上手に使いこなして、業務効率をグン！とあげていきましょう。
当社では、『Microsoft認定トレーナー』資格を持つEXCEL女子による「Excel研修」を提供しております。書籍化するなど注目いただいている研修です。
社員教育に時間を割けない、Excelスキルの底上げをしたいなどお悩みでしたら、ぜひお気軽にお問い合わせください。