Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup a number based on multiple criteria
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup a number based on multiple criteria
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" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup a number based on multiple criteria
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 "Mike H" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup and Display Multiple Unique values based on criteria | Excel Worksheet Functions | |||
Lookup and Display Multiple Unique values based on criteria | Excel Worksheet Functions | |||
Lookup based on multiple criteria | New Users to Excel | |||
Index/Match - Lookup based on multiple column criteria | Excel Worksheet Functions | |||
Lookup based on two criteria in 1 row | Excel Discussion (Misc queries) |