lookup a number based on multiple criteria

[scottgorilla]

I have a list with two possible numbers but each number has several criteria

A1 B1 C1
DIAMETER LENGTH MAX WEIGHT
70 48 368
70 60 245
70 72 145
100 48 700
100 60 550
100 72 450


I am looking for the result from column a based on both the length and
weight which is located on another worksheet say cells f15 for the length and
k15 for the weight. I have tried this formula but it always returned the
same resulkt of 70 which is incorrect.
INDEX($AP$3:$AP$36,MATCH(1,('ROLL CUT
SHEET'!M27<=FORMULAS!$AQ$3:$AQ$36)*(FORMULAS!AG15>=$AR$3:$AR$36),0))) using
ctrl+shift+enter.


Thank you so much for assistance in advance.

 

[Mike H]

Hi,

I'm afraid you lost me about which sheet your data/formula are on so this
assumes everything is on the same sheet

SUMPRODUCT((B2:B7=D1)*(C2:C7=E1)*(A2:A7))

Where
D1= Length
E1= Max weight

Mike

 

[scottgorilla]

Mike,

Thank You for your response.

I have all of the criteria on one sheet now and when I run the formula it is
giving me an answer of 0. Perhaps I didn't let you know that the D1 (length)
may not be a whole number. for instance in the example I ran the length is
71.50 but the weight is 250 lbs so the solution should be 100 but it returned
0