[Excel] ドロップダウンリストの作成方法

Question

前提・実現したいこと

Excelにて、ドロップダウンリストを作成しています。
他テーブルからデータを参照しようとしています。

テーブル名：都道府県
こちらの都道府県のテーブルはそれぞれ記号をもっており
この記号の値を持った都道府県ごとにドロップダウンリストを作成したいのですが、実現できずに困っていたので質問させて頂きました。

（例）
AAAの列のドロップダウンリストは、「北海道」「青森」「岩手」のみが表示される。

試したこと

AAA列の入力規則に以下の数式を入力してもドロップダウンリストは作成されませんでした。

=VLOOKUP("AAA",INDIRECT("都道府県"),1,TRUE)

補足情報（FW/ツールのバージョンなど）

Excel 2019

Answer 1

A2からA4に「AAA」と名前を付ければ
これで指定可能です。

---

以下のような表を基に説明します。

E列からH列で各記号単位の都道府県一覧表を作成しています。
E1からH1は記号を入力してください。（AAAからDDDでしょうか）
E2に下記式を入れCtrl+Shift+Enter（配列にします）
増えそうな件数分だけオートフィルで式をコピーしてください。（H列まで）

=IFERROR(INDEX(都道府県,MATCH(LARGE((都道府県[記号]=E$1)*1/ROW(都道府県[記号]),ROWS($E$1:$E1)),1/ROW(都道府県[都道府県]),0),COLUMNS(都道府県[都道府県])),"")

これでAからC列に追記されたり記号が変更されても一覧に反映されます。

続いてJ4に入力規則を設定するとします。（J3にはAAAをいれています）

=OFFSET(INDIRECT(ADDRESS(1,MATCH(J$3,$1:$1,0))),1,,1048576-COUNTBLANK(OFFSET($A:$A,,MATCH(J$3,$1:$1,0)-1))-1)

J$3の部分は直接文字列を入れても構いません。
任意の位置まで式をコピーしてください。

Comments

[tqqk]

回答ありがとうございます。
テーブルの他の行の記号が「AAA」に変更になったときや
テーブルの下に記号が「AAA」のものが追加になった時など動的に対応できるもので何か解決策などございますでしょうか？

[radames1000]

別表を使うやり方がありますがちょっと手間なので少々お待ちください。

[tqqk]

回答ありがとうございます。
とても丁寧に教えて頂き本当に助かりました！

最後におひとつ教えて頂きたいのですが、
ドロップダウンがシート１、テーブルがシート２にあった場合は入力規則で

「J$3」　⇒「"AAA"」
「$1:$1」⇒「Sheet2!$1:$1」
「$A:$A」⇒「Sheet2!$A:$A」

上記のようにしても再現できますでしょうか？
試してみたのですがうまくできませんでした。

[radames1000]

ADDRESSにもシート指定が必要です。
"Sheet2!" & ADDRESS(

[tqqk]

追加の回答ありがとうございます。
無事にドロップダウンリストを作成できました。