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

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

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

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

関数の説明

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

XLOOKUP関数とは

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

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

XLOOKUP関数の機能に関して

範囲または配列で一致の検索を行い、 2 つめの範囲または配列から対応する項目を返します。既定では、完全一致が使用されます

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


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

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

つまり、

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

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

つまり、

縦横無尽です。

ちなみに、

Horizontal)の方向に検索するから、HLOOKUP、
Vertical)の方向に検索するから、VLOOKUP、

となっているわけです。

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

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

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

XLOOKUP関数の構文

XLOOKUP関数の引数に関して

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

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

第1引数:検索値
XLOOKUPの第1引数「検索値」

検索値: 検索値には検索する値を指定します

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

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

第2引数:検索範囲
XLOOKUPの第2引数「検索範囲」

検索範囲: 検索範囲には検索対象の配列または範囲を指定します

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

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

第3引数:戻り範囲
XLOOKUPの第3引数「戻り範囲」

戻り範囲: 戻り範囲には戻り値の配列または範囲を指定します

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


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

第4引数:[見つからない場合]
XLOOKUPの第4引数「見つからない場合」

[見つからない場合]: 一致が見つからない場合に返されます

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

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

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

例えば、

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

などを指定します。

第5引数:[一致モード]
XLOOKUPの第5引数「一致モード」

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

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

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

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

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

XLOOKUPの第5引数「一致モード」

第6引数:[検索モード]
XLOOKUPの第6引数「検索モード」

検索モード: 検索モ ー ドは使用する検索モ ー ドを指定します。既定では、先頭から末尾の検索が使用されます

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


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

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

逆に、

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

を指定します。

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

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

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

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

XLOOKUPの第6引数「検索モード」

関数の使用例

例題

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

解答

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

XLOOKUP関数の引数入力結果

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

XLOOKUP関数の引数入力結果

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

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


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

XLOOKUP関数の解説

解答付きサンプルデータ

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

最後に

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

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

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

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

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

—————————————
参考記事
—————————————

原理原則に関する記事

Excel関数自体の原理原則に関する記事です。こちらを参照すると、個別の関数の理解がさらに深まるでしょう。

構文に関する記事

Excel関数の引数と戻り値は何だっけ?という時に、役立つ記事です。

数式の原則|Excel関数にはそれぞれ構文がある

Excel関数には構文があります。それが、引数と戻り値です。構文に沿えばいいだけです。言わば、数学の公式のようなものです。理論を知らなくても、公式に当てはめれば答え…

数式の原則|Excel関数の引数とは

Excelの引数は、料理を完成させるための材料のようなものです。しかも、材料に種類があります。その種類を理解すると、Excel関数を上手く使いこなせるようになります。

ダイアログボックスの使い方に関する記事

Excel関数名は何だっけ?VLOOKUP関数の使い方は何だっけ?という時に、ダイアログボックスを使うと便利です。
下記はそのダイアログボックスに関する記事です。

数式の原則|Excelの検索ダイアログから、関数を探す

Excelの検索ダイアログに使えば、目的の関数を発見しやすくなります。WEB検索する前に、Excel関数の検索ダイアログを使ってみましょう。

数式の原則|Excelのダイアログに沿って、関数を使う

Excelのダイアログに使えば、関数の使い方を調べることができます。言わば、関数のガイドラインのようなものです。

参照形式に関する記事

関数式をコピーする時はどうしたらいい?
$マークは、どう付けたらいいだろう?
といった悩みをお持ちの方は以下をご覧ください。

数式の原則|絶対参照/相対参照/複合参照とは

絶対参照/相対参照/複合参照とは何かをどこよりも分かりやすく解説します。

数式の原則|絶対参照/相対参照/複合参照の使い方

「絶対参照」「相対参照」「複合参照」は、どうやって使うのかを紹介します。 ポイントは、「$マークは、直後にあるモノを固定する」です。