command lab
  • TOP
  • BLOG
  • Excel

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

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

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

関連リンク
Office 2019以前のバージョンでXLOOKUP関数のように柔軟な使い方をしたい場合はINDEX関数とMATCH関数を組み合わせて使用する方法があります。
INDEX関数をMATCH関数を組み合わせて使用する方法

XLOOKUP関数のポイント

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

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

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

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

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

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

ポイント3:戻り値を一気に表示可能

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

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

ポイント4:縦方向と横方向の両方で機能

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

ポイント5:逆検索を実行可能

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

XLOOKUP関数の設定項目

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

=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード]

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

引数1:検索値

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

引数2:検索範囲

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

引数3:戻り範囲

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

引数4:見つからない場合の値(省略可)

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

引数5:一致モード(省略可)

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

設定値説明
0(デフォルト)完全一致のみを検索。一致しない場合は#N/Aを返します
-1完全一致または一致しない場合は次に小さい値を返します
1完全一致または一致しない場合は次に大きい値を返します
2ワイルドカードと一致(*、?、~)

引数6:検索モード(省略可)

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

設定値説明
1(デフォルト)最初の値から検索します
-1最後の値から検索します(逆検索)
2昇順で並び替えされたバイナリ検索
-2降順で並び替えされたバイナリ検索

XLOOKUP関数の基本的な使い方

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

1:「検索値」を指定

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

2:「検索範囲」を指定

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

3:「戻り範囲」を指定

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

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

おすすめの解説動画

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

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

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

下のスタートボタンを押して練習モードに入ります。
STARTSTOP
※入力モード切り替え ctrl + shift + alt + space
PRE
NEXT
ガイドモード
リピートモード

※推奨ブラウザ Google Chrome

PUSH ENTER

関連記事