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バージョン | 2010 | 2013 | 2016 | 2019 | 2021 | Office365 |
|---|---|---|---|---|---|---|
| 互換性 | ✕ | ✕ | ✕ | ✕ | ◯ | ◯ |
XLOOKUP関数が使えない環境での代用
XLOOKUP関数が使えないエクセルで代わりの方法を見つけるのは難しいでしょうが、関数を自作できる人なら、互換性のないExcelバージョンでもXLOOKUPを実行できると思います。
XLOOKUPの自作関数を配布されているサイトもあります。
XLOOKUP関数の引数と書式
「数式」→「関数ライブラリ」→「検索/行列」からXLOOKUPを選択します。
XLOOKUP関数の書式の構成はこうなります。
XLOOKUP関数の基本的な使い方
XLOOKUP関数で簡単な表を例にして、引数の指定を一つずつ確認していきましょう。
以下の表から送料1000円の予算内で送れる最大個数を求めます。
第一引数「検索値」
検索のキーとなる値を指定します。この例では予算1000円の入ったセルをクリックで指定します。
第二引数「検索範囲」
検索値を検索するセル範囲、または配列を指定します。この例では表の「送料」のデータ範囲をドラッグで指定します。
第三引数「戻り値範囲」
値を取り出すセル範囲、または配列を指定します。この例では「個数」のデータ範囲をドラッグで指定します。
第四引数「見つからない場合」
検索値が見つからなかった場合に表示する値を指定します。
この例では空白を表示する””を入力しました。
第五引数「一致モード」
検索値の検索方法を以下の対応する値で指定します。
一致モードに指定する値の一覧
| 値 | 一致モード |
|---|---|
| 0 | 完全一致（既定値） |
| -1 | 検索値の小さい方の近似値 |
| 1 | 検索値の大きい方の近似値 |
| 2 | 曖昧検索（検索値にワイルドカードが使用できる） |
XLOOKUP関数で検索値にワイルドカードを使う
「一致モード」で「2」を指定すると、検索値にワイルドカードが使えます。
といっても、抽出できるのは「検索範囲」で部分一致した最初のデータだけで、曖昧検索によって合致するすべてのデータが取得できるわけではありません。
例えば「一致モード」を2、「検索値」を”*デスク”と指定して金額を引き出すと、デスクを後方一致で含む商品名の一番最初のデータから抽出されます。
検索値に、一部だけしか思い出せない長いコード番号や商品名を指定するような場合には便利に使える機能です。
この例では一致モードに「-1」を入力して「小さい方の近似値」を指定しました。
第六引数「検索モード」
検索値を検索する順序を以下の対応する値で指定します。
検索モードに指定する値の一覧
| 値 | 検索モード |
|---|---|
| 1 | 検索範囲の上（先頭）から下（末尾）へ検索（既定値） |
| -1 | 検索範囲の下（末尾）から上（先頭）へ検索 |
| 2 | 検索範囲のデータが昇順に並んでいるとしてバイナリ検索 |
| -2 | 検索範囲のデータが降順に並んでいるとしてバイナリ検索 |
この例では「1」を入力して「上（先頭）から下（末尾）へ検索」を指定しましたが、省略してもOK。また、「送料」のデータは昇順に並んでいるので「2」を入力しても結果は同じです。
XLOOKUP関数で1000円未満の最大値に対応する個数が返りました。
数式 =XLOOKUP(E1,A2:A6,B2:B6,“”,-1,1)
XLOOKUP関数で複数の検索値を指定して一括検索
XLOOKUP関数では第一引数「検索値」をセル範囲や配列で指定することができます。
前章で使った表で、予算のセルを700、1000、1200の３つに増やして、まとめて検索してみましょう。
取得する個数の最初のセルを選択して、XLOOKUP関数の第一引数「検索値」に予算のセル範囲をドラッグで指定します。
第二～第六引数は前章と同じです。第六引数「検索モード」は省略しても構いません。
他の２つのセルにオートフィルコピーして完了です。※Office365ではスピル機能によってExcelが自動で予測した結果を返すため、最初のセルの数式が確定すると同時に、他の２つのセルの戻り値も表示されます。
XLOOKUP関数とVLOOKUPとの違い
圧倒的に使われている検索関数といえばVLOOKUPです。VLOOKUPを使っていると「こうできたらいいな」と思うことがありますが、XLOOKUPでそれが実現できます。
XLOOKUPは検索範囲の左側にある列を戻り値範囲にできる
VLOOKUP関数では検索範囲は左端の列に限定されています。戻り値を取得する列は２列目以降で、検索範囲より右側に位置していなくてはなりません。XLOOKUPでは検索範囲と戻り値範囲を自由に位置設定できます。
今度は「個数」の70を検索値にして、表の「個数」のデータ範囲を検索範囲に、「送料」のデータ範囲を戻り値範囲に指定します。一致モードは検索値の大きい方の近似値で「1」を指定、見つからない場合は空白を指定、検索モードは省略しています。
XLOOKUP関数で検索範囲より左にある列から戻り値を取得しました。
VLOOKUPで同じ結果を得ようと=VLOOKUP(E1,A2:B6,1,1)と数式を入れると#N/Aエラーが返りました。
VLOOKUPでエラーを消すIFERRORネストがXLOOKUPの「見つからない場合」で不要になる
VLOOKUP関数では検索値が未入力だったり、見つからなかった場合などに生じるエラーを非表示にするため、IFERROR関数に数式をネスト（入れ子）していました。
XLOOKUPでは引数「見つからない場合」の指定によって、IFERRORに数式をネストする作業が不要になりました。
INDEXとMATCH関数で実装したクロス抽出をXLOOKUP関数で実行する
VLOOKUPで列を検索し、HLOOKUPで行を検索して、その交差位置の値を取り出すことはできませんでした。クロス表の縦横で交差するデータを抽出するにはINDEX関数とMATCH関数を組み合わせていました。
XLOOKUP関数では引数「戻り値範囲」にもう一つXLOOKUPをネスト（入れ子）してクロス抽出でき、数式も分かりやすくなります。
以下の第１四半期の経費をまとめた表から「5月の通信費」を転記してみましょう。
まず、最初のXLOOKUP関数で「通信費」を縦に検索します。
数式を入れるセルを選択して、引数「検索値」に「通信費」と入ったセルを参照します。
「検索範囲」に経費の行見出しの範囲をドラッグで指定します。
「戻り値範囲」にはもう一つのXLOOKUP関数をネストして「6月」を横に検索します。
「検索値」に「6月」と入ったセルを参照、「検索範囲」に列見出しの月の範囲を指定、「戻り値範囲」には経費の数値のデータ範囲を指定します。
「見つからない場合」に空白文字を指定し、一致モード、検索モードは省略して数式を確定させます。
XLOOKUP関数でクロス表の縦横で交差するデータが抽出されました。
- XLOOKUPはVLOOKUPやHLOOKUPの機能を拡張させた便利な関数です
- XLOOKUP関数はExcel2021から一般公開された新関数で、使えるExcelバージョンはOffice365と2021～です
- VLOOKUP関数は検索範囲が左側限定ですが、XLOOKUP関数は右でも上でも下でも自由な位置を指定することができます
- VLOOKUP関数はエラーの始末にIFERRORを使いますが、XLOOKUP関数では「見つからない場合」を指定すればIFERRORは不要です
- XLOOKUP関数では「一致モード」の指定で検索値にワイルドカードが使えます
- XLOOKUP関数では「検索モード」の指定で検索の順序を「上から、下から、昇順、降順」の選択ができます
- XLOOKUP関数では複数の検索値を指定して一括検索でき、スピルによってまとめて表示できます
- INDEXとMATCH関数を組み合わせていたクロス抽出もXLOOKUPで実装できます