XLOOKUP関数の使い方|Excelの進化した検索関数はこれ程違う!

XLOOKUP関数の使い方|Excelの進化した検索関数はこれ程違う!

使用関数:XLOOKUP

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」を2019、2016,2013などで使えるようにしてみた
Office365のアップデートで注目の新関数「XLOOKUP」が実装されました。滅茶苦茶便利なのですが、2007、2010、2013、2016などの旧バージョンでは使えないので自作してみました。

XLOOKUP関数の引数の指定方法

XLOOKUP関数の構文を、引数それぞれの指定方法を確認しながら、VLOOKUP関数との違いも合わせて説明していきます。

荷物の重さと送付にかかる料金の対応表から、XLOOKUP関数で2500円以内で送れる重さの最大値を求めます。
XLOOKUP関数で2500円以内で送れる重さを取得する表

XLOOKUP関数の構文と引数の構成は以下のようになります。
XLOOKUP関数の構文と引数の構成

引数それぞれの説明と指定方法です。

第1引数【検索値】
XLOOKUP関数の1番目の引数「検索値」には検索のキーとなるデータ「どの値を検索するか」を指定します。全角半角は区別して検索されますが、英字の大文字小文字は区別されません。この例では「予算金額」の入ったセルを参照します。
XLOOKUP関数の第1引数「検索値」をセル参照で指定

XLOOKUP関数では複数の検索値を指定できる

この引数はVLOOKUP関数と同じ名前ですが、XLOOKUP関数では複数の検索値を指定できるという点が違います。後述するスピル機能を使って、指定した複数の検索値に対応するデータをまとめて取り出すことが可能です。

第2引数【検索範囲】
VLOOKUP関数の2番目の引数「検索範囲」には「どこからどこまでを検索するか」を指定します。VLOOKUP関数の「範囲」に対応する引数です。

この例では「重さと送付にかかる料金の対応表」のデータ範囲をドラッグで指定します。
XLOOKUP関数の第2引数「検索範囲」を指定

VLOOKUP関数の「範囲」には規則があるが、XLOOKUP関数では不要

VLOOKUP関数の「範囲」にする表では検索値を探す列を一番左にするという規則がありますが、XLOOKUP関数の「検索範囲」に特定の規則はありません。

第3引数【戻り範囲】
XLOOKUP関数の3番目の引数「戻り範囲」には「どこからデータを取り出すか」をセル範囲、または配列で指定します。

2番目の引数「検索範囲」と「戻り範囲」はセルの高さを揃えて指定する必要があります。高さが違うとエラー#VALUE!が返ります。

この例では「重さと送付にかかる料金の対応表」の「重さ」のデータ範囲をドラッグで指定します。
XLOOKUP関数の第3引数「戻り範囲」を入力

XLOOKUP関数では検索値より左側のデータも取り出せる

VLOOKUPではデータを取り出す列を先頭列からの番号で指定しましたが、XLOOKUP関数ではセル範囲をドラッグで指定できます。VLOOKUPではできなかった検索値の左側にあるデータも取り出すことができます。

第4引数【見つからない場合】
XLOOKUP関数の4番目の引数「見つからない場合」には「検索値が検索範囲の中に見つからなかった場合、何を表示するか」を指定します。空白文字「””」以外にも「”該当データなし”」など文字列を指定して表示させることもできます。
XLOOKUP関数の第4引数「見つからない場合」を入力

VLOOKUPでは入れ子で処理した#N/Aや0をXLOOKUPでは引数指定で処理できる

4番目の引数「見つからない場合」は省略もできますが、省略した場合、検索値が未入力の時にエラー値#N/Aが返ります。VLOOKUP関数ではIFERRORに入れ子して空白にしていましたが、XLOOKUP関数ではこの第4引数「見つからない場合」を指定するだけでエラー値を処理できます。

第5引数【一致モード】
XLOOKUP関数の5番目の引数「一致モード」には「検索値の照合を近似値検索で行うか完全一致検索で行うか」を以下の対応する値で指定します。

一致モード
0 完全一致(既定値)
-1 検索値の小さい方の近似値
1 検索値の大きい方の近似値
2 曖昧検索(検索値にワイルドカードが使用できる)

この例では一致モードに「-1」を入力して「小さい方の近似値」を指定しました。
XLOOKUP関数の引数「一致モード」に-1を指定

XLOOKUP関数の検索値にワイルドカードを使う

「一致モード」で「2」を指定すると、検索値にワイルドカードが使えます。
といっても、対象は「検索範囲」で部分一致した最初のデータだけで、曖昧検索によって一致するすべてのデータを取り出せるわけではありません。

例えば「一致モード」を2、「検索値」を”*デスク”と指定して金額を引き出すと、デスクを後方一致で含む商品名の一番最初のデータから抽出されます。
XLOOKUP関数で検索値にワイルドカードを使って指定した例

検索値に、一部しか分からないID番号や顧客の名前を指定するような場合には便利に使える機能です。

第6引数【検索モード】
XLOOKUP関数の6番目の引数「検索モード」には「検索値をどの方向からどの方向へ検索するか」を以下の対応する値で指定します。

検索モード
1 検索範囲の上(先頭)から下(末尾)へ検索(既定値)
-1 検索範囲の下(末尾)から上(先頭)へ検索
2 検索範囲のデータが昇順に並んでいるとしてバイナリ検索
-2 検索範囲のデータが降順に並んでいるとしてバイナリ検索

この例では省略しました。省略すると「1」を入力して「上(先頭)から下(末尾)へ検索」を指定したことになります。また、「料金」のデータは昇順に並んでいるので「2」を入力しても結果は同じです。

XLOOKUP関数では検索する範囲の数値を昇順にソートする必要はない

VLOOKUPでは検索値の照合にTRUE(近似値)を指定した場合には検索する範囲の数値を昇順にソートしなければなりませんでしたが、XLOOKUP関数では不要です。この手間を省けるのもVLOOKUP関数とXLOOKUP関数の大きな違いです。

XLOOKUP関数を使って、2500円以下で直近の最大値に対応する重さが返りました。
XLOOKUPで検索した結果
数式➔ =XLOOKUP(D4,B4:B9,A4:A9,”該当データなし”,-1)

スピル機能で大量データの処理速度がUP

XLOOKUP関数は同時期に加わったスピルに対応しています。スピル(Spill)は数式の結果を予測して、オートフィルコピーせずにまとめて表示する機能です。

前章の例で予算金額のセルを1000、2500、3000の3つに増やして、その複数のセル範囲をまとめて検索値に指定します。
XLOOKUP関数の検索値を複数指定する

他の引数は上記と同じです。スピル機能によってExcelが自動で予測した結果を返すため、最初のセルのXLOOKUP関数が確定すると同時に、他の2つのセルの戻り値も表示されます。
スピル機能によって最初のセルのXLOOKUP関数が確定すると同時に、他の2つのセルの戻り値も表示

INDEXとMATCHを組み合せたクロス抽出をXLOOKUP関数で実装する

VLOOKUPで列を検索し、HLOOKUPで行を検索して、その交差位置の値を取り出すことはできません。表の縦横でクロスするデータを抽出するにはINDEX関数とMATCH関数を組み合わせていました。
XLOOKUP関数では引数「戻り値範囲」にもう一つXLOOKUPをネスト(入れ子)してクロス抽出でき、数式も分かりやすくなります。

経費をまとめた表から「5月の通信費」を転記してみましょう。まず、最初のXLOOKUP関数で検索値「通信費」を縦に検索します。
数式を入れるセルを選択して、引数「検索値」に「通信費」と入ったセルを参照し、「検索範囲」に経費の行見出しの範囲をドラッグで指定します。
XLOOKUP関数の「検索値」と「検索範囲」を指定

「戻り値範囲」にはもう一つのXLOOKUP関数をネストして「6月」を横に検索します。
「検索値」に「6月」と入ったセルを参照、「検索範囲」に列見出しの月の範囲を指定、「戻り値範囲」には経費の数値のデータ範囲を指定します。
XLOOKUPの「戻り値範囲」にもう一つのXLOOKUP関数をネスト

「見つからない場合」に空白文字を指定し、一致モード、検索モードは省略して数式を確定させます。XLOOKUP関数で表の縦横でクロスする場所のデータが抽出されました。
XLOOKUP関数でクロス抽出した結果

これからはVLOOKUPに代わりXLOOKUP関数の世紀

XLOOKUP関数がVLOOKUP関数とどれ程違うか、お分かりいただけたでしょうか? スピル機能も登場した今、ビジネスの様々な場面でスピーディに入力業務を行うため、XLOOKUP関数の存在は欠かせなくなります。「これからはVLOOKUPに代わりXLOOKUP関数の世紀である」と割り切って、早目にXLOOKUP関数への切り替えを行うことをおすすめします。

タイトルとURLをコピーしました