XLOOKUPの範囲を自動拡張｜テーブル参照・列全体指定の使い方

2024年12月22日2026年10月22日

XLOOKUPの参照範囲をデータ追加に合わせて広げるには、元データをExcelの「テーブル」にし、列名で参照する方法が便利です。A2:A100のような固定範囲では、101行目に追加したデータは検索されません。

練習用Excelファイル（無料ダウンロード）

列全体を指定する検索式を確認できます。テーブル化する手順は本文に沿って操作してください。ボタンをクリック／タップして保存し、Excelで開いてください。

無料Excelサンプルファイルダウンロード（.xlsx・12KB）

Excelファイル（.xlsx）／登録不要／数式・練習データ入り

まず動かしてみよう｜この記事のサンプルをその場で試せます

練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。

参照範囲を広げる3つの方法

指定方法	追加した行の扱い	向いている使い方
A2:A100などの固定範囲	指定範囲を超えると対象外	行数が決まった小さな表
A:Aなどの列全体	同じ列の追加行も対象	簡単に試したい場合
テーブル名[列名]	テーブルへ追加した行も対象	継続して更新する一覧

ポイント自動拡張するかどうかはXLOOKUPだけの機能ではなく、参照範囲の指定方法で決まります。VLOOKUPでもテーブルを参照できます。

▶ 3つの範囲指定の違いを、上の練習ツールで試す

列全体を検索する方法

G2に検索する発注番号、A列に発注番号、C列に発注者がある場合です。

G3に入力：列全体を指定して検索する

数式=IF(G2="","",XLOOKUP(G2,A:A,C:C,"該当なし"))

ひとことでA列とC列を丸ごと範囲にして、あとから行を足しても検索されるようにする

G2

検索値です。探したい発注番号が入っています

A:A

検索範囲です。A列まるごとを指定します。足した行も対象になります

C:C

戻り範囲です。見つかった行の発注者を返します。検索範囲と同じ、列全体にします

G2=""

外側のIFで、検索セルが空白かどうかを確認します。空白なら、何も表示しません

データの流れ

① A列から発注番号を探して、同じ行のC列を返す結果

A7H-1005G3苺電機

ポイントA列とC列に追加した行も検索されます。検索セルが空白なら何も表示しないよう、外側のIFで確認しています。

注意列全体には見出し、合計行、表の下のメモも含まれます。また、多数の数式や配列計算で列全体を繰り返し扱うと計算負荷が増える場合があります。検索結果のセルは参照しているA列・C列の外に置いて、循環参照を避けます。

▶ この式（G3）を、上の練習ツールで試す／空白を確認するIFの式（E7）を、上の練習ツールで試す

列全体を指定して発注者を検索する（XLOOKUP）式 =XLOOKUP(G2,A:A,C:C,"該当なし") の解説図。G3に入力する — G3に =XLOOKUP(G2,A:A,C:C,"該当なし") を入力。A列で H-1005 を探す → 7行目で見つかる C列の7行目 → 苺電機

テーブルで参照範囲を自動拡張する

1．見出しを含む一覧をテーブルにする

表内のセルを選び、「挿入」→「テーブル」を選択します。WindowsではCtrl+Tでも開けます。対象範囲と「先頭行をテーブルの見出しとして使用する」を確認して確定します。

2．テーブル名と列名を確認する

テーブル内を選択し、「テーブルデザイン」でテーブル名を確認します。ここでは「発注一覧」、見出しを「発注番号」「発注者」とします。実際の見出しが「受注番号」「客名」なら、その名前に読み替えてください。

3．検索式をテーブルの外に入力する

G3に入力：テーブルの列名で指定して検索する

数式=IF(G2="","",XLOOKUP(G2,発注一覧[発注番号],発注一覧[発注者],"該当なし"))

ひとことで範囲ではなく「テーブル名[列名]」で指定して、行を足すたびに範囲が自動で広がるようにする

発注一覧[発注番号]

検索範囲です。テーブル「発注一覧」の「発注番号」列を指します。行を足すと、自動で広がります

発注一覧[発注者]

戻り範囲です。テーブルの「発注者」列を指します

G2

検索値です。テーブルの外のセルに置きます

補足テーブルは、練習ツールでは再現していません。ダウンロードした練習ファイルの「応用」シートで試せます。

ポイント角かっこ内が列名です。セルの範囲をマウスで選ぶと構造化参照が入力されるので、列名を手入力するより間違いを減らせます。

4．1行追加して検索する

ポイントテーブルの直下に新しい発注番号と発注者を追加し、その行までテーブルの書式・範囲が広がったことを確認します。G2に追加した番号を入力し、発注者が出れば完了です。離れたセルへ貼り付けただけではテーブルの一部にならないので、「テーブルのサイズ変更」で範囲を確認してください。

追加したのに見つからないとき

確認点	対処
追加行がテーブルの外にある	テーブルの範囲へ追加するか、サイズを変更する
数式がまだA2:A100を参照している	テーブル名[列名]へ書き換える
検索範囲と戻り範囲の行数が違う	同じテーブルの列を指定する
番号が数値と文字列で混在している	入力形式を統一する。先頭ゼロが必要なら文字列で管理する
同じ発注番号が複数ある	番号の重複を確認する。XLOOKUPは通常最初の1件を返す