XLOOKUP関数で複数列のスピル配列を作成する方法

XLOOKUP関数は、Excelの強力な検索ツールの一つであり、特定の条件に基づいてデータを検索し、関連する情報を返します。本記事では、XLOOKUP関数を使用して複数列から成る戻り値をスピル配列として表示する基本的な手順を解説します。

VLOOKUP関数やHLOOKUP関数では1つの式で1セル分の戻り値しか生成できません。しかしながらXLOOKUP関数では複数セルの戻り値(スピル配列)を生成することができるのです。

セルH5へ作成した式の戻り値はI5とJ5まで及んでいる

XLOOKUP関数の基本と構文

XLOOKUP関数は、指定された検索値を検索範囲内で見つけ、対応する戻り範囲から値を返す関数です。複数列の戻り値を取得する場合、戻り範囲に複数の列を指定することができます。

以下はXLOOKUP関数の基本構文(必須部分)です。引数はさらに設定することができるのですが、本稿では必須部分のみを解説します。

=XLOOKUP(検索値<1つ>, 検索範囲<1列>, 戻り範囲<1列～複数列>)

複数列から成る戻り値をスピル配列として表示するには、以下の手順に従います。

検索値とは、検索範囲内で見つけたい値です。これは、特定の数値やテキスト、またはセル参照を使用して指定できます。

下のサンプル図ではセルH5で計算式の作成を開始しました。検索値にセルG5を指定しています。「1012」のように直接入力することもできます。ここへ指定できる値は1つのみです。

検索範囲は、検索値が存在する可能性がある範囲を指定します。この範囲は、列または行の連続したセル範囲・線形配列である必要があります。

サンプル図では書籍番号全体が入力されているA2:A16を指定しています。

戻り範囲は、検索値に対応する情報が含まれている範囲です。複数列・矩形配列の情報を戻り値として取得したい場合、この範囲に複数の列を指定します。

サンプル図ではC～E列を指定するためにC2:E16を指定しました。サイズは「検索範囲」と同じとします。また「左→右」の指定しかできません。例えばこのサンプルでは「金額、発刊日、書名」のような指定はできません(関数を追加すればできるが標準では不可)。

XLOOKUP関数を実行すると、指定された複数列の情報がスピル配列として自動的に展開されます。

=XLOOKUP(検索値, A列範囲, C列:E列範囲)

このXLOOKUP関数の例では、A列の範囲内で指定された検索値を見つけ、対応するC列からE列までの情報を戻り値として返します。

例えば下の表から書籍番号が「1008」の書名、発刊日、金額を検索させる式は以下の通りです。

=XLOOKUP(1008, A2:A16, C2:E16)

またセルG5に入力されている書籍番号の書名、発刊日、金額を検索させる式は以下の通りです。

=XLOOKUP(G5,A2:A16,C2:E16)

検索表を「テーブル甲」というテーブルとして登録してある場合には以下のような式となります。

=XLOOKUP(G5,テーブル甲[書籍番号],テーブル甲[[書名]:[金額]])