XLOOKUP関数の使い方|VLOOKUPが進化した便利な検索関数

Excel(エクセル)のXLOOKUP関数の使い方
ExcelVLOOKUP関数関数

Excel(エクセル)のXLOOKUP関数の使い方|VLOOKUPが進化した便利な検索関数

XLOOKUPは最新の検索関数です。VLOOKUP関数を進化させ、HLOOKUP関数を代用でき、INDEX・MATCH関数を組み合わせていたクロス抽出も実装できます。XLOOKUP関数の基本的な使い方から応用まで、できるだけ分かりやすく解説していきます。

使用関数:XLOOKUP

XLOOKUP(エックスルックアップ)関数とは?

検索して値を抽出する関数です。最も使われているのはVLOOKUP関数ですが、XLOOKUPは機能性も柔軟性も格段にアップしています。

XLOOKUP関数が使えるExcelバージョン

XLOOKUP関数はOffice365に新加入し、単体のExcelでは2021から一般公開されています。Excel2010、2013、2016、2019ではXLOOKUPは使えませんのでご注意下さい。

Excelバージョン20102013201620192021Office365
互換性

XLOOKUP関数が使えない環境での代用

XLOOKUP関数が使えないエクセルで代わりの方法を見つけるのは難しいでしょうが、関数を自作できる人なら、互換性のないExcelバージョンでもXLOOKUPを実行できると思います。

XLOOKUPの自作関数を配布されているサイトもあります。

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

XLOOKUP関数の引数と書式

「数式」→「関数ライブラリ」→「検索/行列」からXLOOKUPを選択します。
関数ライブラリの「検索/行列」からXLOOKUPを選択

XLOOKUP関数の書式の構成はこうなります。
XLOOKUP関数の書式の構成

XLOOKUP関数の基本的な使い方

XLOOKUP関数で簡単な表を例にして、引数の指定を一つずつ確認していきましょう。

以下の表から送料1000円の予算内で送れる最大個数を求めます。
XLOOKUP関数で1000円以内で送れる個数を取得する表

第一引数「検索値」

検索のキーとなる値を指定します。この例では予算1000円の入ったセルをクリックで指定します。
XLOOKUP関数の引数「検索値」を指定

第二引数「検索範囲」

検索値を検索するセル範囲、または配列を指定します。この例では表の「送料」のデータ範囲をドラッグで指定します。
XLOOKUP関数の引数「検索範囲」を指定

第三引数「戻り値範囲」

値を取り出すセル範囲、または配列を指定します。この例では「個数」のデータ範囲をドラッグで指定します。
XLOOKUP関数の引数「戻り値範囲」を指定

第四引数「見つからない場合」

検索値が見つからなかった場合に表示する値を指定します。
この例では空白を表示する””を入力しました。
XLOOKUP関数の引数「見つからない場合」を指定

第五引数「一致モード」

検索値の検索方法を以下の対応する値で指定します。

一致モードに指定する値の一覧

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

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

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

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

検索値に、一部だけしか思い出せない長いコード番号や商品名を指定するような場合には便利に使える機能です。

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

第六引数「検索モード」

検索値を検索する順序を以下の対応する値で指定します。

検索モードに指定する値の一覧

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

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

XLOOKUP関数で1000円未満の最大値に対応する個数が返りました。
XLOOKUPで検索した結果
数式 =XLOOKUP(E1,A2:A6,B2:B6,“”,-1,1)

XLOOKUP関数で複数の検索値を指定して一括検索

XLOOKUP関数では第一引数「検索値」をセル範囲や配列で指定することができます。

前章で使った表で、予算のセルを700、1000、1200の3つに増やして、まとめて検索してみましょう。
予算のセルを700、1000、1200の3つに増やしXLOOKUPで検索する表

取得する個数の最初のセルを選択して、XLOOKUP関数の第一引数「検索値」に予算のセル範囲をドラッグで指定します。
XLOOKUP関数の第一引数「検索値」に予算のセル範囲をドラッグで指定

第二~第六引数は前章と同じです。第六引数「検索モード」は省略しても構いません。
XLOOKUP関数の第二~第六引数を指定

他の2つのセルにオートフィルコピーして完了です。※Office365ではスピル機能によってExcelが自動で予測した結果を返すため、最初のセルの数式が確定すると同時に、他の2つのセルの戻り値も表示されます。
XLOOKUP関数で複数の検索値に対応する結果が一括表示

XLOOKUP関数とVLOOKUPとの違い

圧倒的に使われている検索関数といえばVLOOKUPです。VLOOKUPを使っていると「こうできたらいいな」と思うことがありますが、XLOOKUPでそれが実現できます。

XLOOKUPは検索範囲の左側にある列を戻り値範囲にできる

VLOOKUP関数では検索範囲は左端の列に限定されています。戻り値を取得する列は2列目以降で、検索範囲より右側に位置していなくてはなりません。XLOOKUPでは検索範囲と戻り値範囲を自由に位置設定できます。

今度は「個数」の70を検索値にして、表の「個数」のデータ範囲を検索範囲に、「送料」のデータ範囲を戻り値範囲に指定します。一致モードは検索値の大きい方の近似値で「1」を指定、見つからない場合は空白を指定、検索モードは省略しています。
検索範囲より左側の列を戻り値範囲にしたXLOOKUP関数の引数の指定

XLOOKUP関数で検索範囲より左にある列から戻り値を取得しました。
検索範囲より左側の列を戻り値範囲にしたXLOOKUP関数の結果

VLOOKUPで同じ結果を得ようと=VLOOKUP(E1,A2:B6,1,1)と数式を入れると#N/Aエラーが返りました。
VLOOKUPで検索範囲より左側の列を戻り値範囲に指定するとエラー

VLOOKUPでエラーを消すIFERRORネストがXLOOKUPの「見つからない場合」で不要になる

VLOOKUP関数では検索値が未入力だったり、見つからなかった場合などに生じるエラーを非表示にするため、IFERROR関数に数式をネスト(入れ子)していました。
VLOOKUP関数でIFERROR関数に数式をネストしたセルとしなかったセルの比較

XLOOKUPでは引数「見つからない場合」の指定によって、IFERRORに数式をネストする作業が不要になりました。
XLOOKUP関数の引数「見つからない場合」に空白文字を指定

INDEXとMATCH関数で実装したクロス抽出をXLOOKUP関数で実行する

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

以下の第1四半期の経費をまとめた表から「5月の通信費」を転記してみましょう。
XLOOKUP関数を実行する表

まず、最初のXLOOKUP関数で「通信費」を縦に検索します。
数式を入れるセルを選択して、引数「検索値」に「通信費」と入ったセルを参照します。
XLOOKUP関数の検索値を指定

「検索範囲」に経費の行見出しの範囲をドラッグで指定します。
XLOOKUPの「検索範囲」に経費の行見出しの範囲をドラッグで指定

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

「見つからない場合」に空白文字を指定し、一致モード、検索モードは省略して数式を確定させます。
XLOOKUP関数でクロス抽出する引数の指定が完了

XLOOKUP関数でクロス表の縦横で交差するデータが抽出されました。
XLOOKUP関数の結果

XLOOKUP関数の使い方 まとめ
  • XLOOKUPはVLOOKUPやHLOOKUPの機能を拡張させた便利な関数です
  • XLOOKUP関数はExcel2021から一般公開された新関数で、使えるExcelバージョンはOffice365と2021~です
  • VLOOKUP関数は検索範囲が左側限定ですが、XLOOKUP関数は右でも上でも下でも自由な位置を指定することができます
  • VLOOKUP関数はエラーの始末にIFERRORを使いますが、XLOOKUP関数では「見つからない場合」を指定すればIFERRORは不要です
  • XLOOKUP関数では「一致モード」の指定で検索値にワイルドカードが使えます
  • XLOOKUP関数では「検索モード」の指定で検索の順序を「上から、下から、昇順、降順」の選択ができます
  • XLOOKUP関数では複数の検索値を指定して一括検索でき、スピルによってまとめて表示できます
  • INDEXとMATCH関数を組み合わせていたクロス抽出もXLOOKUPで実装できます
タイトルとURLをコピーしました