【エクセル】XLOOKUP関数の使い方(VLOOKUPの後継関数)

2020年にExcelのVLOOKUP関数やHLOOKUP関数の後継となる関数「XLOOKUP」がMicrosoftにより発表されました。VLOOKUP関数の弱点を補い、より便利により柔軟に使いやすくなっています。「Office 365」もしくは「Office 2021」以降のバージョンでXLOOKUP関数を使用できます。ここではXLOOKUP関数の使い方について解説します。

関連リンク
Office 2019以前のバージョンでのExcelが使われる可能性がある場合は下記方法を選択することをおすすめします。

XLOOKUP関数のポイント

XLOOKUP関数のポイントとなる部分は下記となります。

1. 検索値を含む列を左端にしなくても良い
2. 一致モードのデフォルトが完全一致
3. 戻り値を一気に表示可能
4. 垂直配列と水平配列の両方で機能
5. 逆検索を実行可能

ポイント１：検索値を含む列を左端にしなくても良い

VLOOKUP関数では検索値を含む列を必ず左端に設定する必要がありましたが、XLOOKUP関数では左端である必要はなくなりました。

ポイント２：一致モードのデフォルトが完全一致

一致モードは場合により完全一致にしないと正しい結果を返せないことがあるため、基本的には完全一致に指定するべきなのですが、VLOOKUP関数では一致モードのデフォルトが近似値含めての検索だったため、完全一致にするには「false」を毎回指定する必要がありました。XLOOKUP関数ではデフォルトが完全一致なので、多くの場合、設定を省略することができます。

ポイント３：戻り値を一気に表示可能

XLOOKUP関数は計算結果である戻り値を複数のセルに渡って返すことが可能です（スピル）。下記のように戻り範囲を複数列に指定すると、複数列で返却値が返却されます。列ごとに数式を入力しなければならなかったVLOOKUP関数に比べて効率的に設定ができます。

関連リンク
スピルについてわかりやすく解説

ポイント４：縦方向と横方向の両方で機能

従来では縦方向の検索はVLOOKUP関数、横方向の検索はHLOOKUP関数を使用する必要がありましたが、XLOOKUP関数では縦方向も、横方向もどちらも検索できます。例えば、横方向であれば下記のように設定します。

ポイント５：逆検索を実行可能

検索モードの切替により、逆検索も実行できるようになりました。VLOOKUP関数ではできなかった機能です。6つめの設定値（引数）を「ｰ1」に設定することで逆検索になります。

XLOOKUP関数の設定項目

XLOOKUP関数には設定項目が6つありますがそのうち3つは省略可能で「検索値」、「検索範囲」、「戻り範囲」の3つ設定すれば機能します。関数では設定項目のことを引数（ひきすう）と呼び、引数は下記のように「,」区切りで設定していきます。

それぞれの引数について詳しく説明していきます。

引数１：検索値

検索値には検索する値を指定します。例えば、下記の例ではF3に指定しているIDを検索値として指定します。

引数２：検索範囲

検索範囲を指定します。引数１で指定した検索値が入っている範囲を指定します。下記例では縦方向に検索範囲を指定していますが、XLOOKUP関数では縦方向、横方向どちらでも検索できます。

引数３：戻り範囲

結果として返す戻り値が入っている範囲を指定します。下記例では検索したIDに対する商品名を返したいので、商品名の列を指定します。注意点として検索範囲に対応する範囲となるので、行、または列の数を合わせて指定する必要があります。

引数４：見つからない場合の値（省略可）

検索値のデータが見つからない場合に返す値を指定します。直接数式に文字列を指定する場合は「”」で囲んで指定します。下記の例では検索値が検索範囲内に見つからない場合は「Not found」を表示するよう設定しています。

引数５：一致モード（省略可）

検索する方法を指定します。デフォルトは「完全一致」になり、検索値が検索範囲内の文字列に完全一致した場合のみ値を返します。

引数６：検索モード（省略可）

検索モードを指定。デフォルトでは、先頭から末尾へ検索されます。

XLOOKUP関数の基本的な使い方

XLOOKUP関数の一番基本的な使い方について解説します。下記では商品リストの中から指定IDに対しての商品名をXLOOKUP関数で検索して表示する方法について解説します。

１：「検索値」を指定

1つ目の設定値（引数）として「検索値」を指定します。ここで検索したいのはB3セルに設定したIDなので、セル参照としてB3を指定します。

２：「検索範囲」を指定

2つ目の設定値（引数）として「検索範囲」を指定します。ここでの検索範囲はIDの列となりますので、IDの列をドラッグして、B6:B15と指定します。

３：「戻り範囲」を指定

次に戻り範囲としてどの範囲の値を返すのかの設定をします。ここではIDに対して商品名を返したいので商品名の列となります。商品名の列をドラッグして、C6:C15と指定します。ここで気をつけたいのが「検索範囲」で指定した範囲の高さと必ず同じにしなければいけないことです。

IDに対して適切な商品名が返されました。

発展編：XLOOKUPで複数条件を指定して検索する方法

XLOOKUP関数では複数条件で検索も簡単にできるようになっています。VLOOKUP関数ではわざわざ列を増やして検索値をマージさせるなどの対応が必要でしたが、複数の検索値や範囲を「＆」でつなげるだけで対応できます。

おすすめの解説動画

XLOOKUP関数で何ができるのかを事例を用いて詳しく説明している動画となります。分かりやすく解説しているのでおすすめです。

ショートカットキーの練習できます

当サイトはショートカットキーの練習を実際の動きを確認しながら練習できる機能がついています。繰り返し指を動かして練習ができるので、ゲーム感覚で遊んでいくうちに自然とショートカットキーが使えるようになれます。ショートカットキーは暗記するよりも実際に手を動かして練習していったほうが習得が早くなるので、是非当サイトをブックマークに登録し定期的に練習してみてください。下記のSTARTキーを押下すると練習を始められます。