行全体または列全体を返す

これまで、XLOOKUPは1つの値を返していました。しかし、データの行全体または列全体を一度に返すこともできます。

数式が複数の値を返すと、それらの値は隣接するセルに自動的にスピルします。これにより、1つのXLOOKUPで小さなレコード全体を埋められます。

ポイントは、戻り配列を複数の列にまたがる範囲にすることです。B2:B20だけを返す代わりに、B2:E20を返します。

XLOOKUPは一致する行を見つけ、その行にある戻り配列のすべての列を返します。1つの数式で4つの結果が得られます。

たとえばF2という1つのセルに数式を入力し、Enterキーを押します。値はF2、G2、H2、I2にわたって表示されます。

スピル範囲は薄い青色の枠で囲まれます。編集するのは左上のセルだけで、残りのセルはスピルによって埋められるため、直接変更できません。

従業員テーブルで、A列にID、B列からE列に名前、部署、役職、給与が入っているとします。

D2にIDを入力すると、1つのXLOOKUPでレコード全体が返されます。IDを変更すれば行全体がすぐに更新され、1つの数式だけで小さな検索ツールになります。

同じ考え方は縦方向にも使えます。見出しの行を検索すれば、結果の列全体を返せます。

ここでは、XLOOKUPが見出し行B1:E1からD2のラベルを検索し、一致する見出しの下にあるB2:E50の列全体へスピルします。

数式がスピルしたら、セルの後ろにハッシュ記号を付けることで、スピル範囲全体を参照できます。たとえばF2#のように指定します。

これは強力な機能です。スピルした行の幅が変わっても、スピルした行を合計するSUMは正しく動作します。F2#は常に「F2からスピルした範囲全体」を意味するためです。

結果は配列なので、範囲を受け取れる関数に直接渡せます。

たとえば、検索結果をSUMで囲めば、補助セルを使わずに、1つの数式だけで返された月別の数値の行を合計できます。

スピルする数式には、値を埋めるための空のセルが必要です。スピルする範囲にすでにデータが入ったセルが1つでもあると、XLOOKUPは#SPILL!エラーを返します。

対処方法は簡単です。邪魔になっているセルをクリアするか、空いている場所へ数式を移動してください。スピル範囲は完全に空でなければなりません。

見栄えのよい検索カードを作るには、フィールドの見出しもスピルさせられます。

データ行を返すXLOOKUPを1つ配置し、その上で見出し範囲を参照します。スピル範囲の幅が広がったり狭くなったりしても、ラベルは返された列と正しく対応します。

1つのXLOOKUPを別のXLOOKUPの中に入れ子にすることもできます。内側のXLOOKUPで列全体を返し、外側のXLOOKUPでその中から1つのセルを選びます。

これにより、行と列の両方を一致させる、本格的な2方向検索をXLOOKUPだけで実現できます。INDEX-MATCH-MATCHに代わる便利な方法です。

XLOOKUPで複数の値を返せることが分かりました。

• 複数列の戻り配列で行全体をスピルできます
• 複数行の戻り配列で列全体をスピルできます
• #サフィックスを使ってスピル範囲を参照できます
• 邪魔になるセルをクリアして#SPILL!を防ぎます

これにより、1つの数式が完全なレコードビューアになります。

XLOOKUPの結果をスピルさせる方法についての理解度を確認しましょう。

XLOOKUPのコースを終え、結果をスピルさせる方法を学びました。

• 戻り配列を広げて、行全体または列全体をスピルさせます
• スピルした値は空いている隣接セルに入り、青い枠で示されます
• #サフィックスを使って、F2#のようにスピル範囲を参照します
• スピル範囲を空けておき、#SPILL!を防ぎます

構文、フォールバック、方向、スピルをマスターすれば、XLOOKUPで従来の検索機能のほぼすべてを置き換えられます。