ExcelでXLOOKUP関数を使っていると、「戻り範囲」という項目の意味や指定方法が分からず、数式をうまく作成できないことがあります。
「XLOOKUPの戻り範囲には何を指定すればいい?」
「戻り範囲を複数列にして、複数のデータを一度に取得したい」
「戻り範囲を指定したのに、#VALUE!や#SPILL!が表示される」
XLOOKUP関数は、指定した値を検索し、対応するデータを別の範囲から取得する関数です。
VLOOKUP関数と似ていますが、検索範囲と戻り範囲を別々に指定できる点が特徴です。
ただし、検索範囲と戻り範囲のサイズが合っていなかったり、数式の結果を表示するセルが埋まっていたりすると、エラーが発生する場合があります。
この記事では、XLOOKUP関数の戻り範囲の意味や指定方法、複数列のデータを取得する方法、エラーが表示される原因と対処法を解説します。
XLOOKUPの戻り範囲とは?検索範囲との違い
XLOOKUP関数の「戻り範囲」とは、検索値が見つかったときに、対応する結果を取り出すセル範囲のことです。
例えば、商品コードから商品名を調べたい場合、商品コードが入力されている列が「検索範囲」、商品名が入力されている列が「戻り範囲」になります。
XLOOKUP関数の基本構文は次のとおりです。
=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
| 引数 | 意味 | 必須・省略 |
|---|---|---|
| 検索値 | 探したい値 | 必須 |
| 検索範囲 | 検索値を探す範囲 | 必須 |
| 戻り範囲 | 一致した位置から結果を取得する範囲 | 必須 |
| 見つからない場合 | 一致する値がない場合の表示内容 | 省略可能 |
| 一致モード | 完全一致・近似一致などの指定 | 省略可能 |
| 検索モード | 検索方向や検索方法の指定 | 省略可能 |
最初の3つの引数を指定すれば、基本的な検索ができます。
戻り範囲の具体例
次のような商品一覧があるとします。
| A列:商品コード | B列:商品名 | C列:価格 |
|---|---|---|
| A001 | ノート | 150 |
| A002 | ボールペン | 120 |
| A003 | 消しゴム | 100 |
| A004 | ファイル | 300 |
| A005 | 付箋 | 200 |
例えば、E2セルに「A003」と入力し、対応する商品名をF2セルに表示したい場合は、次の数式を使用します。
=XLOOKUP(E2,A2:A6,B2:B6)
この数式では、次のように指定しています。
- 検索値:E2セルに入力された「A003」
- 検索範囲:A2(商品コード)
- 戻り範囲:B2(商品名)
検索範囲で「A003」が見つかると、同じ行に対応する戻り範囲の値「消しゴム」が返されます。
つまり、戻り範囲には、最終的に表示したいデータが入っている列や行を指定します。
XLOOKUPで戻り範囲を指定する方法

XLOOKUP関数では、検索したいデータと取得したいデータの位置を確認して、検索範囲と戻り範囲を指定します。
① 商品名を取得する場合
先ほどの商品一覧を使い、E2セルの商品コードから商品名を取得します。
- 商品コード・商品名・価格の一覧を用意する。
- E2セルに検索したい商品コードを入力する。
- 結果を表示するF2セルを選択する。
- 次の数式を入力する。
=XLOOKUP(E2,A2:A6,B2:B6)
- Enterキーを押して結果を確認する。
E2セルが「A003」であれば、F2セルには「消しゴム」と表示されます。
② 価格を取得する場合
同じ商品コードから価格を取得したい場合は、戻り範囲をC列に変更します。
=XLOOKUP(E2,A2:A6,C2:C6)
E2セルが「A003」であれば、結果は「100」です。
このように、検索値と検索範囲が同じでも、戻り範囲を変更することで取得するデータを切り替えられます。
③ 左側の列を戻り範囲に指定する場合
XLOOKUP関数では、検索範囲より左側の列も戻り範囲に指定できます。
例えば、C列の価格を検索して、A列の商品コードを取得する場合です。
=XLOOKUP(E2,C2:C6,A2:A6)
E2セルに「300」と入力すると、対応する商品コード「A004」が返されます。
ただし、検索範囲に同じ価格が複数ある場合は、通常、最初に見つかった値が返されます。
VLOOKUP関数では左方向の検索に制約がありますが、XLOOKUP関数では検索範囲と戻り範囲を独立して指定できるため、左側のデータも取得できます。
XLOOKUPの戻り範囲を複数列にする方法
XLOOKUP関数では、戻り範囲に複数列を指定することもできます。
例えば、商品コードを検索して、商品名と価格を同時に取得する場合です。
① 複数列をまとめて取得する
次のような商品一覧があるとします。
| A列 | B列 | C列 |
|---|---|---|
| 商品コード | 商品名 | 価格 |
| A001 | ノート | 150 |
| A002 | ボールペン | 120 |
| A003 | 消しゴム | 100 |
| A004 | ファイル | 300 |
E2セルに商品コード「A003」を入力し、F2セルから商品名と価格を表示したい場合は、次の数式を使用します。
=XLOOKUP(E2,A2:A5,B2:C5)
この数式では、戻り範囲にB2を指定しています。
検索結果は次のようになります。
| F列 | G列 |
|---|---|
| 消しゴム | 100 |
XLOOKUP関数では、戻り範囲に複数列を指定すると、対応する行の複数の値をまとめて返せます。
これは、Excelの動的配列と呼ばれる仕組みによるものです。
② 複数列の結果が自動的に展開される
先ほどの数式をF2セルに入力すると、F2セルに商品名、G2セルに価格が表示されます。
このように、1つの数式から複数のセルへ結果が広がる動作を「スピル」といいます。
通常、G2セルに別の数式を入力する必要はありません。
ただし、結果を表示する範囲に既存のデータがあると、#SPILL!エラーが発生する場合があります。
③ 3列以上のデータをまとめて取得する
例えば、商品コード・商品名・価格・在庫数・担当者の表があるとします。
| A列 | B列 | C列 | D列 | E列 |
|---|---|---|---|---|
| 商品コード | 商品名 | 価格 | 在庫数 | 担当者 |
| A001 | ノート | 150 | 20 | 田中 |
| A002 | ボールペン | 120 | 35 | 佐藤 |
| A003 | 消しゴム | 100 | 15 | 鈴木 |
G2セルに商品コードを入力し、商品名から担当者までを取得したい場合は、次の数式を使用します。
=XLOOKUP(G2,A2:A4,B2:E4)
この場合、戻り範囲は4列です。
そのため、検索結果も4列に展開されます。
ただし、戻り範囲に指定する列数が増えるほど、結果を表示するための空きセルが必要になります。
XLOOKUPの戻り範囲を複数行にする方法
XLOOKUP関数は、縦方向だけでなく、横方向の表にも使用できます。
例えば、1行目に月、2行目に売上、3行目に利益が入力されている場合です。
| B列 | C列 | D列 | |
|---|---|---|---|
| 1行目 | 1月 | 2月 | 3月 |
| 2行目 | 100 | 150 | 200 |
| 3行目 | 30 | 45 | 60 |
F1セルに「2月」と入力し、その月の売上と利益を取得する場合は、次の数式を使用します。
=XLOOKUP(F1,B1:D1,B2:D3)
この場合、検索範囲は横方向のB1、戻り範囲は2行×3列のB2です。
検索値「2月」に対応する列から、売上「150」と利益「45」が縦方向に返されます。
XLOOKUP関数は、検索範囲の向きに合わせて、戻り範囲の行または列を取得する仕組みです。
XLOOKUPで離れた複数列を取得する方法
XLOOKUP関数で複数列を取得する場合、通常はB2のように連続した範囲を指定します。
しかし、商品名と在庫数だけを取得し、その間にある価格を除外したい場合もあるでしょう。
このような場合は、CHOOSECOLS関数などを組み合わせる方法があります。
CHOOSECOLS関数を使う
例えば、次のような表があるとします。
| A列 | B列 | C列 | D列 |
|---|---|---|---|
| 商品コード | 商品名 | 価格 | 在庫数 |
| A001 | ノート | 150 | 20 |
| A002 | ボールペン | 120 | 35 |
| A003 | 消しゴム | 100 | 15 |
F2セルに商品コードを入力し、商品名と在庫数だけを取得したい場合は、次の数式を使用できます。
=CHOOSECOLS(XLOOKUP(F2,A2:A4,B2:D4),1,3)
この数式では、まずXLOOKUP関数でB列からD列のデータを取得します。
その後、CHOOSECOLS関数で1列目と3列目だけを取り出します。
| 取得する項目 | CHOOSECOLSでの指定 |
|---|---|
| 商品名 | 1 |
| 価格 | 2 |
| 在庫数 | 3 |
例えば、F2セルに「A003」を入力した場合は、「消しゴム」と「15」が横方向に表示されます。
ただし、CHOOSECOLS関数は、すべてのExcelバージョンで使用できるわけではありません。
対応していない場合は、XLOOKUP関数を列ごとに使用する方法もあります。
XLOOKUPの戻り範囲を固定する方法

XLOOKUP関数を下方向や横方向にコピーすると、セル参照が自動的に変わることがあります。
検索範囲や戻り範囲を固定したい場合は、絶対参照を使用します。
絶対参照で戻り範囲を固定する
例えば、次の数式です。
=XLOOKUP(E2,$A$2:$A$100,$B$2:$B$100)
この数式では、検索範囲と戻り範囲を絶対参照で固定しています。
| 指定方法 | 意味 |
|---|---|
| A2 | コピー先に応じて参照範囲が変わる |
| $A$2:$A$100 | 行と列を固定する |
| A$2$100 | 行番号を固定する |
| $A2:$A100 | 列番号を固定する |
検索値のE2セルは固定していないため、数式を下方向にコピーするとE3、E4と変わります。
一方、検索範囲と戻り範囲は固定されたままです。
F4キーで絶対参照に切り替える
Windows版Excelでは、数式の編集中に参照部分を選択してF4キーを押すと、絶対参照などを切り替えられます。
ただし、ノートパソコンによってはFnキーと組み合わせる必要があります。
XLOOKUPで戻り範囲を別シートに指定する方法
XLOOKUP関数では、別のワークシートにあるデータも戻り範囲に指定できます。
例えば、「商品一覧」というシートに商品コードと商品名が保存されている場合です。
検索値が現在のシートのA2セルにあるときは、次の数式を使用できます。
=XLOOKUP(A2,商品一覧!A2:A100,商品一覧!B2:B100)
この数式では、商品一覧シートのA列から商品コードを検索し、B列の商品名を取得します。
シート名に空白がある場合
シート名に空白が含まれる場合は、シート名をシングルクォーテーションで囲みます。
例えば、「商品 一覧」というシート名の場合です。
=XLOOKUP(A2,'商品 一覧'!A2:A100,'商品 一覧'!B2:B100)
また、数式をコピーして使用する場合は、必要に応じて検索範囲と戻り範囲を絶対参照にしてください。
=XLOOKUP(A2,'商品 一覧'!$A$2:$A$100,'商品 一覧'!$B$2:$B$100)
XLOOKUPで戻り範囲を指定するとエラーになる原因
XLOOKUP関数では、戻り範囲の指定方法によってエラーが発生することがあります。
主なエラーと原因は次のとおりです。
| エラー | 主な原因 | 対処法 |
|---|---|---|
| #VALUE! | 検索範囲と戻り範囲のサイズが合わないなど | 範囲の行数・列数を確認する |
| #N/A | 検索値が見つからない | 検索値と検索範囲を確認する |
| #SPILL! | 結果の展開先にデータがある | スピル範囲を空ける |
| #REF! | 参照先が無効になっている | セル参照を修正する |
| #NAME? | 関数名が認識されない | Excelの対応バージョンを確認する |
| #CALC! | 配列計算などを処理できない | 数式の構成を確認する |
① #VALUE!:検索範囲と戻り範囲のサイズが合っていない
XLOOKUP関数では、検索範囲と戻り範囲の対応関係が重要です。
例えば、次の数式を確認してください。
=XLOOKUP(E2,A2:A10,B2:B5)
検索範囲はA2の9行ですが、戻り範囲はB2の4行です。
このように、検索範囲と戻り範囲の行数が一致していないと、#VALUE!エラーになる場合があります。
正しく指定するには、次のように範囲をそろえます。
=XLOOKUP(E2,A2:A10,B2:B10)
ただし、戻り範囲を複数列にする場合は、列数まで検索範囲と同じにする必要はありません。
縦方向に検索する場合は、基本的に行数が対応していることが重要です。
② #N/A:検索値が見つからない
XLOOKUP関数では、検索値が見つからない場合、通常は#N/Aエラーが返されます。
例えば、次の数式です。
=XLOOKUP(E2,A2:A10,B2:B10)
E2セルの商品コードがA2に存在しない場合は、#N/Aが表示されます。
この場合は、検索値が正しいか確認してください。
また、数字が文字列として保存されているなど、データの種類が一致していない場合もあります。
③ #N/Aを「該当なし」に変更する
XLOOKUP関数では、第4引数を使って、検索値が見つからなかった場合の表示内容を指定できます。
=XLOOKUP(E2,A2:A10,B2:B10,"該当なし")
この数式では、検索値が見つからない場合に「該当なし」と表示されます。
空白のように見せたい場合は、次のように指定できます。
=XLOOKUP(E2,A2:A10,B2:B10,"")
ただし、これは検索値が見つからない場合の表示を変更する設定です。
戻り範囲のサイズが間違っているなど、ほかの原因によるエラーまで解消できるわけではありません。
④ #SPILL!:複数列の結果を表示できない
戻り範囲に複数列を指定した場合、XLOOKUP関数は複数のセルへ結果を展開します。
例えば、次の数式です。
=XLOOKUP(E2,A2:A10,B2:D10)
この数式をF2セルに入力すると、通常はF2からH2まで結果が表示されます。
しかし、G2やH2にすでに値が入力されている場合は、#SPILL!エラーが発生することがあります。
対処方法は次のとおりです。
- #SPILL!が表示されているセルを選択する。
- エラーの原因となっている範囲を確認する。
- 結果の展開先に入力されているデータを確認する。
- 不要なデータを削除するか、数式を別の場所へ移動する。
- 結果が正しく表示されるか確認する。
なお、スピル範囲に結合セルが含まれている場合も、エラーが発生することがあります。
また、Excelテーブルの内部ではスピルした配列数式を使用できないため、テーブルの外側に数式を配置してください。
⑤ #REF!:戻り範囲の参照先が無効になっている
#REF!エラーは、数式が無効なセル参照を含んでいる場合などに表示されます。
例えば、参照していた列やシートを削除した場合です。
数式バーを確認し、検索範囲や戻り範囲に#REF!が含まれていないか確認してください。
また、別のExcelブックを参照している場合は、参照先のファイルが移動・削除されていないか確認する必要があります。
XLOOKUPの戻り範囲が空白なのに「0」が表示される場合

XLOOKUP関数では、戻り範囲のセルが空白の場合、結果として「0」が表示されることがあります。
例えば、次の数式です。
=XLOOKUP(E2,A2:A10,B2:B10)
検索値に対応するB列のセルが空白でも、結果が「0」になる場合があります。
空白セルを空白として表示する方法
次のように、戻り範囲の値を判定する方法があります。
=LET(x,XLOOKUP(E2,A2:A10,B2:B10),IF(x="","",x))
この数式では、XLOOKUP関数の結果を変数xに保存し、空白と判定される場合は空文字列を返します。
ただし、この方法では、戻り値が数値の0である場合も空白として扱われる可能性があります。
そのため、実際の0と空白を厳密に区別したい場合には適していません。
実際の0と空白を区別する必要がある場合は、検索した行のセルが本当に空白かどうかを確認する方法などを検討してください。
XLOOKUPの戻り範囲とVLOOKUPの違い
XLOOKUP関数とVLOOKUP関数は、どちらも表から対応するデータを取得するための関数です。
ただし、戻り範囲の指定方法に違いがあります。
| 比較項目 | XLOOKUP | VLOOKUP |
|---|---|---|
| 検索範囲 | 独立して指定できる | 表の左端列を検索する |
| 戻り範囲 | セル範囲を直接指定 | 列番号で指定 |
| 左方向の検索 | 可能 | 通常の指定では不可 |
| 複数列の取得 | 可能 | 列番号の配列指定などで対応可能 |
| 完全一致 | 既定 | FALSEなどの指定が必要 |
| 見つからない場合の表示 | 第4引数で指定可能 | IFNAなどを組み合わせる |
例えば、VLOOKUP関数では次のように指定します。
=VLOOKUP(E2,A2:C10,2,FALSE)
この数式では、A2の2列目から結果を取得します。
一方、XLOOKUP関数では次のように指定します。
=XLOOKUP(E2,A2:A10,B2:B10)
XLOOKUP関数は、取得したいデータが入っている範囲を直接指定できるため、列番号を数える必要がありません。
また、表の途中に列を追加した場合でも、参照範囲が適切に調整されれば、列番号のずれによる問題を避けやすくなります。
XLOOKUP関数が使えないExcelのバージョン
XLOOKUP関数は、すべてのExcelバージョンで使用できるわけではありません。
Microsoft公式では、Microsoft 365版Excel、Excel 2021、Excel 2024、Excel for the webなどで利用できる関数として案内されています。
一方、Excel 2016やExcel 2019では、XLOOKUP関数を新しく使用することはできません。
| Excelのバージョン | XLOOKUP関数 |
|---|---|
| Microsoft 365版Excel | 対応 |
| Excel 2024 | 対応 |
| Excel 2021 | 対応 |
| Excel for the web | 対応 |
| Excel 2019 | 非対応 |
| Excel 2016 | 非対応 |
古いExcelでXLOOKUP関数を使用すると、#NAME?エラーなどが表示される場合があります。
また、新しいExcelで作成したXLOOKUP関数を含むファイルを古いExcelで開いた場合も、正しく計算できないことがあります。
その場合は、VLOOKUP関数やINDEX関数とMATCH関数を組み合わせる方法を検討してください。
よくある質問
XLOOKUPの戻り範囲には何を指定する?
検索値が見つかったときに、取得したいデータが入力されているセル範囲を指定します。
例えば、A列で商品コードを検索してB列の商品名を取得する場合は、B列が戻り範囲です。
XLOOKUPの戻り範囲は複数列にできる?
はい。例えば、次のように指定できます。
=XLOOKUP(E2,A2:A10,B2:D10)
この場合、検索値に対応するB列からD列までのデータをまとめて取得できます。
XLOOKUPの検索範囲と戻り範囲は同じサイズにする必要がある?
縦方向に検索する場合は、検索範囲と戻り範囲の行数が対応している必要があります。
ただし、戻り範囲は複数列でも構いません。
横方向に検索する場合は、検索範囲と戻り範囲の列数が対応している必要があります。
XLOOKUPの戻り範囲を別シートに指定できる?
はい。例えば、次のように指定できます。
=XLOOKUP(A2,商品一覧!A2:A100,商品一覧!B2:B100)
別シートに保存されたデータから検索結果を取得できます。
XLOOKUPで#SPILL!が表示されるのはなぜ?
複数列や複数行の結果を表示する範囲に、既存のデータや結合セルなどがある可能性があります。
結果を表示するセル範囲を空けるか、数式を別の場所へ移動してください。
まとめ
XLOOKUP関数の戻り範囲とは、検索値が見つかったときに、対応する結果を取得するセル範囲のことです。
- XLOOKUPの戻り範囲には、取得したいデータが入力されているセル範囲を指定する。
- 基本構文は
=XLOOKUP(検索値,検索範囲,戻り範囲)。 - 戻り範囲を複数列にすると、複数のデータを一度に取得できる。
- 横方向の表では、複数行のデータを取得することもできる。
- 離れた列を取得したい場合は、CHOOSECOLS関数などを組み合わせる。
- 戻り範囲を固定したい場合は、絶対参照を使用する。
- #VALUE!エラーでは、検索範囲と戻り範囲のサイズを確認する。
- #SPILL!エラーでは、結果を表示するセル範囲を確認する。
- #N/Aエラーでは、検索値が存在するか確認する。
- XLOOKUP関数は、Excel 2019以前のバージョンでは使用できない。
XLOOKUP関数は、検索範囲と戻り範囲を別々に指定できるため、VLOOKUP関数より柔軟にデータを取得できます。
特に、複数列のデータをまとめて取得したい場合は、戻り範囲を広げるだけで対応できる点が便利です。
エラーが発生した場合は、検索範囲と戻り範囲の対応関係や、結果を表示するセルの状態を確認してください。