XLOOKUP関数の使い方 – 縦横無尽に検索し、一致するデータとその関連情報を取得 -DL有

2024-01-26 2024-02-22

えくせるサイトウ

Excel関数のXLOOKUPの構文と使用例について詳しく説明します。

Excel 2019に登場した新しい関数で、
表や範囲からデータを検索して値を返すことができます。

VLOOKUP関数やHLOOKUP関数の後継ともいえる関数です。

関数の説明

解説を読む前に、巻末の参考記事もチェックしておくと良いため、時間があれば覗いてみてください。

XLOOKUP関数とは

Excel標準機能の[関数の挿入]ダイアログ/[関数の引数]ダイアログを大体のことは書かれているので、
活用して詳しく解説します。

ダイアログの使い方は、巻末の参考記事を参照していただければと思います。

範囲または配列で一致の検索を行い、 2 つめの範囲または配列から対応する項目を返します。既定では、完全一致が使用されます
[関数の挿入]ダイアログより

XLOOKUPは、検索（Lookup）という意味の英語にXという文字を付けたものです。

Xは、横（Horizontal）と縦（Vertical）の両方の方向に検索できることを表していると思います。

つまり、

Xは無限を表す記号としても使われるので、

両方の方向に検索できることを示していると考えられます。

つまり、

縦横無尽です。

ちなみに、

横（Horizontal）の方向に検索するから、HLOOKUP、
縦（Vertical）の方向に検索するから、VLOOKUP、

となっているわけです。

VLOOKUP関数やHLOOKUPと比較した際の

XLOOKUP関数の特徴は、以下です。

検索値を表の左端に配置する必要がない
列番号や検索方法を指定する必要がない
複数の列や行を一度に取得できる
見つからない場合のメッセージを設定できる
一致モードや検索モードを細かく指定できる

XLOOKUP関数の構文

XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
※[]の引数は省略可能

検索範囲から、検索値を探し、指定した戻り範囲に指定した値を返します。
また、省略可能な引数として、[見つからない場合][一致モード][検索モード]) の3つがあります。

第1引数：検索値

検索値: 検索値には検索する値を指定します
[関数の挿入]ダイアログより

検索値は、探したいデータを指定します
セル参照や数値、文字列などが使えます。

第2引数：検索範囲

検索範囲: 検索範囲には検索対象の配列または範囲を指定します
[関数の挿入]ダイアログより

検索範囲は、検索値が入っているセルの範囲を指定します。

第3引数：戻り範囲

戻り範囲: 戻り範囲には戻り値の配列または範囲を指定します
[関数の挿入]ダイアログより

戻り範囲は、検索結果として取得したいセルの範囲を指定します。

第4引数：[見つからない場合]

[見つからない場合]: 一致が見つからない場合に返されます
[関数の挿入]ダイアログより

[見つからない場合]は、検索値が見つからなかったときに表示する内容を指定します。

省略して、検索値が見つからない場合は #N/A エラーになります。

例えば、

”検索値は見つかりませんでした”

などを指定します。

第5引数：[一致モード]

[一致モード]: 一致モードには検索範囲内の値に対する検索値の一致方法を指定します

[関数の挿入]ダイアログより

[一致モード]は、検索値と完全に一致するか、近似値も検索するかを指定します
省略すると完全一致になります。以下の値が使えます。

設定値	説明
0、または、省略	完全一致。見つからない場合は「#N/A」と表示されます。(既
-1	完全一致。見つからない場合は次に小さい項目が表示されます。
1	完全一致。見つからない場合は次に大きい項目が表示されます。
2	ワイルドカードとの一致 (*、?、~（チルダ）など)

なお、覚える必要はありません。
XLOOKUP関数の入力していくと、ヒントが表示され、ぞれぞれ解説も記載されます。

第6引数：[検索モード]

検索モード: 検索モードは使用する検索モードを指定します。既定では、先頭から末尾の検索が使用されます
[関数の挿入]ダイアログより

[検索モード]は、検索範囲を

先頭から末尾に向かって検索するか、

逆に、

末尾から先頭に向かって検索するか

を指定します。

省略すると、先頭から末尾になります。

検索モードのオプション	説明
1、または、省略	先頭の項目から検索を実行します。これが既定の設定です。
-1	末尾の項目から逆方向に検索を実行します。
2	昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。並べ替えられていない場合、無効な結果が返されます。
-2	降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。並べ替えられていない場合、無効な結果が返されます。

バイナリ検索とは、ざっくり言うと・・・
「高速に検索できる」機能を指します

なお、こちらも覚える必要はありません。
XLOOKUP関数の入力していくと、ヒントが表示され、ぞれぞれ解説も記載されます。

関数の使用例

例題

XLOOKUP関数を使って、商品番号の2に対応する商品名と単価を表示させます。

解答

次のように、各引数を指定します。

「OK」をクリックすると、下図になります。

具体的な関数式は以下です。

=XLOOKUP($A10, $A$2:$A$6, $C$2:$C$6)

上記の関数式を、列方向へコピーすると、
単価も表示されます。

解答付きサンプルデータ

解答付きサンプルデータが欲しい方は、下記からダウンロードしてください。

5023.XLOOKUP.xlsx ダウンロード

最後に

XLOOKUP関数の基本的な使い方の解説をしました。

そのため、本記事はすごくシンプルですが、
XLOOKUP関数は奥が深いです。

VLOOKUP関数やHLOOKUPとの違い、
XLOOKUP関数の[一致モード][検索モード]とは何か？

など、疑問が尽きることはないでしょう。

そのあたりを今後の記事で取り上げられたらと考えています。