【Excel】XLOOKUP関数の使い方-関数と全ての引数の実例の紹介

2019年10月25日2019年12月7日

 

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

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

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

それが、

XLOOKUP関数

です。

複数の戻り値を返すこと

や

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

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

XLOOKUP関数とは

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

XLOOKUP関数の使い方

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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

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

 

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

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

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

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

 

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

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

その他

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

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

 

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

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

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

 

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

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

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

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

 

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

 

 

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

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

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

まとめ