LOOKUP関数にて切り上げた数値を返したい

質問

エクセルについての質問です。

　　　A　　　 B　 　 　C　　　　　　D　
　検査値　結果　対応範囲　検査範囲
1　　4.7　　 3.0　 　 3.0　　　　　 4.6
2　　5.1　　 3.2　　　 3.2　　　　　 5.0
3　　6.2　　 4.0　　　 4.0　　 　　　6.1
4　　6.8　　 4.5　　　 4.5　　　　　 6.7

上のような表があります。
B1の数式は

=IF(ISERROR(LOOKUP($B1,$D$1:$D$4,C$1:C$4)),0,LOOKUP($B1,$D$1:$D$4,C$1:C$4))
で現在入力しています。

現状だと、検査値が4.9の場合、3.0を返してしまいます。

1.検査値に最も近い検査範囲(中間の場合は大きい方)を参照して結果を出したい場合の数式

2.検査値が検査範囲の数値を1ポイントでも超える場合は次の数値で参照する場合の数式

※いずれも検査値が4.6を下回る場合はゼロとする

を教えて頂けないでしょうか。
いろいろ調べてみたのですが、わかりませんでした。

質問文も何かとわかりづらいかと思いますが、何卒よろしくお願い致します。

回答No.2

KURUMITO · ベストアンサー

1については

=IF(A2<D$2,"",IF(ABS(INDEX(D:D,MATCH(A2,D:D,1))-A2)<ABS(INDEX(D:D,MATCH(A2,D:D,1)+1)-A2),INDEX(C:C,MATCH(A2,D:D,1)),INDEX(C:C,MATCH(A2,D:D,1)+1)))

2については

=IF(A2<D$2,"",IF(A2>MAX(D:D),INDEX(C:C,MATCH(MAX(D:D),D:D,0)),IF(A2>INDEX(D:D,MATCH(A2,D:D,1)),INDEX(C:C,MATCH(A2,D:D,1)+1),INDEX(C:C,MATCH(A2,D:D,1)))))

ただしD列の最大値よりも大きなA列の数値の場合にはD列で最大値となっているC列のデータを表示するようになっています。

お礼

両方にご回答いただきありがとうございます。

急ぎでしたので、一旦コピーして使わせていただきました。
構造や考え方がまだ理解できていませんが、
頂いた式をもとに後程勉強させていただきます。

お忙しいところ本当にどうもありがとうございます。

※1.2.ともにご回答頂いたというところでこちらの回答をベストとさせていただきました。お詫びではないですが回答評価はお二方ともに押させていただきました。
どうもありがとうございました。

回答No.1

tom04

こんにちは！

＞1.検査値に最も近い検査範囲(中間の場合は大きい方)を参照して結果を出したい場合の数式

に対応するために・・・
あまりスマートな方法ではないと思いますが、
↓の画像のように作業用の列を1列設けます。
作業列F2は単に
=E2

F3を
=AVERAGE(E2:E3)
として、F5セルまでオートフィルでコピー！

最後にB2セルに（せっかく数式をお考えなので、それをそのまま使っています）

=IF(ISERROR(LOOKUP(A2,$E$2:$E$5,$D$2:$D$5)),0,MAX(LOOKUP(A2,$E$2:$E$5,$D$2:$D$5),LOOKUP(A2,$F$2:$F$5,$D$2:$D$5)))

という数式を入れオートフィルで下へコピーではどうでしょうか？

※　検証していませんので、ご希望通りの表示でなかったらごめんなさいね。m(_ _)m

お礼

さっそくのご回答ありがとうございます。

1.検査値に最も近い検査範囲～
に関しては、完璧にできました！

急ぎだったので大変助かりました、どうもありがとうございます。