エクセル XLOOKUP 関数：検索する値に対応した値を指定した範囲の行または列から取得する

はじめに

エクセルの XLOOKUP 関数の使い方を紹介します。

XLOOKUP 関数は検索する値に対応したセルを指定した範囲の行または列から取得します。

「VLOOKUP 関数」と「HLOOKUP 関数」の両方の機能を持ち、見出しで検索もできる上位関数です。複数条件を指定する方法も紹介します。

XLOOKUP 関数

XLOOKUP(検索値, 検索範囲, 結果範囲)
検索値を検索範囲のデータと比較し、一致した行または列に対応するセルを結果範囲から取得します。

XLOOKUP(検索値, 検索範囲, 結果範囲, 見つからない場合, 一致モード, 検索モード)
検索値が見つからないときは見つからない場合の値を返します。一致モードで検索値と近いデータを取得するように指定できます。検索モードで検索範囲を逆順に検索するように指定できます。

引数「検索値」	検索する値を、数値、文字列、セル参照、関数などで指定します。 引数「一致モード」が 2 のときだけ、ワイルドカードを使用できます。
引数「検索範囲」	検索対象となるデータの範囲を 1 行または 1 列で指定します。
引数「結果範囲」	取得するセルの範囲を指定します。複数の行または列を指定すると結果もそのセルの範囲になります。
引数「見つからない場合」	省略できます。 引数「検索範囲」の中から見つからないときに返す値を、数値、文字列、セル参照、関数などで指定します。
引数「一致モード」	省略できます。 0 または省略：検索する値と同じ値と一致します。 1：検索する値と同じまたは、それ以上の最小値と一致します。 -1：検索する値と同じまたは、それ以下の最大値と一致します。 2：検索する値と同じ値と一致します。ワイルドカードを使用できます。
引数「検索モード」	省略できます。 1 または省略：引数「検索範囲」が 1 列なら上から下へ、1 行なら左から右へ検索します。 -1：引数「検索範囲」が 1 列なら下から上へ、1 行なら右から左へ検索します。

使い方

XLOOKUP 関数は「VLOOKUP 関数」としても「HLOOKUP 関数」としても使用できます。また「VLOOKUP 関数より高度に検索する」のように行と列の見出しで検索して交差するセルも取得できます。

「VLOOKUP 関数」のように検索するために引数に何を指定すればいいのか解説します。「HLOOKUP 関数」のように検索するには「行」と「列」を入れ替えて読んでください。

第 1 引数 (検索値)

次のようなデータがあります。この中から何を検索したいですか？

「No.」を検索して名前を取得したい。「名前」を検索してバージョンを取得したい。その検索する値を第 1 引数に指定します。検索する値がセル「B8」に入っているのなら次のように入力します。

第 2 引数 (検索範囲)

「No.」を検索するなら、そのデータの範囲を第 2 引数に指定します。次のように入力します。

「名前」を検索するなら次のように入力します。

第 3 引数 (結果範囲)

何を取得しますか？

「No.」から名前を取得するなら、その「名前」のデータの範囲を第 3 引数に指定します。次のように入力します。

「バージョン」を取得するなら次のように入力します。

ここまでで結果を取得できるようになります。以降の引数はすべて省略できます。

第 4 引数 (見つからない場合)

検索結果が見つからないときに何を表示しますか？

検索結果が見つからないときはエラー #N/A が表示されます。その代わりに表示したい値を第 4 引数に指定します。空にしたいときは次のように入力します。

見つかりません と表示するには次のように入力します。

第 5 引数 (一致モード)

数値を検索するときに近い値と一致するようにしますか？

省略または 0 を指定すると検索する値と同じ値が一致します。

1 を指定すると検索する値と同じ値が見つからないときは、それ以上の最小値と一致します。

-1 を指定すると検索する値と同じ値が見つからないときは、それ以下の最大値と一致します。

2 を指定するとワイルドカードを使用した値と一致します。

通常は一致するデータを取得したいので省略します。

第 6 引数 (検索モード)

逆順に検索しますか。

省略または 1 を指定すると検索範囲の上または左から検索します。

-1 を指定すると検索範囲の下または右から検索します。

一致する値が複数あるときに使用します。通常は上または左から検索したいので省略します。

スポンサーリンク

使用例

「No.」を検索して「名前」を取得します。

「バージョン」を検索して「No.」を取得します。

HLOOKUP 関数のように検索する

「HLOOKUP 関数」のように検索します。

セルの範囲を取得する

引数「結果範囲」に複数列を指定してセルの範囲を取得します。

結果がセルの範囲になるので「SUM 関数」などの引数に指定できます。

結果も複数列で表示されます。これはスピルという機能によるものです。

別シートを検索する

別シートを参照するには シート名!セル名 を入力します。セル名の前に シート名! を付けると、そのシートのセルを参照できます。

シート「Sheet2」に検索と取得したいデータの範囲があります。

シート「Sheet2」のセルを参照するには Sheet2!B3:B5 や Sheet2!C3:C5 を入力します。

シート「Sheet2」から「No.」を検索して「名前」を取得します。

複数条件で検索する

次のようなデータがあります。「名前」から「個数」を取得するとき、一致するデータが複数あるため一つに絞り込めません。

一つに絞り込むために検索する値を複数にし、「名前」と「バージョン」を結合した値で検索します。

引数「検索値」に名前とバージョンを結合した値を指定します。引数「検索範囲」に名前とバージョンの列を結合した範囲を指定して複数条件に対応できます。

セルの範囲を結合できるのはスピルという機能によるものです。

見出しで検索する

行見出しと列見出しを検索条件にして交差するセルを取得する方法を紹介します。

二つの XLOOKUP 関数を組み合わせて取得します。

2 つ目の XLOOKUP 関数で列見出しの検索をします。引数「結果範囲」にデータ全体を指定します。

取得されるセルの範囲が 1 列になります。

これを 1 つ目の XLOOKUP 関数の引数「結果範囲」に指定して行見出しを検索します。

これで行見出しと列見出しの交差するセルの値を取得できます。

解説

引数「検索値」の大文字と小文字は区別しません。 "ABC" と "abc" は同じものとして一致します。

引数「検索範囲」に 1 列だけ指定します。

引数「検索範囲」のデータは並べ替えられている必要はありません。

引数「検索範囲」に複数列を指定するとエラー #VALUE! になります。

引数「結果範囲」は引数「検索範囲」の行数に合わせる必要があります。行数が合っているなら 1 列でも複数列の範囲でも指定できます。

引数「結果範囲」に 1 列を指定すると、対応したセルの値を取得します。複数列を指定すると、対応したセルの範囲を取得します。

引数「結果範囲」と引数「検索範囲」の行数が合っていないときはエラー #VALUE! になります。

引数「見つからない場合」を省略して検索結果が見つからないときはエラー #N/A になります。

引数「一致モード」に 1 を指定したときに 10, 20, 30 の中から 15 を検索すると、15 以上の最小値である 20 と一致します。

引数「一致モード」に -1 を指定したときに 10, 20, 30 の中から 15 を検索すると、15 以下の最大値である 10 と一致します。

引数「一致モード」に 2 を指定したとき、引数「検索値」にワイルドカードを使用できます。使用できるワイルドカードには次のものがあります。

パターン	説明	使用例	一致例
*	任意の長さの文字	"あ*"	あ, あい, あいう
?	任意の 1 文字	"あ?"	あい, あか, あき
~	ワイルドカードの文字 * ? を検索する	"あ~?~*"	あ?*

スポンサーリンク