Excel情報504　関数--XLOOKUP関数で検索する方法

【テーマ】
今回はVLOOKUP関数、HLOOKUP関数、INDEX+MATCH関数と同じような検索ができるXLOOKUP関数をご紹介します。

【方法】
XLOOKUP(検索値、検索範囲、戻り範囲)。VLOOKUP関数やHLOOKUP関数では「列番号」「行番号」だったところが、XLOOKUPでは「戻り範囲」になります。

【参考】
検索範囲に検索値が存在しなかった場合、IFERROR関数のように返す値を指定できます。

VLOOKUP関数やHLOOKUP関数は、検索値を検索範囲の一番左端/上端にする必要がありました。

INDEX関数+MATCH関数を使えば、検索範囲を左端/上端にしなくても対応できますが、

数式が複雑になってしまいます。

そこで今回ご紹介するXLOOKUP関数を使えば、簡単な数式で対応でき、

また、一つの数式で複数項目に対応する値を取得することができます。

※Office365のみ対応　2020年3月現在

VLOOKUP関数は、

「検索範囲」から取得したいデータが左から何列目にあるかを指定し、

「検索値」は「検索範囲」の一番左端に置かなければいけませんでした。

XLOOKUP関数は、

取得したいデータをセル範囲で指定するため、列数を意識する必要はありません。

また、検索値は検索範囲のどこに置いても対応ができ、一つの数式で複数データ取得できます。

※データの並びによっては取得したデータの数だけ数式の設定が必要です。

■事例１　設定方法(検索値が検索範囲の右側にある場合)

例として下図の「売上表」を使用します。

型番 P100 の「商品名」を検索します。

検索結果を表示したいセル(例ではC5セル)にXLOOKUP関数を設定します。

■事例２設定方法(複数データを取得する場合)

例として下図の「売上表」を使用します。

型番 PR100 の「商品名」「数量」「売上金額」を検索します。

検索結果を表示したい一番左側のセル(例ではC5セル)にXLOOKUP関数を設定します。

VLOOKUP関数では列番号で指定していたところをセル範囲で指定することで、

複数データを取得することができます。

※ただし下図のように、取得したい項目とデータの並びが同じではない場合、

　事例１同様、データの数だけ数式を設定する必要があります。

■ご参考までに

※

HLOOKUP関数のように水平方向に検索するには

下記のような数式になります。

例ではC5セルに、「型番」　P100 の価格を検索するXLOOKUP関数を設定しています。

「検索範囲」に型番行(C8:E8セル)、「戻り範囲」に価格行(C10:E10セル)を指定しています。

※

「検索範囲」に「検索値」が存在しない場合、

「見つからない場合」を設定することで、

IFERROR関数のように任意の値が表示できます。

例では「見つからない場合」に「"該当無し"」を指定しています。