MENU

Excelの入力規則でXLOOKUPがエラーになる原因と解決策｜ドロップダウンの元の値・テーブル参照に対応

Excel

Excelのデータの入力規則（リスト）の「元の値」にXLOOKUPを直接入れると、「この数式には問題があります」と表示されることがあります。原因と、補助列・スピル範囲・名前定義で安定して動くドロップダウンを作る手順をまとめます。

ITtripTOOLS
その「面倒」、無料で解決。 ITtripがつくる、6つの道具。 無料ツール一覧へ すべて無料・追加課金なし
先頭0を守るCSV編集こわさないCSV PDFに直接書き込みかけるPDF 写真＋説明をPDFに写レポ スマホ⇄PCで共有コピペ便 · 文章・写真 みんなの日程を調整日程Fit 匿名質問・投票を集めるKOEQ
目次

起きている現象：入力規則の「元の値」にXLOOKUPを入れると弾かれる

たとえば、Excelのテーブル employees があり、セル F10 に「Department」などのヘッダー名が入っている状況を想定します。

このとき、データの入力規則（リスト）の「元の値」に次の式をそのまま入力すると、エラーになることがあります。

=XLOOKUP(F10,(employees[#Headers]),employees)&""

表示されやすいメッセージ例は次のようなものです。

  • 「この数式には問題があります。数式を入力しようとしていませんか？」
  • 「数式に問題があります」

ここで重要なのは、「XLOOKUPが間違っている」のではなく、入力規則の“元の値”欄が想定している入力形式と、XLOOKUPの戻り値の性質が噛み合っていないという点です。

原因：入力規則（リスト）の「元の値」は“範囲参照”が基本で、動的配列の値をそのまま受け取れない

データの入力規則のリストは、見た目は「数式欄」っぽいのですが、実際には何でも入るわけではありません。基本は次の2パターンです。

「元の値」で素直に動く指定例特徴
セル範囲（1列/1行）=$G$2:$G$100もっとも安定。行数が変わる場合は工夫が必要
スピル範囲参照（#）=$G$2#Microsoft 365の動的配列と相性が良い
固定のリスト（区切り文字で列挙）営業,総務,人事手入力向け。長いと管理が破綻しやすい

一方で、次のような指定は「元の値」欄ではエラーになりやすい（または期待どおりに評価されない）代表例です。

エラーになりやすい指定例なぜ問題になるか
XLOOKUPなどの複雑な数式を直接入力=XLOOKUP(...)入力規則は「値の配列」ではなく「範囲参照」を欲しがるため
動的配列関数の結果を“その場で”生成=SORT(UNIQUE(...))セル上ではスピルできても、元の値欄はスピル先を持てない
テーブルの構造化参照を元の値に直書き=employees[Department] / employees[#Headers]入力規則UI側で構造化参照を正しく解釈できず弾かれやすい

今回の式のポイントは、XLOOKUP が「見つかった列の値を配列として返す」ことです。セルに入れてスピルさせる分には問題ありません。しかし入力規則の「元の値」は、基本的に“参照先の範囲”を指定させたい機能であり、数式が返した配列そのものを受け取ってドロップダウンにする、という設計になっていません。

また、式末尾の &"" は「数値を文字列化する」などの目的で使われることがありますが、今回の根本原因（配列を直接渡していること、構造化参照が混ざっていること）を解決するものではありません。

最優先でおすすめ：補助列（スピル）にXLOOKUP結果を出し、その範囲を入力規則で参照する

実務でいちばん安定し、トラブルシュートもしやすいのがこの方法です。ポイントはシンプルで、入力規則に数式を書かないことです。

手順

  1. シートの空きスペースに「ドロップダウン候補を出す場所（補助列）」を用意します。例として G2 を使います。
  2. G2 にXLOOKUPを入力します（テーブルを使う場合の例）。 =XLOOKUP($F$10, employees[#Headers], employees) これで、F10 のヘッダー名に一致する列が、G2 を起点にスピルして縦に展開されます。
  3. 空白が混ざる・重複が多いなど、ドロップダウンを整えたい場合は、補助列側で整形します。たとえば「空白を除外」するなら次のように書けます。 =LET( col, XLOOKUP($F$10, employees[#Headers], employees), FILTER(col, col<>"") ) さらに「空白除外＋重複排除＋並べ替え」までやるなら、次のようにしておくと、入力規則側が常に見やすい候補になります。 =LET( col, XLOOKUP($F$10, employees[#Headers], employees), SORT(UNIQUE(FILTER(col, col<>""))) )
  4. ドロップダウンを設定したいセルを選択し、[データ] → [データの入力規則] を開きます。
  5. 「入力値の種類」を リスト にして、「元の値」に補助列のスピル範囲を指定します。 =$G$2#

この方法が“王道”な理由

  • 元の値欄は参照だけにして、制約の強い場所で無理をしない
  • 候補リストがセル上に見えるので、間違いに気づきやすい（デバッグしやすい）
  • 候補の加工（空白除外・重複排除・並べ替え）を、入力規則から切り離して自由にできる

どのセルに何を書くか（例）

役割セル例内容
列名（ヘッダー）指定F10Department や Location など
候補リスト生成（補助列）G2=LET(col, XLOOKUP(...), SORT(UNIQUE(FILTER(...))))
入力規則の元の値設定画面=$G$2#

補助列を見せたくない場合：名前の定義（名前の管理）で“動的リスト”を作って参照する

「補助列は作りたいが、画面上に見せたくない」「別シートにまとめて隠したい」という場合は、名前の定義（Name Manager）を経由すると管理が楽になります。

入力規則の元の値欄に直接数式を入れるのではなく、“名前”に数式を持たせて、元の値欄では名前だけを参照します。

手順

  1. [数式] → [名前の管理] → [新規作成] を開きます。
  2. 名前を付けます（例：dvEmployees）。
  3. 「参照範囲」に、候補一覧を返す数式を入れます。例： =LET( col, XLOOKUP(Sheet1!$F$10, employees[#Headers], employees), SORT(UNIQUE(FILTER(col, col<>""))) ) ※Sheet1 は実際のシート名に置き換えてください。
  4. ドロップダウンを設定したいセルで、入力規則（リスト）の「元の値」に次のように入力します。 =dvEmployees

この方法が向いているケース

  • 候補リストの生成ロジックを「見えないところ」に置きたい
  • ドロップダウン候補を複数セルで使い回したい（同じ名前を参照すればOK）
  • 別シートにマスタ・候補生成を集約して、入力シートをスッキリさせたい

なお、組織のExcel環境（バージョン差、更新チャネル差）によっては、名前定義の動的配列が入力規則で期待どおりに扱えないケースもあります。その場合は、前章の補助列＋スピル参照（#）が最も再現性が高いです。

構造化参照が原因で詰まるパターン：テーブル参照は“入力規則に直書きしない”が鉄則

今回の例では employees[#Headers] のような構造化参照が含まれていました。構造化参照は、セル上の数式では非常に便利ですが、入力規則の「元の値」欄は構造化参照との相性が良くありません。

対策は、次のいずれかです。

  • 入力規則にはセル範囲（またはスピル範囲）だけを書き、構造化参照は補助列の数式側に閉じ込める
  • 構造化参照を使うなら、名前の定義を経由して入力規則では名前だけ参照する

「テーブルを使う＝入力規則にテーブル名を書けば良い」ではない

よくある誤解が、次のような指定です。

=employees[Department]

セル上の数式なら正しい書き方ですが、入力規則の元の値に直書きするとエラーになりやすい代表例です。どうしてもテーブル列をソースにしたい場合は、名前の管理でテーブル列を指す名前（例：DeptList）を作り、入力規則は =DeptList の形に寄せるのが安全です。

上級者向け：入力規則が“範囲参照”として扱える形にして返す（OFFSET/INDIRECTなど）

入力規則は「値の配列」よりも「範囲参照」が得意です。そこで、数式の戻り値を“参照”に寄せる考え方もあります。ただし、運用・性能・保守の面で注意点が多いので、実務では最初におすすめする方法ではありません。

考え方（例：範囲を返す関数を使う）

  • OFFSET：基準セルから行列オフセットして範囲を返す（揮発性で重いことがある）
  • INDIRECT：文字列から参照を作る（揮発性、参照切れに弱い）

こうした関数は「元の値欄に直接書ける場合」もありますが、構造化参照が絡むと失敗しやすいため、結局は名前の定義に入れて呼び出す形に落ち着くことが多いです。

もし「選んだヘッダー名に応じて列を切り替える」という要件が強い場合でも、まずは補助列でスピルさせて、入力規則はスピル参照にするほうが、壊れにくく説明もしやすい設計になります。

よくあるつまずきと、切り分けチェックリスト

ヘッダー名が一致していない（F10の値が微妙に違う）

F10 に入っている文字が、テーブルのヘッダーと完全一致していないと、XLOOKUPは見つけられずエラーや空になります。特に多いのが次のパターンです。

  • 見た目は同じだが末尾にスペースがある
  • 全角/半角が混ざっている
  • 表記ゆれ（例：Dept / Department）

対策として、ヘッダー名の入力自体も別のドロップダウンにしてしまう、または TRIM（余分なスペース削除）を噛ませるのが有効です。

空白がドロップダウンに大量に出てしまう

元データに空白セルがあると、そのまま候補に出ます。候補を整えるなら、補助列側で FILTER を使って空白除外を入れておくと、利用者のストレスが減ります。

重複が多く、リストが見づらい

部署名や拠点名のように同じ値が繰り返される列は、UNIQUE で重複排除し、SORT で並べ替えると、選択肢が短くなってミスが減ります。

入力規則の「元の値」に別シート範囲を直接入れようとしている

運用上は「候補一覧は別シート（マスタシート）に置きたい」ことが多いですが、入力規則の元の値は、環境によっては別シート参照を直接受け付けないことがあります。その場合は、次のいずれかに寄せると安定します。

  • 候補一覧（スピル結果）を同一シートに置き、元の値は =$G$2# のように参照する
  • 別シートに置くなら、名前の定義で参照を作り、元の値は =名前 にする

「”&”” を付ければ直る」と思っている

&"" は型（数値→文字列）を整える用途で、入力規則が求める「範囲参照」の問題は解決しません。今回のエラーは“型”より“渡し方”が原因なので、設計を変える必要があります。

実務で失敗しない設計のコツ：入力規則は「参照だけ」にして、ロジックはシート側に逃がす

入力規則のドロップダウンは、入力ミス防止に強力ですが、元の値欄は自由度が低い場所です。そこに複雑な数式を押し込もうとすると、以下のような運用コストが上がります。

  • 突然エラーになっても原因が追いにくい
  • 環境差（バージョン/更新チャネル/OS差）で挙動が変わりやすい
  • 保守担当が変わったときにブラックボックス化しやすい

おすすめの設計指針は次のとおりです。

設計指針狙い具体例
元の値は「参照だけ」入力規則の制約に引っかからない=$G$2# / =dvEmployees
ロジックはシート or 名前定義へ動作確認・修正がしやすいXLOOKUP/LET/FILTER/UNIQUE/SORT はセル側で実行
候補リストは“整形済み”で渡す利用者の操作ミスを減らす空白除外、重複排除、並べ替え

まとめ：XLOOKUPは“入力規則に直書きしない”が正解

  • 入力規則（リスト）の「元の値」は、基本的にセル範囲か固定リストを想定している
  • XLOOKUP のような動的配列を返す式を「元の値」に直書きすると、エラーになりやすい
  • 最も安定するのは、補助列でスピルさせて、入力規則は =$G$2# のように参照する方法
  • 補助列を見せたくないなら、名前の定義で動的リストを作り、元の値は =名前 にする

ドロップダウンは「入力を楽にする」だけでなく、「入力を揃える」ための仕組みです。だからこそ、壊れやすい場所（元の値欄）にロジックを詰め込まず、見える場所（補助列）か管理しやすい場所（名前定義）にロジックを逃がすのが、長く使えるExcel設計になります。

Excel
Excel ドロップダウンリスト 構造化参照 XLOOKUP データの入力規則

この記事を書いた人

佐藤健司のアバター 佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

関連記事

コメント

コメントする

目次