【Excel】XLOOKUPの検索値を範囲にしてスピルさせる|空白はトリム参照、複数列は1列しか出ない

XLOOKUPの検索値を範囲にして結果をスピルさせる Excel・業務効率化

XLOOKUPは、検索値にE2のような1つのセルではなく、E2:E7のような範囲を指定できます。範囲にすると、結果が下のセルに自動で広がり(スピル)、1つの式で全部の行を検索できます。

ただし、この書き方には注意点が2つあります。検索値が空の行にも値が表示されることと、戻り値を複数列にしても1列しか出ないことです。

この記事では、検索値を範囲にする書き方と、この2つへの対処を紹介します。なお、この記事の表は説明のために作ったもので、例の数字も架空です。

例として使う表

商品の一覧が、このように入っているとします。

商品コード商品名単価
1001商品A300
1002商品B500
1003商品C250
1004商品D800
1005商品E150

見出しは1行目で、商品コードはA列、商品名はB列、単価はC列に入っています。

E列に入れた商品コードの商品名を、F列に出します。商品コードは、E2:E5に1003、1001、1005、1002と入っていて、E6から下はまだ空です。

検索値を範囲にする

F2に次の式を入れます。

=XLOOKUP(E2:E7,A:A,B:B)

式はF2にしか入れていませんが、結果はF2:F7にスピルされます。

商品コード商品名
1003商品C
1001商品A
1005商品E
1002商品B
0
0

商品コードはE2:E7、商品名はF2:F7に入っています。

式を1つずつ下にコピーしなくていいので、コピーのし忘れで式が足りない行ができません。

ただし、スピルする先のセル(F3:F7)に何か入っていると、F2は #SPILL! のエラーになります。スピルする先は空けておきます。

検索値が空の行を空白にする

先ほどの結果では、商品コードが空のE6とE7の行に、0が表示されています。空のまま検索されるためです。この0を消す方法は2つあります。

IFで空白にする

F2の式を、次のようにIFで包みます。

=IF(E2:E7="","",XLOOKUP(E2:E7,A:A,B:B))

IFは、E2:E7を上から1行ずつ調べて、商品コードが空の行は空白、入っている行はXLOOKUPの結果を出します。

トリム参照で、データがある行までにする

実務では、「トリム参照」を使っています。範囲のコロンの後ろにピリオドを付けて E2:.E1000 のように書くと、範囲の下のほうにある空の行を自動で切り捨てます。

=XLOOKUP(E2:.E1000,A:A,B:B)

この式なら、商品コードが入っているE5までが検索値になり、結果はF2:F5にだけスピルされます。E6に商品コードを足すと、結果も自動でF6まで広がります。

書き方切り捨てる空の行
E2:.E1000下のほうの空の行
E2.:E1000上のほうの空の行
E2.:.E1000上と下の空の行

範囲を広めに指定しておけば、データが増えても式を直す必要がありません。ただし、切り捨てるのは上や下の空の行だけです。途中に空の行があると、その行は空のまま検索されます。

トリム参照は、新しいMicrosoft 365で使える書き方です。古いExcelでは使えません。

戻り値を複数列にしても1列しか出ない

商品名と単価を一度に出そうとして、戻り値をB:Cにしてみます。

=XLOOKUP(E2:.E1000,A:A,B:C)

この式では、商品名(B列)だけが縦にスピルされ、単価(C列)は出ません。エラーにもならないので、気づきにくいところです。

検索値が1つなら、XLOOKUPは見つかった行の「商品名と単価」を横に並べて返せます。検索値を範囲にすると、検索値を1つずつ調べて、結果を縦に並べようとします。このとき、1つの結果が「商品名と単価の2つ」だと、縦に並んだ各行の中にさらに2列が入る形(表の中に表)になります。Excelはこの形を作れないので、各行の結果を先頭の商品名だけにして返します。

先ほどのIFで包む方法でも1列のままです。IFは行ごとに「空白」か「XLOOKUPの結果」のどちらか1つを選ぶだけなので、C列は出ません。

複数列を出したいときの対処

1行ずつの式にする

検索値を1つのセルにすれば、商品名と単価が横にスピルされます。F2に次の式を入れて、下にコピーします。

=XLOOKUP(E2,A:A,B:C,{"該当なし",0})

4つ目の引数は、見つからなかったときに表示する値です。戻り値が2列なので、{"該当なし",0} のように{}で囲むと、商品名の列には「該当なし」、単価の列には0と、列ごとに値を指定できます。

商品コード商品名単価
1003商品C250
1001商品A300
9999該当なし0

商品コードはE2:E4、商品名はF2:F4、単価はG2:G4に入っています。9999は商品の一覧にないので、「該当なし」と0になります。

列ごとに式を分ける

範囲でスピルさせたい場合は、列ごとに式を分けます。F2に商品名、G2に単価の式を入れます。

=XLOOKUP(E2:.E1000,A:A,B:B)

=XLOOKUP(E2:.E1000,A:A,C:C)

式は2つになりますが、どちらも縦にスピルするので、下にコピーする必要はありません。トリム参照もそのまま使えます。

まとめ

  • XLOOKUPの検索値を範囲にすると、1つの式で全部の行を検索できる
  • 検索値が空の行は、IFで空白にするか、トリム参照(E2:.E1000)でデータがある行までにする
  • 検索値が範囲のときは、戻り値を複数列にしても先頭の1列しか出ない
  • 複数列を出したいときは、1行ずつの式にするか、列ごとに式を分ける
  • 見つからないときの値は、{"該当なし",0} のように列ごとに指定できる

XLOOKUPに乗り換えて便利だったことは、こちらの記事で紹介しています。

【Excel】XLOOKUPに乗り換えて便利だったこと|IFERROR不要・列がずれない・左の列も取れる
VLOOKUPからXLOOKUPに乗り換えて便利だった使い方を、実務の例で紹介します。IFERRORが要らない、列を増やしてもずれない、左の列や複数の列も取り出せる、縦にも横にも検索できる、という4つの点です。

複数の条件でXLOOKUPを使う方法は、こちらの記事で紹介しています。

【Excel】XLOOKUPで複数条件を検索する|作業列なしと検索キー列の使い分け
XLOOKUPで店舗と商品のような複数の条件を検索する方法を紹介します。作業列なしで式の中で条件をつなげる方法と、検索キーの列を用意する方法の使い分けも書いています。

タイトルとURLをコピーしました