いきなり答える備忘録

Google Workspace(旧G Suite)・Microsoft 365・LibreOfficeなどに関するメモ

(Gスプレッドシート)XLOOKUP関数で縦横同時にスピルさせる

 GoogleスプレッドシートのXLOOKUP関数で、結果の範囲(第3引数)を複数列(場合によっては行)にし、さらに検索キーが複数ある場合に期待されるような結果が得られません。いわゆる縦横にスピル(Excel用語ですが)しない問題です。
 ここではこれを何とかしてみます。

  • XLOOKUP関数にMAP/LAMBDA関数を併用することで、複数行複数列の戻り値を得ることができます。

手順

 まずは問題の再現です。
 XLOOKUP関数を使い、F3:F4に入力されている2つの文字列をキーとして、B~D列の表から国語と算数の点数を取得しようとしています。
 しかしF3セルに対する2つの点数は得られているものの、F4セルに対する点数が得られていません。


 ではARRAYFORMULAで囲めばどうなるかですが、今度は2つの国語の点(結果の範囲の中で最も左の列の値)しか得られません。


 そこで解決法として、MAP/LAMBDA関数を併用します。

 G3セル

=MAP(F3:F4,LAMBDA(a,XLOOKUP(a,B3:B8,C3:D8)))

 検索キーであるF3:F4にaと名付け、a内の各セルごとにXLOOKUP関数を実行した結果を得ています。
 なお、MAPはBYROWに代えても同じ結果になります。