XLOOKUP関数 ● さまざまな条件を指定してデータを取り出す (3)スピルの利用

［Excel 2021以降］

Excel 2021の新関数である XLOOKUP（エックス・ルックアップ）関数は「スピル」に対応しており、同時に複数のセルで表引きを行えます。ここではその方法を紹介します。

XLOOKUP関数の基本的な使い方は、下記のページを参照してください。

1 そもそもスピルとは
2 XLOOKUP関数とスピル
3 （A）縦方向にスピルさせる
4 （B）横方向にスピルさせる

そもそもスピルとは

Excel 2021以降、および Microsoft 365では、セルに複数の結果を返す数式を入力して［Enter］キーで確定すると、複数の結果が隣接する複数のセルに自動で表示されるようになりました。この機能を「スピル」と呼びます。「こぼれる」「あふれる」という意味の「spill」が語源です。

例えば、セルC1に「=A1:A5」と入力すると、セルC1～C5の範囲にセルA1～A5の値が表示されます。セルC2～C5は、セルC1の数式の結果がスピルによってあふれ出たものです。セルC2～C5のいずれかのセルを選択すると、セルC1に入れた数式が数式バーに薄く表示されます。これらのセルを「ゴースト」と呼びます。

図1　セルC1に「=A1:A5」と入力して［Enter］キーを押す。

図2　セルC1～C5の範囲にセルA1～A5の値が表示される。セルC2以降はゴースト。

ちなみに、スピル機能を使えないバージョンで同様の処理を行うには、セルC1～C5を選択した状態で「=A1:A5」と入力し、［Ctrl］＋［Shift］＋［Enter］キーを押します。こうして入力した数式は「配列数式」と呼ばれます。
一方、図2のように数式の結果に合わせて適切な範囲に自動でスピルされる数式は、「動的配列数式」と呼ばれます。

XLOOKUP関数とスピル

まず、XLOOKUP関数のオーソドックスな使い方を紹介します。図3では、［検索値］にセルA3の「L-101」を指定して、商品リストから商品名を表引きしています。「L-101」は［検索範囲］の 1番目にあるので、［戻り値範囲］の 1番目の値である「洗濯洗剤リキッドL」が返されます。
［見つからない場合］に空の文字列「""」を指定したので、［検索範囲］に存在しない値をセルA3に入力した場合、セルB3は空欄になります。

さまざまな条件を指定してデータを取り出す
=XLOOKUP(検索値, 検索範囲, 戻り値範囲, ［見つからない場合］,［一致モード］, ［検索モード］)

セルB3 | =XLOOKUP(A3,H3:H8,I3:I8,"")

図3　XLOOKUP関数を使用して、商品リストから「L-101」の商品名を表引きした。

XLOOKUP関数では、スピルを利用して縦方向または横方向に一気に表引きを行えます。例えば図4のセルB3に数式を入力した場合、「A」の範囲（縦方向）、または「B」の範囲（横方向）にスピルさせることができます。残念ながら縦横同時にはスピルさせられません。

図4　セルB3に数式を入力して、「A」または「B」の範囲にスピルさせることができる。

縦にスピルさせるには［検索値］、横にスピルさせるには［戻り値範囲］の指定がポイントになります。

（A）縦にスピル……［検索値］に 1列×複数行を指定すると、同じ行数だけ縦にスピルする
（B）横にスピル……［戻り値範囲］に複数行×複数列を指定すると、同じ列数だけ横にスピルする

それでは実際の操作を見ていきましょう。

（A）縦方向にスピルさせる

［検索値］に 1列×複数行を指定すると、同じ行数だけ縦にスピルさせられます。図3では［検索値］にセルA3だけを指定しましたが、4行分のセルA3～A6 を指定すると、図5～図6のように 4行分の範囲に商品名を求めることができます。［検索値］以外の引数は図3と同じです。

=XLOOKUP(検索値, 検索範囲, 戻り値範囲, ［見つからない場合］,［一致モード］, ［検索モード］)

セルB3 | =XLOOKUP(A3:A6,H3:H8,I3:I8,"")

図5
　(1) セルB3を選択。
　(2) XLOOKUP関数を入力。［検索値］に「A3:A6」を指定すること。
　(3) ［Enter］キーを押す。

図6
　(4) セルB3～B6の範囲に、セルA3～A6の商品IDに対応する商品名が表示された。セルB4～B6はゴースト。

メモ

VLOOKUP関数でも同様にスピルできる

スピル機能を利用できるバージョンであれば、VLOOKUP関数でも図5～図6と同様にスピルさせられます。

=VLOOKUP(A3:A6,H3:K8,2,FALSE)

メモ

横にコピーするには絶対参照を使う

図5の数式の［検索値］と［検索範囲］を絶対参照に変えて、セルB3をセルD3までコピーすると、4行分の分類と単価を表引きできます。

=XLOOKUP($A$3:$A$6,$H$3:$H$8,I3:I8,"")

（B）横方向にスピルさせる

［戻り値範囲］に複数行×複数列を指定すると、同じ列数だけ横にスピルさせられます。図3では［戻り値範囲］に「I3:I8」という 1列のセル範囲を指定しましたが、3列分のセルI3～K8 を指定すると、図7～図8のように 3列分の範囲に商品名、分類、単価を一気に求めることができます。［戻り値範囲］以外の引数は図3と同じです。