VLOOKUP関数で商品の種類別に異なるシートを参照

Excel 2013 関数技　37回

VLOOKUP関数で商品の種類ごとに異なるシートを検索する

見積書などを作る場合、商品の種類が多いと価格を調べるのも大変です。
今回は、商品コードや商品番号を判断して、異なるシートの商品を検索する場合のVLOOKUP関数の場合を紹介します。
（サンプルファイルは、こちらから 2013関数技37回サンプルデータ）

商品の種類ごとに異なるシートを検索するには

衣料品などは、商品の種類が多く、コードから参照するにしても、複数のコード表がある場合は、どのように、効率よく入力していけばいいのでしょうか？

コードにあるルールを活用する

複数のコード表には、それぞれ名前を定義しておく

名前を定義しておくことで、数式が非常に簡潔でわかりやすくなります。
例えば、Tシャツのコード表を数式で範囲を指定する場合、
Tシャツ!$A$2:$C$15　となりますが、
名前定義で、Tシャツという名前をつけておけば、そのまま範囲が、
Tシャツでいいのです。

コードから、キーを取り出す。
A11セルから、T1308N-1001というコードの検索キーであるTを取り出します。式は、LEFT関数を使って、左から1文字を取り出せば良いです。
=LEFT(A11,1)
キーが先頭の2文字なら、=LEFT(A11,2)
もし、キーが左から2文字目にあるなら、
＝MID(A11,2,1)　というようになります。

取り出したキー文字で何をするかというと、コード表を参照したいので、VLOOKUP 関数を使います。
Tなら、Tシャツのコード表を返す式を考えます。
その前に、分類表を作り名前を定義しておきます。分類という名前です。

VLOOKUP関数の復習です。
どこに、LEFT(A11,1)を入れるかといえば、
検索値です。
そして、範囲は、分類表で、分類という名前を定義してあります。
列番号は、2列目

式は、このようになります。
=VLOOKUP(MID(A12,1,1),分類,2,0)
これで、どういう結果がでるでしょうか？

Tは、Tシャツ、Cはコートと返されました。これではまだ文字列なので、INDIRECT関数でセル範囲の指定に変えます。

今までのコードは、INDIRECT関数の引数である、参照文字列になります。
結果として、名前定義で指定しているセル範囲を指定することになるのです。
INDIRECT(VLOOKUP(MID(A11,1,1),分類,2,0))

それで、このINDIRECT関数で返されたセル範囲は、再びVLOOKUP関数の引数になります。
VLOOKUP(検索値, 範囲, 列番号, [検索の型])
検索の型は省略しています。

もう、これで、90％完成です。あとは、単価のセルに式をコピーするだけです。注意することは、参照セルの列だけ絶対参照にしておくことです。

すると、横にも下にもコピーすることができます。