エクセル 数値範囲を含む複数条件検索

sheet1には、下記のような表があります。

　　　A列　　B列　　C列　　D列
1　カテゴリ　min max 値引き額
2　あ 　　　　1　　　50 1
3　あ 　　　　51 　　80 1.5
4　あ　　 　81 　　100　2
5　い　　 　1 　　50 1.4
6　い　　 　51 　　60 1.5
7　い 　　　　61 　　100 3
8　う 　　　　5　　　30 1
9　う 　　　31 　100 2

sheet２には下記のような表があります。

　　　A列　　B列　　C列
1　カテゴリ　単価 値引き額
2　う 　　　　８０　　
3　あ 　　　　９８
4　あ　　 　４０　　
5　い　　　　５５

値引き額をsheet１から検索して結果を返したいのですが、
いい関数はないでしょうか。
例えば、sheet２、2行目の場合は、sheet１にある、
カテゴリ「う」に該当するもので、単価が80円内にある
範囲、この場合の値引き額は２になります。

共有の都合上、マクロ・VBAは使用できないため、関数もしくは
なんらか列を挿入するなりは可能なので、いい方法がありましたら
ご教授頂ければと思います。

PCはWindows７　エクセルは2010　を使用しています。

よろしくお願いします！

No.4 回答者： CoalTar 回答日時： 2016/01/15 12:31

No.3です

カテゴリ数が3つ以上ないとLOOKUP関数はまずいのでVLOOKUP関数に修正
=VLOOKUP(B14,INDEX($B$2:$B$9,MATCH($A14,$A$2:$A$9,0)):INDEX($D$2:$D$9,MATCH($A14,$A$2:$A$9,0)+COUNTIF($A$2:$A$9,$A14)-1),3)

この回答へのお礼

ありがとうございます！
こちらの方法も試してみます。
いろいろとやり方次第ですね～

お礼日時：2016/01/15 13:03

No.3 回答者： CoalTar 回答日時： 2016/01/15 12:27

カテゴリはまとまっていること、

項目のmaxは使用しない(エラー処理などしない)
C14セルに
=LOOKUP(B14,INDEX($B$2:$B$9,MATCH($A14,$A$2:$A$9,0)):INDEX($D$2:$D$9,MATCH($A14,$A$2:$A$9,0)+COUNTIF($A$2:$A$9,$A14)-1))

No.2 回答者： mike_g 回答日時： 2016/01/15 11:21

Sheet1 において、

１．範囲 A1:D9 を選択
２．[数式]→[定義された名前]→[選択範囲から作成]
３．“上端行”だけにチェックを残して、[OK]をツン
Sheet2 において、
４．次の配列数式を入力したセル C2 を下方にズズーッとオートフィル
＿＿ =INDEX(値引き額,MAX((A2=カテゴリ)*(B2>=min)*(B2<=max)*ROW(カテゴリ))-1)

【備考】ステップ１の「D9」はお好みで「D100」「D1000」「D1000000」でも構わないけど、必要十分なだけの行数にするのが常識です。





さもありなん_goo

この回答へのお礼

ありがとうございます！
こちらの方法も試してみます。

お礼日時：2016/01/15 13:03

No.1 ベストアンサー 回答者： gyouda1114 回答日時： 2016/01/15 09:14

INDIRECT関数をつかってVLOOKUP関数で

１．Sheet１の表に｢あ｣、｢い｣、｢う｣と名前を定義する
｢あ｣　=Sheet1!$B$2:$D$4
｢い｣　=Sheet1!$B$5:$D$7
｢う｣　=Sheet1!$B$8:$D$9
指定したセル範囲に名前をつける
http://span.jp/office2010_manual/excel2010/cell/ …
２．Sheet2のC2に
=VLOOKUP(B2,INDIRECT(A2),3,TRUE)
下方にオートフィル

■INDIRECT関数
http://www.kenzo30.com/ex_kisotyu/ex_ks_tyukyu9_ …

この回答へのお礼

ありがとうございます！
出来ました！

お礼日時：2016/01/15 13:02