◆OFFSET関数を組み合わせて設定する
部・課両方の増減に自動対応する二段階リストを作成するためには、
OFFSETをさらに組み合わせて実現します。

まずは、名前定義に設定する「課」の数式を見てみましょう。
・課

=OFFSET(マスタ!$A$1,1,MATCH($A$3,マスタ!$1:$1,0)-1,COUNTA(OFFSET(マスタ!$A:$A,0,MATCH($A$3,マスタ!$1:$1,0)-1))-1,1)

■解説：
「課」の名前定義は、ご覧のように非常に長いものになっています。
いきなりこの数式を理解しようとしても大変なことになってしまいますので
順序よく考えていくようにします。

まず、A列の営業部だけを増減対応させる場合は、名前の定義で、

=OFFSET($A$1,1,0,COUNTA($A:$A)-1,1) ・・・ ①

このように数式を入れれば良いことを、まずはご理解下さい。

この数式は、A列限定の数式になっています。
そこで、このA列が自動で右にずれれば良いと考えられます。

参照範囲をずらすには、OFFSET関数を使います。
したがって、上記数式のうち、OFFSETの第三引数の横にずらす数値と、
A列部分($A:$A)にOFFSETを組み込むようにします。

では、何列分横にずらすかになりますが、
入力シートのA3セルの値が、マスタシートの1行目で何番目にあるかがわかれば、
その分だけずらせば良いことになります。

ある値が、ある範囲内の何番目にあるかを調べるにはMATCH関数ですね。

=MATCH(入力$A3,マスタ!$1:$1,0) ・・・ ②

これで、列位置を取得できました。

①の数式の第三引数は0となっていて、基準セル($A$1)を横にずらす指定になりますので、
ここに、②の数式を組み込みます。

ただし②のMATCH関数で出る値はA3セルが「営業部」なら1ですが、
その時にずらす数値は0になりますので、②-1と、調整値を加えて組み込みます。

=OFFSET(マスタ!$A$1,1, MATCH(入力$A3,マスタ!$1:$1,0)-1

次は、$A:$Aを横にずらすのでOFFSET関数をさらに組み込んでいくことになります。
考え方は前述と同様となります。

=OFFSET(マスタ!$A:$A,0,MATCH(入力!$A3,マスタ!$1:$1,0)-1)・・・ ③

OFFSETの第四・第五引数は省略可能で、省略した場合は、基準セル範囲と同じ大きさになります。
ここでは列全体となります。

この③を、$A:$Aの代わりに組み込んで、当初の数式が完成します。

あとは、B3セルにデータの入力規則で、元の値ボックスに「課」を設定すればOKです。

以上で完成になりますが、とても長く難しい数式です。
Excelの数式としては、最上級の難しさといっても過言ではありません。

この場合では、A列に縦方向に部署名を入れていく場合には使用できますが、
それ以外の複数個所で使用する場合はそれぞれに名前定義が必要となってしまいます。

そのような場合は、相対参照を使ってさらに工夫することで解決できます。

【関連記事】
OFFSET関数
COUNTA関数
INDIRECT関数
MATCH関数
6大必須関数(3) COUNTA関数～データの「件数」を見る関数