【エクセル】重複条件にて表から参照する

質問

名前 皮 サイズ 値段
りんご 皮つき 20 100
りんご 皮つき 35 200
りんご　皮つき 50 350
りんご 皮なし 20 120
りんご 皮なし 35 240
りんご　皮なし 50 400
みかん 皮つき 20 150
みかん 皮つき 40 400
みかん 皮なし 20 170
みかん 皮なし 40 450

このような表があり、DGET関数を使い重複条件にて参照していましたが、
サイズが指定してある数値の以上以下の場合にはそれに対応した値段を参照したく
例えば「りんご　皮つき　19」だとすると100、「みかん　皮なし　45」だと450という感じで参照するにはどうしたら良いでしょうか。
よろしくおねがいします。

回答No.4

KURUMITO

お示しの表はデータベースとして例えばシート2にあるとします。
シート2のA2セルから下方には果物名が、B2セルから下方には皮つき、皮なしなどがC2セルから下方にはサイズが、D2セルから下方には値段が入力されているとします。
なお、お示しのようにデータが整然と下方に順序良く並んでいるとします。
そこでシート2のF列は作業列としてF2セルには次の式を入力して下方にオートフィルドラッグコピーします。

=IF(A2="","",A2&B2)

そこでお求めのデータですが例えばシート１のA2セルから下方には果物名が、B2セルから下方には皮つき、皮なしなどがC2セルから下方にはサイズが、入力されて行くとします。
D2セルから下方にサイズに合った値段を表示させるとしてD2セルには次の式を入力して下方にオートフィルドラッグコピーします。

=IF(C2="","",IF(C2<=INDEX(Sheet2!C:C,MATCH(A2&B2,Sheet2!F:F,0)),INDEX(Sheet2!D:D,MATCH(A2&B2,Sheet2!F:F,0)),IF(C2>=INDEX(Sheet2!C:C,MATCH(A2&B2,Sheet2!F:F,0)+COUNTIF(Sheet2!F:F,A2&B2)-1),INDEX(Sheet2!D:D,MATCH(A2&B2,Sheet2!F:F,0)+COUNTIF(Sheet2!F:F,A2&B2)-1),IF(ABS(C2-INDEX(INDEX(Sheet2!C:C,MATCH(A2&B2,Sheet2!F:F,0)):INDEX(Sheet2!C:C,MATCH(A2&B2,Sheet2!F:F,0)+COUNTIF(Sheet2!F:F,A2&B2)-1),MATCH(C2,INDEX(Sheet2!C:C,MATCH(A2&B2,Sheet2!F:F,0)):INDEX(Sheet2!C:C,MATCH(A2&B2,Sheet2!F:F,0)+COUNTIF(Sheet2!F:F,A2&B2)-1),1)))>=ABS(C2-INDEX(INDEX(Sheet2!C:C,MATCH(A2&B2,Sheet2!F:F,0)):INDEX(Sheet2!C:C,MATCH(A2&B2,Sheet2!F:F,0)+COUNTIF(Sheet2!F:F,A2&B2)-1),MATCH(C2,INDEX(Sheet2!C:C,MATCH(A2&B2,Sheet2!F:F,0)):INDEX(Sheet2!C:C,MATCH(A2&B2,Sheet2!F:F,0)+COUNTIF(Sheet2!F:F,A2&B2)-1),1)+1)),INDEX(Sheet2!D:D,MATCH(C2,INDEX(Sheet2!C:C,MATCH(A2&B2,Sheet2!F:F,0)):INDEX(Sheet2!C:C,MATCH(A2&B2,Sheet2!F:F,0)+COUNTIF(Sheet2!F:F,A2&B2)-1),1)+1+MATCH(A2&B2,Sheet2!F:F,0)-1),INDEX(Sheet2!D:D,MATCH(C2,INDEX(Sheet2!C:C,MATCH(A2&B2,Sheet2!F:F,0)):INDEX(Sheet2!C:C,MATCH(A2&B2,Sheet2!F:F,0)+COUNTIF(Sheet2!F:F,A2&B2)-1),1)+MATCH(A2&B2,Sheet2!F:F,0)-1)))))

これでD列にはお望みのデータが表示されるでしょう。

回答No.3

noname#204879

面倒だけど、最終の計算式を簡単にするために、私なら次のようにします。

添付図参照（小さ過ぎて見えなければ、私の回答は無視してください）
貴方が示す「このような表」が左端です。
それを右端の上段の表に書き替えます。
その手順の説明は省略するけど、真ん中の表はその書き替え途中のものです。
緑色のサイズ値はそのセルの上下の値の平均値です。

右端の下段のセル N16 には次の配列数式を入力しています。
{=VLOOKUP(M16,IF((K$2:K$11=K16)*(L$2:L$11=L16),M$2:N$11,""),2)}

回答No.2

mt2008

こんなのはどうでしょう。
添付の図を見てください（ちゃんとみえるかな）。
F列とL:O列を作業列として使います（あとで非表示にしてください）。

L2には　=">="&I2
M2には　=G2
N2には　=H2
O2には　="<="&I2

L5には　=DMIN(A1:D11,L4,L1:N2)　←指定サイズ以上で最小のサイズ
M5には　=DMAX(A1:D11,M4,M1:O2)　←指定サイズ以下で最大のサイズ

F2には　=IF(AVERAGE(L5:M5)>I2,MIN(L5:M5),MAX(L5:M5))　←一番近いサイズ

最後にJ2に　=DGET(A1:D11,J1,F1:H2)

回答No.1

mt2008

『それに対応した』がよくわかりません。
「りんご　皮つき　30」の様にサイズ20と40の中間の時はどうするんでしょう？

補足

説明不足で申し訳ありません。
中間の場合は最寄りのサイズで、最寄りが2種類ある30の時は大きい方の40にしたいです。
宜しくお願いします。