人気アプリの不具合・使い方まとめ

【Excel】XLOOKUPの戻り範囲とは?指定方法・複数列の取得・エラーの対処法を解説

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セルの商品コードから商品名を取得します。

  1. 商品コード・商品名・価格の一覧を用意する。
  2. E2セルに検索したい商品コードを入力する。
  3. 結果を表示するF2セルを選択する。
  4. 次の数式を入力する。

=XLOOKUP(E2,A2:A6,B2:B6)

  1. 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ノート15020田中
A002ボールペン12035佐藤
A003消しゴム10015鈴木

G2セルに商品コードを入力し、商品名から担当者までを取得したい場合は、次の数式を使用します。

=XLOOKUP(G2,A2:A4,B2:E4)

この場合、戻り範囲は4列です。

そのため、検索結果も4列に展開されます。

ただし、戻り範囲に指定する列数が増えるほど、結果を表示するための空きセルが必要になります。

XLOOKUPの戻り範囲を複数行にする方法

XLOOKUP関数は、縦方向だけでなく、横方向の表にも使用できます。

例えば、1行目に月、2行目に売上、3行目に利益が入力されている場合です。

B列C列D列
1行目1月2月3月
2行目100150200
3行目304560

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ノート15020
A002ボールペン12035
A003消しゴム10015

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!エラーが発生することがあります。

対処方法は次のとおりです。

  1. #SPILL!が表示されているセルを選択する。
  2. エラーの原因となっている範囲を確認する。
  3. 結果の展開先に入力されているデータを確認する。
  4. 不要なデータを削除するか、数式を別の場所へ移動する。
  5. 結果が正しく表示されるか確認する。

なお、スピル範囲に結合セルが含まれている場合も、エラーが発生することがあります。

また、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関数は、どちらも表から対応するデータを取得するための関数です。

ただし、戻り範囲の指定方法に違いがあります。

比較項目XLOOKUPVLOOKUP
検索範囲独立して指定できる表の左端列を検索する
戻り範囲セル範囲を直接指定列番号で指定
左方向の検索可能通常の指定では不可
複数列の取得可能列番号の配列指定などで対応可能
完全一致既定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関数より柔軟にデータを取得できます。

特に、複数列のデータをまとめて取得したい場合は、戻り範囲を広げるだけで対応できる点が便利です。

エラーが発生した場合は、検索範囲と戻り範囲の対応関係や、結果を表示するセルの状態を確認してください。

参考リンク

Pick up

1

Instagramを開いたとき、突然「生年月日を追加」のような表示が出て、先に進めなくなって困ることがあります。 さらに、そのあとログインしづらくなったり、「アカウントがブロックされました」「年齢確認 ...

2

Google検索を開いたとき、検索窓の下に自分の履歴ではないキーワードがずらっと表示されて困ったことはありませんか? 「興味のないワードが出てきて邪魔」「検索履歴じゃないのに表示されるのが気になる」「 ...

3

不在連絡かのような内容のSMS 「お客様が不在の為お荷物を持ち帰りました。こちらにてご確認ください http:// ~」 がまたまた届きました。 このメール、またまた増加しているようです。 ...

-人気アプリの不具合・使い方まとめ