Lookup problem

[Guest]

I have an array of values that I need to lookup a value in. The interesting
part is that the lookup values have to search citeria that are between
min-max values. To make it more interesting there are multiple criteria in
the row lookup. Basically I have a list of rectangular dimensions, length,
width and thickness. that are used to lookup a yield percentage. The
thickness and width dimensions provide the looup array to find a row by
finding where the lookup value fits in the min-max dimensions. This is also
true about the column criteria, each column must be found by determining
finding the correct range range between min-max values. Another interesting
part of this is that there are repetitive values in both the thickness and
width lookup arrays although they are in different combinations. I paste an
example of this here to illustrate.

Length
Thickness Width 306 801 1800 min
Min Max Min Max 800 1799 2399 max
1 19 1 39 50.0% 44.6% 39.9%
1 19 40 93 26.3% 23.1% 20.4%
1 19 94 102 26.0% 22.9% 20.2%
1 19 103 199 25.7% 22.7% 20.0%
1 19 200 210 25.4% 22.4% 19.8%
1 19 211 299 25.2% 22.2% 19.6%
20 39 40 93 17.3% 15.5% 13.4%
20 39 94 102 17.1% 15.3% 13.3%
20 39 103 199 17.0% 15.1% 13.2%

I have been trying to do this with an Index/match combination but I can''t
get the match functions to cooperate and work together to find the multiple
criteria.
Any help is much appreciated.
Thanks,
RDW

 

[just_jon]

Guessing at:

Thickness Width Length
Min Max Min Max 0 801 1800
1 19 1 39 50.00% 44.60% 39.90%
1 19 40 93 26.30% 23.10% 20.40%
1 19 94 102 26.00% 22.90% 20.20%
1 19 103 199 25.70% 22.70% 20.00%
1 19 200 210 25.40% 22.40% 19.80%
1 19 211 299 25.20% 22.20% 19.60%
20 39 40 93 17.30% 15.50% 13.40%
20 39 94 102 17.10% 15.30% 13.30%
20 39 103 199 17.00% 15.10% 13.20%

17
109
1800
19.6%

=LOOKUP(2,(1/(A3:A11<=A13)*(B3:B11>=A13)*(C3:C11<=A14)*(D311>=A14)),INDEX(E3:G11,0,MATCH(A15,E2:G2)))

Note that the column headers for the last 3 columns must represent the
minimum acceptable value, not the upper limit.

 

[Herbert Seidenberg]

Shouldn't the answer be 20.00% ?

 

[just_jon]

Absolutely, missed a set of parens --

=LOOKUP(2,(1/((A3:A11<=A13)*(B3:B11>=A13)*(C3:C11<=A14)*(D311>=A14))),INDEX(E3:G11,0,MATCH(A15,E2:G2)))

 

[Guest]

Thanks Just_jon, That worked perfectly. Thanks also to Herbert.