VLOOKUP関数の範囲指定を動的に処理する！
２つのプルダウンリストを連携させる時に、入力規則で利用することで有名な､INDIRECT関数。
もともとは引数に指定した名前（文字列）のオブジェクトを返す関数です。
この関数を使うことで、VLOOKUPなど引数に範囲をしていする関数の指定範囲を動的に処理することが出来ます！
INDIRECT関数
まずはINDIRECT関数についてです。
=INDIRECT(参照文字列)
引数には参照文字列を指定します。これはオブジェクトの名前のことです。
オブジェクトとは、「シート」とか「セル」とか「セル範囲」、「テーブル」など、Excelに出てくるパーツの事と思ってくれれば大丈夫です。
このパーツ（オブジェクトのこと）には、それぞれ名前を付けることが出来ます。
次のサンプル動画をご覧ください。
動画の中では、C5セル（オブジェクト）に「指定のセル」という名前をつけ、C3セルに「=指定のセル」と参照して表示させています。
通常は、A1とかB3とかいうように、セルには名前がついていますが、これは左上の「名前ボックス」変更することが出来ます。
覚えやすいように名前を変えることができるんですね。
INDIRECT関数で指定する「参照文字列」も、元々のセル（オブジェクト）の名前、例えば「A1」とか「B3」とかを指定出来ますし、変更して付けた名前（この場合「指定のセル」）も使用することが出来ます。
試しに、先程のサンプルのC8セルに
=INDIRECT(“指定のセル”)
C11セルに
=INDIRECT(“C5”)
と入力すると同じ結果になることが分かります。
そして、これは単体セルだけでなく、他のオブジェクト「セル範囲」や「テーブル」でも同じことが可能です。
セル範囲に名前を付ける
セル範囲に名前を付ける場合は、名前を付けたいセル範囲を選択して、左上の「名前ボックス」で名前を付けます。
これをINDIRECT関数で参照します。
B8セルに次の数式を入力します。
=INDIRECT(“指定したセル範囲”)
名前を付けた範囲「B2:C3」が表示されることが分かります。
テーブルに名前を付ける
テーブルも同じように使えます。名前の付け方が違うので、次をご覧ください。
※テーブの作り方が分からない方は先にこちらの記事の後半を御覧ください！
テーブルの名前を変更するには、テーブルの中を選択して、「テーブルデザイン」タブを表示させて、左の方に出てくる「テーブル名」のボックスの中の名前を変更します。デフォルトでは「テーブル◯◯」みたいな感じになっています。
テーブル名を「商品単価テーブル」としましょう。
これでテーブルの名前が変更されましたので、INDIRECT関数でこの名前が使えるようになります。
E1セルに、次の数式を入力します。
=INDIRECT(“商品単価テーブル”)
Enterで確定します。
図のように、商品単価テーブルのデータの中身が表示されます。
※見出しは入らないのでご注意ください。
INDIRECT関数でVLOOKUP関数を動的に処理する
それではやっと本題です（笑）
ここからは、ここまで紹介したINDIRECT関数を使うことで、VLOOKUP関数の検索範囲を動的に処理する方法を紹介します！
最終的に、次のようなものを作っていきます。
どのようなものかというと次のようなものです。
B4:C6に、「商品選択」「オプション選択」「金額」の項目があります。
商品の選択項目は、C4セルに商品名テーブル（B9:B13）の項目でプルダウンの入力規則を指定
オプション選択は、C5セルにC4セルで選んだ商品の種類に応じで、ハンバーガーテーブル（D9:E14）、カレーテーブル（G9:H14）、唐揚げテーブル（J9:K14）、カツ丼テーブル（M9:N14）から動的にプルダウンリストを作成するようにしています。
C4とC5のプルダウンリストを連携させるために、各テーブルには、商品名の各名前と同じテーブル名を付けています。
よく分からないという方は、こちらの記事を先にご覧ください。
上の記事にはありませんので、念のためですがプルダウンリストを連動させるときのテーブルが、２列以上のテーブルの場合、INDIRECT関数に指定する参照文字列を工夫する必要があります。
通常は
=INDIRECT(テーブル名)
のところを
=テーブル名[列名]
とテーブル名に続けて、［］で列名を続ける必要があります。
この場合、テーブル名と列名を同じにしているので、C5セルの入力規則には
=INDIRECT(C4&”[“&C4&”]”)
と入力しています。
本筋に戻ります。
そして、金額（C6セル）には、各テーブルのオプションの金額を、INDIRECT関数とVLOOKUP関数を組み合わせて動的に取得しています。
やり方
VLOOKUP関数の構文は次のようになっています。
=VLOOKUP(①検索値,②範囲,③列番号,④[検索方法])
今回の場合、C4セルで決めた商品に合わせて、VLOOKUP関数の「②範囲」を動的に変更する必要がありますね。
ここに名前の付けられたテーブルとINDIRECT関数を使います。
C6セルに入る数式は次です。
=VLOOKUP(C5, INDIRECT(C4), 2, 0)
①検索値は、C5で選択されたオプションですね。
②範囲に、「INDIRECT(C4)」を入力します。このようにすることで、C4で選択された商品名が名前についているテーブルが②範囲（テーブル）に指定されることになります。
つまり、C4でハンバーガーが選ばれている場合は、「INDIRECT(“ハンバーガー”)」と入ってるのと同じになります。
=VLOOKUP(C5, ハンバーガー, 2, 0)
C4でカレーが選ばれている場合は、
=VLOOKUP(C5, カレー, 2, 0)
と同じことですね。
③列番号は、各テーブルの表示させたい列なので、左から2番目の金額列です。
このようにすることで、プルダウンの連動に合わせて、VLOOKUP関数の検索範囲を動的に変更することが出来ちゃいます！
選択範囲に合わせて、検索範囲を連動するというような機会は結構あると思いますので、そんなときに使えますね^_^
まとめ
VLOOKUP関数の範囲指定を動的に処理する方法を紹介しました！
ちなみに、サラッと流していますが、VLOOKUPの関数やSUMIF関数など、引数に範囲を指定する関数は、範囲にテーブルを指定することが可能です。これ、初めて知ったときは結構感動ものでした。同じように感動してくれる方がいたら嬉しいです＼(^o^)／
コメント
はい！
ありがとうございます。!(^^)!
商品テーブルが別シートにあってもできました！
本当に、本当に、感謝しかありません。
ありがとうございました！！！！！！！
喜んで頂いて何よりです！！(^o^)
まだ、継続的に記事投稿してますのでご贔屓にお願いします(笑)
出来ました！！！！！
ありがとうございました！！！！！！！
めちゃくちゃ嬉しいです。
本当にこれが最後の質問です。
商品テーブルが別シートにあってもやり方は同じでよね？
良かったです！！！！！＼(^o^)／
私も嬉しいです！
商品テーブルが別のシートにある場合もやり方は同じです(^o^)
通常は同じシートには作らないと思います(^^)
テーブルの命名は、同じワークブックで一意の値になりますので
入力規則上の数式も全く同じです！
ただし、シート複製などで別のシートに商品テーブルや商品ごとのテーブルを動かした場合、テーブル名が重複しないように勝手に変わってるので、そのあたりご注意くださいm(_ _)m
ワークブック上にどんな名前が付けられているかは、「数式」タブの「名前の管理」で一覧が表示出来ますのでそこでチェックすると便利です！！
本当にありがとうございます。
ひらちんさんのおかげで、意味はよく伝わります。
ただ、列にテーブルと同じ名前を付けようとすると、
この名前は既に存在します。名前は一意である必要があります。
というエラーメッセージが出てしまいます。
ありがとうございますm(_ _)m
列に名前は（オブジェクトの名前）は付けなくて大丈夫です。
D9セルにテーブル名と同じ文字列の値が入ってればオッケーですm(_ _)m
分かりにくくてすみません(>_<)
補足ですm(_ _)m
D9:E14セルをテーブルに変換した時点で、エクセルはD9とE9がこの列の名前だと認識しますm(_ _)m
なので、名前を付ける（オブジェクトの）のはテーブルに対してだけでオッケーです！
最後に一つ質問させてください。
テーブル名と列名を同じにしている、とありますが、ハンバーガーテーブルだとして、商品名の列(D10:D14)もハンバーガーという名前を付けるという意味ですか？
でも、同じ名前は使えないですよね？
「テーブル名と列名を同じにしている」については、
「D9:E14」範囲をテーブルにして、そのテーブルの名前をハンバーガーにしています。
そして、列名（D4セルの値）もハンバーガーにしていますよと言う意味になりますm(_ _)m
もし、C4セルに「ハンバーガー」が選ばれていたとすると
「=INDIRECT(C4&”[“&C4&”]”)」は
実際は、「=INDIRECT(ハンバーガー[ハンバーガー])」となります。
最初のハンバーガーは、テーブル名を指すハンバーガー
[]内のハンバーガーは、ハンバーガーテーブルのハンバーガー列ということになります。
例えば、=INDIRECT(C4&”[“&C4&”]”)を=INDIRECT(C4&”[金額]”)と修正すると
C5セルのプルダウンの選択項目が、各テーブルの金額の列になります。
ややこしいですが、伝わりましたでしょうか？(;_;)
返信ありがとうございます。
「データの入力規則」からやってるのですが、、、
色々なサイトを検索しても、ひらちんさんの様に
=テーブル名[列名]
とテーブル名に続けて、［］で列名を続ける必要があります。
この場合、テーブル名と列名を同じにしているので、C5セルの入力規則には
=INDIRECT(C4&”[“&C4&”]”)
の、説明しているサイトってないものですね、、、
ご返信ありがとうございますm(_ _)m
先程返信した内容は、後の書き込みを読む前にしてしまったので、検討違いだったなと思いまして削除してしまいましたm(_ _)m
その後、色々やってみたのですが、一つ情報頂けた内容のエラーが再現出来ました。
「=INDIRECT(C4&”[“&C4&”]”)」の数式ですが、「”」（ダブルクォーテーション）や＆の場所や位置が誤っていると同様のエラーが発生します。
=INDIRECT( C4 & “[” & C4 & “]”)
一度ご確認頂けますか？？
「’」シングルクォーテーション２つでは無く、「”」ダブルクォーテーションですのでご注意ください。
今参考にして作らせていただいてるのが仕入管理表です。
会社名
商品名
価格
のような感じで、ひらちんさんの
商品選択
オプション選択
金額
に合わせて作らせていただいてます。
会社名のプルダウンリスト、価格のvlookup関数は、上手くいったのですが、商品名のindirect関数が上手くいきません。
辛うじて、商品名のプルダウンリストに商品名と価格が同時に出る事は出来ました。
はじめまして菅と申します。
初心者の自分にもとても分かり易い解説で感謝しております。
ただ、下記の所でエラーが出てしまいます。
テーブル名[列名]
とテーブル名に続けて、［］で列名を続ける必要があります。
この場合、テーブル名と列名を同じにしているので、C5セルの入力規則には
=INDIRECT(C4&”[“&C4&”]”)
対処方法を教えていただけると助かります。
こんにちは！コメントありがとうございますm(_ _)m
エラーが出るとのことですが、どのようなエラーが出ますか？？
直ぐに返信いただいたのに、申し訳ありません。
エラーメッセージは下記の通りです。
この数式には問題があります。
数式を入力していない場合は次の点に注意します。
最初の文字に等号(=)やマイナス記号(-)を使用してる場合は、数式として認識されます。
です。