command lab
  • TOP
  • BLOG
  • Excel

  • 【エクセル】XLOOKUP関数の使い方を分かりやすく解説

【エクセル】XLOOKUP関数の使い方を分かりやすく解説

2020年のVLOOKUPやHLOOKUPの後継となる関数「XLOOKUP」が発表されました。VLOOKUP関数の弱点を補い、より使いやすくなっています。ここではXLOOKUPの使い方について解説します。

XLOOKUP関数とは

XLOOKUP関数とはVLOOKUPやHLOOKUPの後継関数です。検索したいワードを指定した範囲内・配列内から検索し、対応した値を返します。

XLOOKUP関数の構文

=XLOOKUP(検索値,検索範囲,戻り範囲,見つからない場合,一致モード,検索モード
  • 検索値:検索する値を指定
  • 検索範囲:検索する対象の範囲か配列を指定
  • 戻り範囲:結果として返す戻り値の範囲か配列を指定
  • 見つからない場合:省略可能です。検索値が見つからない場合に返す値を指定します。
  • 一致モード:省略可能です。検索する方法を指定します。デフォルトは「完全一致」になり、検索値が検索範囲内の文字列に完全一致した場合のみ値を返します。
  • 検索モード:省略可能です。検索モードを指定。デフォルトでは、先頭から末尾へ検索されます。

一致モード設定値

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

検索モード設定値

設定値説明
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のポイント

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

検索範囲が左端になくても良い

VLOOKUPでは検索値は必ず左端である必要がありましたが、XLOOKUPでは左端である必要はなくなりました。

一致モードのデフォルトが完全一致

一致モードは完全一致にしないと正しく結果を返せないことが多いので、完全一致に指定することが多いのですが、VLOOKUPでは一致モードのデフォルトが近似値含めての検索だったので、完全一致にするには「false」を指定しなければいけませんでしたが。XLOOKUPではデフォルトが完全一致なので、指定しなくても正しく結果を返すことができます。

戻り値を一気に表示可能

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

XLOOKUPは、垂直配列と水平配列の両方で機能します。

下記のように設定すれば水平方向に検索することも可能です。

XLOOKUPは逆検索を実行できます

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

おすすめの解説動画

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

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

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

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

※推奨ブラウザ Google Chrome

PUSH ENTER

関連記事