使用関数：XLOOKUP
ExcelのXLOOKUP（エックスルックアップ）関数はVLOOKUP関数の後継として2021年から一般公開された検索関数です。VLOOKUP関数に慣れた中級～上級のExcelユーザーよりも、前知識のない初心者程すぐに学習できて使いやすいと言えます。Microsoft365やExcel2021を使用中でXLOOKUP関数が使える環境であるなら、ためらうことはありません。XLOOKUPはあなたが思う以上に進化した関数で、間違いなくVLOOKUPに代わる代表的なExcel関数になるでしょう。
自社で管理するリストからID番号やコードに対応する顧客名・商品名などのデータを取り出して転記する機能はVLOOKUPと同じですが、XLOOKUP関数は縦からも横からも検索でき、検索値の列を範囲の左端にする規則もありません。引数の指定で下から上への検索も可能、未入力のセルに出る#N/Aや0を空白に置き換えるのも数式内で完結します。スピル機能に対応しているので、複数の検索値を指定し、数式をコピーせずに複数行にまとめてデータ転記を行うことができます。
本記事では、「本当にVLOOKUP関数の代わりになり得るのか」「VLOOKUP関数とどれほどの違いがあるのか」分からないという方たちに向け、「Excelの進化した検索関数はこれ程違う！」を主題にしてXLOOKUP関数の使い方を詳しく解説します。
XLOOKUP関数が使えるExcelバージョン
旧バージョンのExcelではXLOOKUP関数、及びスピル（Spill）機能は使えません。以下にXLOOKUP関数の対応バージョン情報をまとめました。
| Excelバージョン | 2010 | 2013 | 2016 | 2019 | 2021 | Microsoft365 |
|---|---|---|---|---|---|---|
| 互換性 | ✕ | ✕ | ✕ | ✕ | ◯ | ◯ |
XLOOKUP関数が使えない環境での代用
XLOOKUP関数が使えないエクセル環境で代わりの方法を見つけるのは難しいでしょうが、関数を自作できる人なら、互換性のないExcelバージョンでもXLOOKUPを実行できると思います。
XLOOKUPの自作関数を配布されているサイトもあります。
XLOOKUP関数の引数の指定方法
XLOOKUP関数の構文を、引数それぞれの指定方法を確認しながら、VLOOKUP関数との違いも合わせて説明していきます。
荷物の重さと送付にかかる料金の対応表から、XLOOKUP関数で2500円以内で送れる重さの最大値を求めます。
XLOOKUP関数の構文と引数の構成は以下のようになります。
引数それぞれの説明と指定方法です。
- 第１引数【検索値】
- XLOOKUP関数の１番目の引数「検索値」には検索のキーとなるデータ「どの値を検索するか」を指定します。全角半角は区別して検索されますが、英字の大文字小文字は区別されません。この例では「予算金額」の入ったセルを参照します。
XLOOKUP関数では複数の検索値を指定できるこの引数はVLOOKUP関数と同じ名前ですが、XLOOKUP関数では複数の検索値を指定できるという点が違います。後述するスピル機能を使って、指定した複数の検索値に対応するデータをまとめて取り出すことが可能です。
- 第２引数【検索範囲】
- VLOOKUP関数の２番目の引数「検索範囲」には「どこからどこまでを検索するか」を指定します。VLOOKUP関数の「範囲」に対応する引数です。
この例では「重さと送付にかかる料金の対応表」のデータ範囲をドラッグで指定します。
VLOOKUP関数の「範囲」には規則があるが、XLOOKUP関数では不要VLOOKUP関数の「範囲」にする表では検索値を探す列を一番左にするという規則がありますが、XLOOKUP関数の「検索範囲」に特定の規則はありません。
- 第３引数【戻り範囲】
- XLOOKUP関数の３番目の引数「戻り範囲」には「どこからデータを取り出すか」をセル範囲、または配列で指定します。
２番目の引数「検索範囲」と「戻り範囲」はセルの高さを揃えて指定する必要があります。高さが違うとエラー#VALUE!が返ります。
この例では「重さと送付にかかる料金の対応表」の「重さ」のデータ範囲をドラッグで指定します。
XLOOKUP関数では検索値より左側のデータも取り出せるVLOOKUPではデータを取り出す列を先頭列からの番号で指定しましたが、XLOOKUP関数ではセル範囲をドラッグで指定できます。VLOOKUPではできなかった検索値の左側にあるデータも取り出すことができます。
- 第４引数【見つからない場合】
- XLOOKUP関数の４番目の引数「見つからない場合」には「検索値が検索範囲の中に見つからなかった場合、何を表示するか」を指定します。空白文字「””」以外にも「”該当データなし”」など文字列を指定して表示させることもできます。
VLOOKUPでは入れ子で処理した#N/Aや0をXLOOKUPでは引数指定で処理できる４番目の引数「見つからない場合」は省略もできますが、省略した場合、検索値が未入力の時にエラー値#N/Aが返ります。VLOOKUP関数ではIFERRORに入れ子して空白にしていましたが、XLOOKUP関数ではこの第４引数「見つからない場合」を指定するだけでエラー値を処理できます。
- 第５引数【一致モード】
- XLOOKUP関数の５番目の引数「一致モード」には「検索値の照合を近似値検索で行うか完全一致検索で行うか」を以下の対応する値で指定します。
値 一致モード 0 完全一致（既定値） -1 検索値の小さい方の近似値 1 検索値の大きい方の近似値 2 曖昧検索（検索値にワイルドカードが使用できる） この例では一致モードに「-1」を入力して「小さい方の近似値」を指定しました。
XLOOKUP関数の検索値にワイルドカードを使う「一致モード」で「2」を指定すると、検索値にワイルドカードが使えます。
といっても、対象は「検索範囲」で部分一致した最初のデータだけで、曖昧検索によって一致するすべてのデータを取り出せるわけではありません。例えば「一致モード」を2、「検索値」を”*デスク”と指定して金額を引き出すと、デスクを後方一致で含む商品名の一番最初のデータから抽出されます。
検索値に、一部しか分からないID番号や顧客の名前を指定するような場合には便利に使える機能です。
- 第６引数【検索モード】
- XLOOKUP関数の６番目の引数「検索モード」には「検索値をどの方向からどの方向へ検索するか」を以下の対応する値で指定します。
値 検索モード 1 検索範囲の上（先頭）から下（末尾）へ検索（既定値） -1 検索範囲の下（末尾）から上（先頭）へ検索 2 検索範囲のデータが昇順に並んでいるとしてバイナリ検索 -2 検索範囲のデータが降順に並んでいるとしてバイナリ検索 この例では省略しました。省略すると「1」を入力して「上（先頭）から下（末尾）へ検索」を指定したことになります。また、「料金」のデータは昇順に並んでいるので「2」を入力しても結果は同じです。
XLOOKUP関数では検索する範囲の数値を昇順にソートする必要はないVLOOKUPでは検索値の照合にTRUE（近似値）を指定した場合には検索する範囲の数値を昇順にソートしなければなりませんでしたが、XLOOKUP関数では不要です。この手間を省けるのもVLOOKUP関数とXLOOKUP関数の大きな違いです。
XLOOKUP関数を使って、2500円以下で直近の最大値に対応する重さが返りました。
数式➔ =XLOOKUP(D4,B4:B9,A4:A9,”該当データなし”,-1)
スピル機能で大量データの処理速度がUP
XLOOKUP関数は同時期に加わったスピルに対応しています。スピル（Spill）は数式の結果を予測して、オートフィルコピーせずにまとめて表示する機能です。
前章の例で予算金額のセルを1000、2500、3000の３つに増やして、その複数のセル範囲をまとめて検索値に指定します。
他の引数は上記と同じです。スピル機能によってExcelが自動で予測した結果を返すため、最初のセルのXLOOKUP関数が確定すると同時に、他の２つのセルの戻り値も表示されます。
INDEXとMATCHを組み合せたクロス抽出をXLOOKUP関数で実装する
VLOOKUPで列を検索し、HLOOKUPで行を検索して、その交差位置の値を取り出すことはできません。表の縦横でクロスするデータを抽出するにはINDEX関数とMATCH関数を組み合わせていました。
XLOOKUP関数では引数「戻り値範囲」にもう一つXLOOKUPをネスト（入れ子）してクロス抽出でき、数式も分かりやすくなります。
経費をまとめた表から「5月の通信費」を転記してみましょう。まず、最初のXLOOKUP関数で検索値「通信費」を縦に検索します。
数式を入れるセルを選択して、引数「検索値」に「通信費」と入ったセルを参照し、「検索範囲」に経費の行見出しの範囲をドラッグで指定します。
「戻り値範囲」にはもう一つのXLOOKUP関数をネストして「6月」を横に検索します。
「検索値」に「6月」と入ったセルを参照、「検索範囲」に列見出しの月の範囲を指定、「戻り値範囲」には経費の数値のデータ範囲を指定します。
「見つからない場合」に空白文字を指定し、一致モード、検索モードは省略して数式を確定させます。XLOOKUP関数で表の縦横でクロスする場所のデータが抽出されました。
XLOOKUP関数がVLOOKUP関数とどれ程違うか、お分かりいただけたでしょうか？ スピル機能も登場した今、ビジネスの様々な場面でスピーディに入力業務を行うため、XLOOKUP関数の存在は欠かせなくなります。「これからはVLOOKUPに代わりXLOOKUP関数の世紀である」と割り切って、早目にXLOOKUP関数への切り替えを行うことをおすすめします。