Need help with formula to lookup number based on multiple criteria

[scottgorilla]

What I'm am trying to accomplish is creating a lookup formula that will give me a result based on criteria from two columns in a range of cells. The criteria in the columns is a set group of numbers but the lookup values will vary.


For instance I have inserted the lookup table and below I will enter the correct results for a couple of scenarios.

SIZE LENGTH MAX WEIGHT
70 24 100000.000
70 36 3628.000
70 48 2712.950
70 60 2162.080
70 72 1397.550
70 84 865.270
70 96 564.440
70 108 380.870
70 120 261.830
70 132 180.680
70 144 122.940
70 156 80.832
70 168 47.780
70 180 22.210
70 192 1.550
70 204 0.000
70 216 0.000
100 24 1000000.000
100 36 81271.71
100 48 34273.93
100 60 17534.67
100 72 10133.09
100 84 6366.37
100 96 4249.74
100 108 2969.17
100 120 2148.71
100 132 1598.32
100 144 1214.89
100 156 939.16
100 168 735.42
100 180 581.28
100 192 462.22
100 204 368.51
100 216 293.52

If Length is equal 71.5 and the weight is equal to 1250.00 the result would be 70

If the length is equal to 71.5 and the weight is 1450.00 the result would be 100

if the length is equal to 180 and the weight is equal 21.00 the result would be 70 if the weight was greater than 22.21 the result would be 100

I hope this is enough info for you to understand my problem.


Thank You,


Scottgorilla

 

[scottgorilla]

I found the formula to give me the proper result if anyone is interested.

=INDEX($A$3:$A$36,MATCH(1,(length<=$B$3:$B$36)*(weight<=$C$3:$C$36),0)

Followed by C+S+E