XLOOKUP と VSTACK 関数を組み合わせる

下図では [商品リスト1] テーブルと [商品リスト2] テーブルを VSTACK 関数の数式を使って 1 つにまとめています。

=VSTACK(商品リスト1 , 商品リスト2)

セル K4 に該当する商品の金額を表示するため、検索値をセル J4 に格納し、VSTACK 関数の処理結果であるセル G4 から H17 の範囲を参照する XLOOKUP 関数の数式を作成しています。

 =XLOOKUP(J4 , G4:G17 , H4:H17)

この場合、[商品リスト 1] や [商品リスト 2] にデータが追加されたとき、XLOOKUP 関数の数式が参照しているセル範囲 (G4:G17 と H4:H17) は自動的には拡張されないので、テーブルに追加したデータ (商品) の結果は該当なしとなってしまいます。

ということは、テーブルにデータが追加されるたびに XLOOKUP 関数の数式も修正しないといけないです。

XLOOKUP の範囲を編集しなくてよいようにする 1

テーブルにデータが追加されたら XLOOKUP で参照する範囲も自動的に拡張されるようにしたいのなら、XLOOKUP の引数で VSTACK による配列の追加をしてしまえばよいです。

この場合は、そもそも VSTACK 関数を使った数式で 2 つのテーブルをまとめた結果がワークシート上になくてもよいですね。 

XLOOKUP の範囲を編集しなくてよいようにする 2

VSTACK でまとめるときに工夫するのも 1 つの手です。 2 つのテーブルの [商品] 列だけをまとめる VSTACK の数式と、[金額] 列だけをまとめる数式をそれぞれで作ります。列ごとに VSTACK 関数の数式を使って結果を表示する、ということです。

スピルの範囲が 2 つに分かれるので XLOOKUP の引数にスピル範囲演算子を使ってそれぞれのスピル範囲を指定します。G4# は VSTACK 関数の数式が入っているセル G4 から始まるスピルの範囲すべて、という意味です。

=XLOOKUP(J4 , G4# , H4#)

検索値を探す列を表の左端に配置することが可能なら VLOOKUP のほうがシンプルですね。

どんなデータで何がしたいのか、今後どんなふうに更新されていき、どのように継続して利用していきたいのか、だれが使うワークシートなのかなどによって数式の作り方は変わりますし、変えるべきでしょう。ここで書いた方法以外にもやり方はあります。アプリケーションとして完成させるわけではないのなら使ってみて少しずつ直せばよいですね。

スピルの結果を何かしたい、というときに少し工夫が必要になるケースが多い気がしたので 1 つの例としてご紹介しました。

石田 かのこ