表の名前定義とINDIRECT関数を併用する方法がよく知られていますが、ここではXLOOKUP関数を中心に新しい関数を使う例を紹介します。多数の名前定義を作成・管理する必要がないのがメリットです。
連動して選択肢が変わるプルダウンの設定方法
はじめにやりたいことの確認です。
左側にある表の見出しには地区名が記録されており、地区名の下にはその地区に属する店舗名が記録されています。
プルダウンを使って地区名のうち1つを右側の表のG列に入力させ、さらに地区名に対応する店舗名のうち1つをH列に入力させるよう設定するものとします。
まずはG列のプルダウンの設定です。G列の入力範囲（G3:G9）を選択し、メニューの「データ」タブにあるデータの入力規則のアイコンをクリックします。
ダイアログが出ますので「入力値の種類」欄では「リスト」を選択します。
そして「元の値」欄に「=$B$2:$D$2」と入力します。これは左の表の見出しの範囲を表しています。
あとはOKをクリックすると、プルダウンで地区名を選択できるようになりました。
このように横に並んだデータでもプルダウンに変換できるのがちょっとしたポイントです。ちなみに値の重複があってもプルダウンの選択肢では重複が除かれます。
さてここからが本題の「連動するプルダウン」の作成です。
H列の入力範囲（H3:H9）を選択し、先ほどと同様に「データ」タブのデータの入力規則のアイコンをクリックします。
ここでも「入力値の種類」欄では「リスト」を選択し、「元の値」欄には次のような式を入力します。
「元の値」欄の式
=XLOOKUP($G3,$B$2:$D$2,$B$3:$D$7)
G列の値をB2:D2の範囲から検索し、B3:D7のうち対応する列を抽出するという式です。
全体的に絶対参照（$）を使っていますが第1引数（検索値）は「$G$3」ではなく「$G3」としています。「$G$3」にしてしまうとすべてのセルが検索値としてG3セルを参照してしまいうまくいきません。
OKすると設定完了です。
画像はH3セルをクリックしたところですが、G3セルの値に対応した店舗名が選択肢として表示されているのがわかります。
もちろんそれ以外のセルでも、隣のセル（G列）の値に応じて選択肢が変わります。
ただしXLOOKUP関数により表の空白部分も抽出されるため、選択肢の最後に空白が表示されているのがわかります。表示される空白は（重複が除かれるので）せいぜい1つですが、気になる場合は次の方法を試してみてください。
空白を表示させない方法
選択肢として空白を表示させたくない場合の対応についてです。
手順は上記と同じですが、最後のH列のプルダウン設定時の「元の値」欄の式を次のようにします。
「元の値」欄の式
=LET(x,XLOOKUP($G3,$B$2:$D$2,$B$3:$D$7),TAKE(x,COUNTA(x)))
XLOOKUP関数で抽出した列にLET関数でxと名付け、TAKE関数とCOUNTA関数を使いxのうち空白以外のものを抽出しています。ここでもXLOOKUP関数の第1引数を「$G$3」としないよう注意してください。
なおTAKE/COUNTA関数のはたらきですが、正確には「xに含まれる値の数をカウントし、上から順にその行数を抽出している」ので左の表（店舗名）の途中に空白が含まれる場合にはおかしな結果になります。
また、TAKE関数が使えない場合は次のような式にするのが1つの方法です。
=LET(x,XLOOKUP($G3,$B$2:$D$2,$B$3:$D$7),OFFSET(x,0,0,COUNTA(x)))
これで選択肢に空白が表示されなくなりました。
テーブルを使う例
左側の表をテーブルにした場合の設定例です。テーブルのメリットとしてデータを追加した際にテーブルの範囲が自動的に拡張されますので、プルダウンもこれに対応させるものとします。
画像は左の表をテーブルに変換して「テーブル1」という名前を付けたところです。
G列のプルダウンの設定です。G3:G9の範囲を選択して入力規則の設定に進みます。
ここで「元の値」欄を「=テーブル1[#見出し]」としたくなるもののこれはエラーになります。
INDIRECT関数を使って「=INDIRECT("テーブル1[#見出し]")」とすることでうまくいきます。
結果は（さしあたり）上記の例と同じですが、テーブルに列が追加されたときに式を直す必要がありません。
続いてH列のプルダウンの設定です。空白を表示させない設定としてみます。
「元の値」欄は次のようにします。
「元の値」欄の式
=LET(x,INDIRECT("テーブル1["&$G3&"]"),TAKE(x,COUNTA(x))) ここでもINDIRECT関数とテーブル名を使って列を抽出し、さらにTAKE関数とCOUNTA関数で空白を除いています。店舗名の追加によりテーブルが下方に拡張されても式を変更する必要がありません。ただしテーブル（店舗名）の途中に空白が含まれているとやはりおかしな結果になります。
空白を表示させて良いのなら単に「=INDIRECT("テーブル1["&$G3&"]")」でOKです。
TAKE関数が使えない場合はTAKE関数の部分を「OFFSET(x,0,0,COUNTA(x))」に置き換えてください。
以上で設定完了です。
値の追加に応じてテーブルが自動的に拡張され、式も変えなくてよいのがメリットですが、テーブル名が変更されたときにINDIRECT関数内のテーブル名が自動的に更新されないのが注意点です。
備考
異なるデータ形式への対応
左側の表の形式が異なる場合についてですが、次の画像のようないわゆる横持ち形式の場合はXLOOKUP関数の参照範囲を変えるだけで対応できます。
J3:J9セルの設定時の「元の値」欄の式
=$B$2:$B$4
K3:K9セルの設定時の「元の値」欄の式
=XLOOKUP($J3,$B$2:$B$4,$C$2:$G$4)
いわゆる縦持ち形式の場合ですが、次のような式が考えられます。
F3:F9セルの設定時の「元の値」欄の式
=$B$2:$B$13
表には同じ地方名が複数並んでいますが、プルダウンでは値の重複が除かれるので考慮は不要です。
G3:G9セルの設定時の「元の値」欄の式
=LET(x,XLOOKUP($F3,$B$2:$B$13,$C$2:$C$13),OFFSET(x,0,0,COUNTIF($B$2:$B$13,$F3)))
XLOOKUP関数を使ってF列の値をB列から検索して最初の行を特定し、対応するC列の行を取得します（これをLET関数でxと名付けています）。さらにOFFSET関数を使い、xを起点として地方名の数と同じ数の行を抽出するという式です。地方名はソートされている必要があり、バラバラに並んでいるとおかしな結果になります。
XLOOKUPの第1引数だけでなくCOUNTIFの第2引数（検索値）も「$F$3」ではなく「$F3」にしているところが注意点です。
プルダウンを1つ（1列）で済ませる方法
ここでは2つのプルダウンを連動させましたが、1つのプルダウンで2段階の絞り込みを行うことも可能です。難しそうに思われますが、実は驚くほど簡単に実現できます。
詳しくは次の記事で紹介しています。
