高度な検索ができるExcelのXLOOKUP関数の使い方

エクセル作業を便利にしてくれる様々な関数。シートの表から何かを検索して処理をしたい時、検索処理ができるVLOOKUP関数やHLOOKUP関数を利用してきたかと思います。

2020年1月頃にリリースされたバージョンから、新たにXLOOKUP関数が追加されました。
XLOOKUP関数を利用することで、自由度が高く高度な検索が可能となりました。

今までのVLOOKUPやHLOOKUPとどう違うのか、簡単なおさらいを含めてご紹介します。

VLOOKUP

VLOOKUP(検索値, 範囲, 列番号, 検索の型)

検索値に対して、選択範囲から4列目と一致する値を返します。

値を指定の列から取得します。

HLOOKUP

HLOOKUP(検索値, 範囲, 行番号, 検索の型)

検索値に対して、選択範囲から3行目と一致する値を返します。

値を指定の行から取得します。

このように、VLOOKUP関数は垂直（Vertical）方向に検索でき、HLOOKUP関数は水平（Horizontal）方向に検索できる関数です。指定した列から検索するか行から検索するかで、状況に応じて関数を使い分けたりします。

ここで、今回ご紹介するXLOOKUP関数。
VLOOKUPやHLOOKUPでやっていた列だけ、または行だけといった検索処理、これを両方対応できるのがXLOOKUP関数になります。

XLOOKUP関数の利用

XLOOKUP関数では、垂直と水平の両方向に検索することができます。

VLOOKUPやHLOOKUPでは、4つ目の引数がデフォルトで「TRUE」となっていて近似値の検索となっているので、完全一致の値を検索する場合では、4つ目の引数を「FALSE」にする必要がある。それに比べてXLOOKUPでは引数を3つ指定するだけで多くの検索処理に対応できます。

以下、XLOOKUP関数の構文

XLOOKUP(検索値, 検索範囲, 戻り値の配列)

検索値：検索する値
検索範囲：検索対象の範囲
戻り値の配列：返される値の配列

戻り値が複数の場合は、配列として返ってきます。

他、見つからない場合や一致モード、検索モードなども引数をカンマ区切りで設定することができます。
XLOOKUP(検索値, 検索範囲, 戻り値の配列, [見つからない場合], [一致モード], [検索モード])

XLOOKUP関数での検索を、ちょっとしたサンプルでご紹介します。

表から検索したい値の結果を得たい時、検索結果のセルにXLOOKUP関数を設定します。

1つ目の引数に検索値を指定、2つ目に検索範囲、3つ目に戻り値となる範囲を指定します。

検索値に対して検索範囲から検索し、それに完全一致した値を返します。

検索値を削除すると検索結果は「N/A（該当なし）」となり、また対象の値を検索する場合に検索値を設定するなどして、その都度知りたい値を検索するように使ってもらうのもいいですね。

垂直と水平の両方向の検索が可能ですので、検索範囲次第では検索値に対して複数の値を配列として返せます。
以下サンプルは、値が配列として返す例です。

自由度が高く、臨機応変に対応できるので利用しやすい感じです。

どのような表からどのような値を検索したいのか、XLOOKUP関数を使い慣れていくと制限のあるVLOOKUPやHLOOKUPは使わなくて済みますね。