ある条件を満たした最も近い数値を求める配列数式

質問

ある条件を満たした最も近い数値を求める配列数式について質問します。

A列には，C列の数値と比較する元になる数値が並んでいます。
B列には，D列の数値と比較する元になる数値が並んでいます。
C列には，A列の数値と比較する対象となる数値が並んでいます。
D列には，B列の数値と比較する対象となる数値が並んでいます。
E列には，計算式が入ります。

すなわち，例えば，E20には，
C$1:C20の範囲内の数値で，
右となりにあるD$1:D20の数値が0でなく，かつ，B$1:B20の数値以下である数値の中で，
最もA20の数値に近い数値を求めます。

A 　B 　 C　　 D　 E
1 100 　10 89 　　0　　　0
2 101 8 90　　　　10　　　　0
3 102 11 92　　　　11　　　92　　
4 99 7 77 　　　　　0　　　0
5 87 11 68　　　　12　　　92
6 105 　 14 100　　　　　7　　　100
7 102 9 78　　　　　8　　　100
8 88 15 102　　　　　0　　　　90
9 98 14 99　　　　10　　　　99
10 150 13 78　　　　　9　　　100
11 103　 8 79　　　　　0　　　100
12 87 9 68　　　　　8　　　78
13　　　77　　　10　　　　70　　　　10　　　78
14　　　89　　　12　　　　71　　　　　5　　　90
15　　　65　　　14　　　102　　　　　3　　　68
16　　　88　　　13　　　105　　　　　0　　　90
17　　　123　　15　　　　98　　　　　8　　　102
18　　　145　　17　　　　99　　　　10　　　102
19　　　85　　　　8　　　100　　　　　0　　　90
20　　　84　　　　9　　　　87　　　　5　　　　78

一定の条件を満たした配列の合計を計算する関数として，SUMPRODUCT()という便利な関数がありますが，
この関数に相当する関数がないので困っています。
MIN関数が配列数式に対応していて，かつ，MIN関数をうまく利用して，最も近い数値を求めることがでるき方法があれば，私の希望が解決できそうなのです。
以上，よろしくアドバイス願います。

回答No.1

mshr1962 · ベストアンサー

答える回答者の為の補足の要求です。
Excelのバージョンは2007以降ですか？2007以前ですか？
2007から追加された新関数もあるので対応が微妙に変わる場合もあります。

あと表示はC列(1行目から数式の行)の数値でしょうか？
E1,E2,E4で0が表示されてますが、これはどういう条件ですか？
結果として条件に合う数値が複数あった場合はどちらを表示するのか考えてますか？

補足

早速の回答ありがとうございます。
まことに申し訳ありませんが，私が投稿するさいに投稿内容を十分に確認しなかったのが原因で，
数列が崩れてしまっていました。
大幅に崩れているので，ここで訂正するのは困難ですので，別に投稿しますので，そちらに投稿に回答願います。
　ちなみに，エクセルのバージョンは，2010です。
　また，条件を満たす数値が複数あった場合には，数値の小さい数値を求めます。
　以上よろしくお願いします。
　また，この投稿は，終了させることにします。