XLOOKUPは、検索値にE2のような1つのセルではなく、E2:E7のような範囲を指定できます。範囲にすると、結果が下のセルに自動で広がり(スピル)、1つの式で全部の行を検索できます。
ただし、この書き方には注意点が2つあります。検索値が空の行にも値が表示されることと、戻り値を複数列にしても1列しか出ないことです。
この記事では、検索値を範囲にする書き方と、この2つへの対処を紹介します。なお、この記事の表は説明のために作ったもので、例の数字も架空です。
例として使う表
商品の一覧が、このように入っているとします。
| 商品コード | 商品名 | 単価 |
|---|---|---|
| 1001 | 商品A | 300 |
| 1002 | 商品B | 500 |
| 1003 | 商品C | 250 |
| 1004 | 商品D | 800 |
| 1005 | 商品E | 150 |
見出しは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 | 商品C | 250 |
| 1001 | 商品A | 300 |
| 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に乗り換えて便利だったことは、こちらの記事で紹介しています。

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

