【最新版】XLOOKUP関数の使い方

2019年10月27日

 

こんにちは。会計士KOです。

今回はエクセル関数のXLOOKUP関数について解説をしていこうと思います。

日々、VLOOKUP関数や、HLOOKUP関数を利用している方に強力な味方となるであろう関数ができました。

それが、

XLOOKUP関数

です。

 
VLOOKUP関数よりも直感的な操作が可能で、そして高機能。
 
特に、
 

複数の戻り値を返すこと

検索結果が見つからなかった場合の処理

 
XLOOKUP関数1つで対応が可能です。

では、以下から内容について確認をしていきましょう。

 

XLOOKUP関数とは

XLOOKUP関数とは、2019年、Microsoft office EXCELに実装された新たな高機能検索用関数です
VLOOKUP関数では、検索行の右以降しか戻り値を返せないことや、検索に失敗したときの挙動などを指定できないなどの弱点がありました。
そのVLOOKUP関数の弱点を補った待望の関数と言えます。

以前のVLOOKUP関数よりも超高機能になっており、しかも直感的に操作できます。

 

XLOOKUP関数の使い方

まず、関数と引数の説明です。
XLOOKUP関数の式と、引数の関係については、以下のスライドの通りとなります。
 

基本的には、まず、第1引数に検索値の指定、第2引数に検索範囲の指定、第3引数に戻り値部分の範囲指定を行った上で、第4引数以降で検索方法等の指定を行っていきます。

第3引数の戻り値には、複数セルを選択できるほか、VLOOKUP関数では利用ができなかった検索値の左側のセル範囲からも戻り値を得ることが可能です。
以下にて、使い方を説明していきます。
 
 
 

垂直方向への検索(VLOOKUP関数と同様の使い方)

垂直方向への検索を行う方法です。従来のVLOOKUP関数と同様の使い方です。

第1引数には、検索値をセルで指定し、第2引数には、検索範囲を指定後、戻り値として返す部分を第3引数として指定しています。

今回の例では、一致モード(第4引数)については、「0」を指定し、完全一致を指定し、検索モード(第5引数)については、「1」を指定し上から順に検索を行っています。

結果としては以下の通りとなります。

 
 

水平方向への検索(HLOOKUP関数と同様の使い方)

水平方向への検索を行う方法です。従来のHLOOKUP関数と同様の使い方です。

第1引数には、検索値をセルで指定し、第2引数には、検索範囲を指定後、戻り値として返す部分を第3引数として指定しています。

今回の例では、一致モード(第4引数)については、今回の例では、「0」を指定し、完全一致を指定し、検索モード(第5引数)については、「1」を指定し左から順に右へと検索を行っています。

実際には、上記の垂直方向への検索を水平方向に置きなおしただけです。全く操作としては変わらないのが良くわかりますね。

結果としては以下の通りとなります。

 

戻り値の範囲を自由に取得する使い方(INDEX関数+MATCH関数の代替)

VLOOKUP関数では利用ができなかった検索値の左側のセル範囲からも戻り値を得ることが可能です。

従来の関数においてはINDEX関数とMATCH関数を利用して、検索を行う必要がありました。

しかしながら、XLOOKUP関数では、以下のように指定するだけで、検索範囲の左のセル範囲でも任意の検索結果を得ることができます

操作方法は一貫しており、戻り範囲を検索範囲の左側のセル範囲に指定してあげるだけです。

今回の例では、上記の例の列を入れ替えています。

結果としては、ちゃんと以下のように任意のデータが得られました。

 

VLOOKUP関数と同様、かつ、検索値が見つからない場合の使い方(VLOOKUP関数+IFERROR関数の代替)

 
検索値が見つからなかった場合の処理については、従来のVLOOKUP関数ではIFERROR関数と組み合わせて、関数を組む必要がありました。
しかしながら、XLOOKUP関数では関数の引数に、検索値が見つからない場合の処理は既に組み込まれているので、1つの関数のみで指定することが可能です。
 

第6引数に、見つからなかった場合に返す値を指定します。

今回は、第5引数に”見つかりません”という文字列を追加しました。

 

結果としては、以下の通りです。

検索文字列に「中国」というテーブルにない国名を指定した結果、見つかりません。という表示が出ているかと思います。

 

 
 

複数の戻り値を返す使い方

XLOOKUP関数では、複数の戻り値を得ることができます。

これがXLOOKUP関数の一番のメリットかと思います。

以下の通りに、戻り値の範囲を複数に指定すれば、1度の検索で複数の値を得ることが可能です。

 

結果は以下の通りとなります。

人口と面積という2つの戻り値を1つの関数で得ることが出来ました。

 

その他

一致モード:検索の精度(完全一致、部分一致、ワイルドカード)を変更する

XLOOKUP関数では、完全一致、部分一致、ワイルドカード指定を第4引数にて指定可能です。
基本的には、以上までの例示の通りに完全一致を指定しておけば問題ありませんが、

例えば、ある特定の文字を含んだ場合に検索結果が欲しい場合は、この引数でワイルドカード指定を行えばよいでしょう。

 

例えば、検索文字列に「国」という文言が入っているデータを取り出したいときは、ワイルドカード指定にて「*国*」と指定してあげます。

また、一致モードには「2」を指定し、ワイルドーカードで検索を行うこととしています。

以下の通りに、アラブ首長国連邦の人口データが検索されたことが分かりますね。

 

 
 

検索モード:検索順の指定

XLOOKUP関数の検索順は第5引数にて指定可能です。

場合によっては下から検索したい場合(右から検索したい場合)もあると思いますが、その場合は第4引数にて検索順を指定するだけです。

例えば、以下のように日本という文字列が表に2回現れるようなテーブルがあったとしましょう。そして、今回は、下の日本行での検索結果が欲しいとします。

このような場合は、第4引数に-1を指定してあげるだけです。

 

結果としては、以下の通りですね。上の日本行の情報ではなく、下にあるオレンジ色の日本行の情報が正しく取得できているかと思います。

 

 

第5引数における検索順の指定は、以下の表の通りです。

基本的には先頭から末尾への検索を行う「1」を指定することが多いでしょう。

また、二分探索アルゴリズムを利用したバイナリ検索機能も備わっていますが、普段使いでバイナリ検索を使うことはほぼないと思いますので、説明は割愛します。

引数の指定 検索方法
1 先頭から末尾(上から下への検索)
-1 末尾から先頭(下から上への検索)
2 バイナリ検索(二分探索)が出来ます。計算量の多いデータを想定していますが、普段利用には不要でしょう。
-2 二分探索が出来ます。計算量の多いデータを想定していますが、普段利用には不要でしょう。
 
 

まとめ

XLOOKUP関数の基本的な使い方は以上です。
また、ワイルドカードとは何かが分からない人は以下に記事を用意しているので、そこから学んでみてください。