|
Look up one number based on mulitple 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. |
Look up one number based on mulitple criteria!!!!
I would think that the table would be on a single worksheet.
=INDEX($AP$3:$AP$36, MATCH(1,('ROLL CUT SHEET'!M27<=FORMULAS!$AQ$3:$AQ$36) *(FORMULAS!AG15=$AR$3:$AR$36),0))) And AQ3:AQ36 are on the Formulas worksheet, but AR3:Ar36 and AP3:Ap36 are on the sheet with the formula. If that doesn't help, you may want to rewrite your question. Your sample showed columns A:C, but that's not in your formula. And you'll want to share what's in the values (M27, AG15), too. In fact, if I were you, I'd create a small table in a test workbook/worksheet and try to get it to work there. 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. -- Dave Peterson |
Look up one number based on mulitple criteria!!!!
I'm sorry for the confusion Dave but actually I have all of the information
and formula on the same sheet now and it still returns the wrong answer..... =IF(OR(Master!C18=ALUM.050DADE,Master!C18=ALUM.050 PALM,Master!C18=STEEL24ga,Master!C18=CLEARPANELS,M aster!C18=IMPBAHAMA,Master!C18=IMPCOLONIAL,Master! C18=DECOBAHAMA,Master!C18=DECOCOLONIAL,Master!C18= ACC6_8,Master!C18=ACC6_8BL,Master!C18=ACC6_8HR,Mas ter!C18=ACC6_8HRB,Master!C18=BULLDOG,Master!C18="" )," ",INDEX($AP$3:$AP$36,MATCH(1,(AL5<=FORMULAS!$AQ$3: $AQ$36)*(FORMULAS!AG5=$AR$3:$AR$36),0))) AL5= 139.50 here is an actual copy of the table 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 100000.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 "Dave Peterson" wrote: I would think that the table would be on a single worksheet. =INDEX($AP$3:$AP$36, MATCH(1,('ROLL CUT SHEET'!M27<=FORMULAS!$AQ$3:$AQ$36) *(FORMULAS!AG15=$AR$3:$AR$36),0))) And AQ3:AQ36 are on the Formulas worksheet, but AR3:Ar36 and AP3:Ap36 are on the sheet with the formula. If that doesn't help, you may want to rewrite your question. Your sample showed columns A:C, but that's not in your formula. And you'll want to share what's in the values (M27, AG15), too. In fact, if I were you, I'd create a small table in a test workbook/worksheet and try to get it to work there. 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. -- Dave Peterson |
Look up one number based on mulitple criteria!!!!
and AG5=336.720
"scottgorilla" wrote: I'm sorry for the confusion Dave but actually I have all of the information and formula on the same sheet now and it still returns the wrong answer..... =IF(OR(Master!C18=ALUM.050DADE,Master!C18=ALUM.050 PALM,Master!C18=STEEL24ga,Master!C18=CLEARPANELS,M aster!C18=IMPBAHAMA,Master!C18=IMPCOLONIAL,Master! C18=DECOBAHAMA,Master!C18=DECOCOLONIAL,Master!C18= ACC6_8,Master!C18=ACC6_8BL,Master!C18=ACC6_8HR,Mas ter!C18=ACC6_8HRB,Master!C18=BULLDOG,Master!C18="" )," ",INDEX($AP$3:$AP$36,MATCH(1,(AL5<=FORMULAS!$AQ$3: $AQ$36)*(FORMULAS!AG5=$AR$3:$AR$36),0))) AL5= 139.50 here is an actual copy of the table 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 100000.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 "Dave Peterson" wrote: I would think that the table would be on a single worksheet. =INDEX($AP$3:$AP$36, MATCH(1,('ROLL CUT SHEET'!M27<=FORMULAS!$AQ$3:$AQ$36) *(FORMULAS!AG15=$AR$3:$AR$36),0))) And AQ3:AQ36 are on the Formulas worksheet, but AR3:Ar36 and AP3:Ap36 are on the sheet with the formula. If that doesn't help, you may want to rewrite your question. Your sample showed columns A:C, but that's not in your formula. And you'll want to share what's in the values (M27, AG15), too. In fact, if I were you, I'd create a small table in a test workbook/worksheet and try to get it to work there. 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. -- Dave Peterson |
Look up one number based on mulitple criteria!!!!
First, the strings in your formula have to be surrounded by double quotes:
=IF(OR(Master!C18="ALUM.050DADE",Master!C18="ALUM. 050PALM", Second, it still looks like AQ3:AQ36 and AR3:AR36 are on different sheets. Third, if all the data and formulas are on the same sheet, then the formula doesn't need the sheet references. Fourth, try this simplified formula: =MATCH(1,(AL5<=$AQ$3:$AQ$36)*(AG5=$AR$3:$AR$36),0 ) (still an array formula) This returns 11. That's 11 rows into the table--or this line of data. 70 144 122.94 The first time that 139.5 is less than or equal to the values in AQ3:AQ36 and at the same time 336.72 is greater than or equal to the values in AR3:AR36. So that 70 is the correct result for your formula. ===== But I think you've got other problems. In your table, you have: 70 24 100000.000 .... 100 24 100000.000 And there's never going to be a formula that uses =index(match()) that will return 100. The first row found that matches those identical conditions will always be the 70. I'm not sure what you're doing, but maybe you could use two tables (one for 70 and one for 100) or add an extra indicator to know what "group" you want to inspect. But it sure looks like you're deciding to bring back 70 or 100. I don't understand what you really want. scottgorilla wrote: I'm sorry for the confusion Dave but actually I have all of the information and formula on the same sheet now and it still returns the wrong answer..... =IF(OR(Master!C18=ALUM.050DADE,Master!C18=ALUM.050 PALM,Master!C18=STEEL24ga,Master!C18=CLEARPANELS,M aster!C18=IMPBAHAMA,Master!C18=IMPCOLONIAL,Master! C18=DECOBAHAMA,Master!C18=DECOCOLONIAL,Master!C18= ACC6_8,Master!C18=ACC6_8BL,Master!C18=ACC6_8HR,Mas ter!C18=ACC6_8HRB,Master!C18=BULLDOG,Master!C18="" )," ",INDEX($AP$3:$AP$36,MATCH(1,(AL5<=FORMULAS!$AQ$3: $AQ$36)*(FORMULAS!AG5=$AR$3:$AR$36),0))) AL5= 139.50 here is an actual copy of the table 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 100000.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 "Dave Peterson" wrote: I would think that the table would be on a single worksheet. =INDEX($AP$3:$AP$36, MATCH(1,('ROLL CUT SHEET'!M27<=FORMULAS!$AQ$3:$AQ$36) *(FORMULAS!AG15=$AR$3:$AR$36),0))) And AQ3:AQ36 are on the Formulas worksheet, but AR3:Ar36 and AP3:Ap36 are on the sheet with the formula. If that doesn't help, you may want to rewrite your question. Your sample showed columns A:C, but that's not in your formula. And you'll want to share what's in the values (M27, AG15), too. In fact, if I were you, I'd create a small table in a test workbook/worksheet and try to get it to work there. 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. -- Dave Peterson -- Dave Peterson |
Look up one number based on mulitple criteria!!!!
I'm using Excel 2007 and those are named cell references so no need for the
double quotations and secondly AQ and AR are on the same sheet. What I am trying to do based on criteria that I have set like as follows if length is for argument sake is 72 and weight is less than 1397.550 the result would be 70 if length is 72 and weight greater than 1397.550 the result would be 100 70 and 100 are diameter of a tube I must use to build my product based on weights and span if length is 133 and weight is less than 122.940 then I can use 70 if the length is 133 and weight is greater than 122.940 then it must be 100. I truly appreciate your assistance and apologize that I seem not to be doing a great job explaining what I'm trying to accomplish. "Dave Peterson" wrote: First, the strings in your formula have to be surrounded by double quotes: =IF(OR(Master!C18="ALUM.050DADE",Master!C18="ALUM. 050PALM", Second, it still looks like AQ3:AQ36 and AR3:AR36 are on different sheets. Third, if all the data and formulas are on the same sheet, then the formula doesn't need the sheet references. Fourth, try this simplified formula: =MATCH(1,(AL5<=$AQ$3:$AQ$36)*(AG5=$AR$3:$AR$36),0 ) (still an array formula) This returns 11. That's 11 rows into the table--or this line of data. 70 144 122.94 The first time that 139.5 is less than or equal to the values in AQ3:AQ36 and at the same time 336.72 is greater than or equal to the values in AR3:AR36. So that 70 is the correct result for your formula. ===== But I think you've got other problems. In your table, you have: 70 24 100000.000 .... 100 24 100000.000 And there's never going to be a formula that uses =index(match()) that will return 100. The first row found that matches those identical conditions will always be the 70. I'm not sure what you're doing, but maybe you could use two tables (one for 70 and one for 100) or add an extra indicator to know what "group" you want to inspect. But it sure looks like you're deciding to bring back 70 or 100. I don't understand what you really want. scottgorilla wrote: I'm sorry for the confusion Dave but actually I have all of the information and formula on the same sheet now and it still returns the wrong answer..... =IF(OR(Master!C18=ALUM.050DADE,Master!C18=ALUM.050 PALM,Master!C18=STEEL24ga,Master!C18=CLEARPANELS,M aster!C18=IMPBAHAMA,Master!C18=IMPCOLONIAL,Master! C18=DECOBAHAMA,Master!C18=DECOCOLONIAL,Master!C18= ACC6_8,Master!C18=ACC6_8BL,Master!C18=ACC6_8HR,Mas ter!C18=ACC6_8HRB,Master!C18=BULLDOG,Master!C18="" )," ",INDEX($AP$3:$AP$36,MATCH(1,(AL5<=FORMULAS!$AQ$3: $AQ$36)*(FORMULAS!AG5=$AR$3:$AR$36),0))) AL5= 139.50 here is an actual copy of the table 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 100000.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 "Dave Peterson" wrote: I would think that the table would be on a single worksheet. =INDEX($AP$3:$AP$36, MATCH(1,('ROLL CUT SHEET'!M27<=FORMULAS!$AQ$3:$AQ$36) *(FORMULAS!AG15=$AR$3:$AR$36),0))) And AQ3:AQ36 are on the Formulas worksheet, but AR3:Ar36 and AP3:Ap36 are on the sheet with the formula. If that doesn't help, you may want to rewrite your question. Your sample showed columns A:C, but that's not in your formula. And you'll want to share what's in the values (M27, AG15), too. In fact, if I were you, I'd create a small table in a test workbook/worksheet and try to get it to work there. 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. -- Dave Peterson -- Dave Peterson |
Look up one number based on mulitple criteria!!!!
It sure looks like you should be changing the table.
Maybe... 70 24 100000.000 70 36 3628.000 70 48 2712.950 70 60 2162.080 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 So that any weight greater than or equal to 72 has to return 100. But I'm not sure what happens at 71.99999 Did you try creating the smaller formula to see how that evaluated. scottgorilla wrote: I'm using Excel 2007 and those are named cell references so no need for the double quotations and secondly AQ and AR are on the same sheet. What I am trying to do based on criteria that I have set like as follows if length is for argument sake is 72 and weight is less than 1397.550 the result would be 70 if length is 72 and weight greater than 1397.550 the result would be 100 70 and 100 are diameter of a tube I must use to build my product based on weights and span if length is 133 and weight is less than 122.940 then I can use 70 if the length is 133 and weight is greater than 122.940 then it must be 100. I truly appreciate your assistance and apologize that I seem not to be doing a great job explaining what I'm trying to accomplish. "Dave Peterson" wrote: First, the strings in your formula have to be surrounded by double quotes: =IF(OR(Master!C18="ALUM.050DADE",Master!C18="ALUM. 050PALM", Second, it still looks like AQ3:AQ36 and AR3:AR36 are on different sheets. Third, if all the data and formulas are on the same sheet, then the formula doesn't need the sheet references. Fourth, try this simplified formula: =MATCH(1,(AL5<=$AQ$3:$AQ$36)*(AG5=$AR$3:$AR$36),0 ) (still an array formula) This returns 11. That's 11 rows into the table--or this line of data. 70 144 122.94 The first time that 139.5 is less than or equal to the values in AQ3:AQ36 and at the same time 336.72 is greater than or equal to the values in AR3:AR36. So that 70 is the correct result for your formula. ===== But I think you've got other problems. In your table, you have: 70 24 100000.000 .... 100 24 100000.000 And there's never going to be a formula that uses =index(match()) that will return 100. The first row found that matches those identical conditions will always be the 70. I'm not sure what you're doing, but maybe you could use two tables (one for 70 and one for 100) or add an extra indicator to know what "group" you want to inspect. But it sure looks like you're deciding to bring back 70 or 100. I don't understand what you really want. scottgorilla wrote: I'm sorry for the confusion Dave but actually I have all of the information and formula on the same sheet now and it still returns the wrong answer..... =IF(OR(Master!C18=ALUM.050DADE,Master!C18=ALUM.050 PALM,Master!C18=STEEL24ga,Master!C18=CLEARPANELS,M aster!C18=IMPBAHAMA,Master!C18=IMPCOLONIAL,Master! C18=DECOBAHAMA,Master!C18=DECOCOLONIAL,Master!C18= ACC6_8,Master!C18=ACC6_8BL,Master!C18=ACC6_8HR,Mas ter!C18=ACC6_8HRB,Master!C18=BULLDOG,Master!C18="" )," ",INDEX($AP$3:$AP$36,MATCH(1,(AL5<=FORMULAS!$AQ$3: $AQ$36)*(FORMULAS!AG5=$AR$3:$AR$36),0))) AL5= 139.50 here is an actual copy of the table 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 100000.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 "Dave Peterson" wrote: I would think that the table would be on a single worksheet. =INDEX($AP$3:$AP$36, MATCH(1,('ROLL CUT SHEET'!M27<=FORMULAS!$AQ$3:$AQ$36) *(FORMULAS!AG15=$AR$3:$AR$36),0))) And AQ3:AQ36 are on the Formulas worksheet, but AR3:Ar36 and AP3:Ap36 are on the sheet with the formula. If that doesn't help, you may want to rewrite your question. Your sample showed columns A:C, but that's not in your formula. And you'll want to share what's in the values (M27, AG15), too. In fact, if I were you, I'd create a small table in a test workbook/worksheet and try to get it to work there. 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. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Look up one number based on mulitple criteria!!!!
Dave,
Unfortunately it would be nice to say ok use the 100 on anything wider than 72" but it is not cost prohibitive. Thus the reason for trying to create the formula so anyone whom enters the data in the program would not have to have all the knowledge or have to continually look the data up based on size and weight. Have excel read the data and give the solution. I have tried with smaller formulas but unfortunately I'm still getting the same result. It will not pick the correct answer and I can't look over all the employees to make sure they didn't make a mistake because mistakes cost money "Dave Peterson" wrote: It sure looks like you should be changing the table. Maybe... 70 24 100000.000 70 36 3628.000 70 48 2712.950 70 60 2162.080 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 So that any weight greater than or equal to 72 has to return 100. But I'm not sure what happens at 71.99999 Did you try creating the smaller formula to see how that evaluated. scottgorilla wrote: I'm using Excel 2007 and those are named cell references so no need for the double quotations and secondly AQ and AR are on the same sheet. What I am trying to do based on criteria that I have set like as follows if length is for argument sake is 72 and weight is less than 1397.550 the result would be 70 if length is 72 and weight greater than 1397.550 the result would be 100 70 and 100 are diameter of a tube I must use to build my product based on weights and span if length is 133 and weight is less than 122.940 then I can use 70 if the length is 133 and weight is greater than 122.940 then it must be 100. I truly appreciate your assistance and apologize that I seem not to be doing a great job explaining what I'm trying to accomplish. "Dave Peterson" wrote: First, the strings in your formula have to be surrounded by double quotes: =IF(OR(Master!C18="ALUM.050DADE",Master!C18="ALUM. 050PALM", Second, it still looks like AQ3:AQ36 and AR3:AR36 are on different sheets. Third, if all the data and formulas are on the same sheet, then the formula doesn't need the sheet references. Fourth, try this simplified formula: =MATCH(1,(AL5<=$AQ$3:$AQ$36)*(AG5=$AR$3:$AR$36),0 ) (still an array formula) This returns 11. That's 11 rows into the table--or this line of data. 70 144 122.94 The first time that 139.5 is less than or equal to the values in AQ3:AQ36 and at the same time 336.72 is greater than or equal to the values in AR3:AR36. So that 70 is the correct result for your formula. ===== But I think you've got other problems. In your table, you have: 70 24 100000.000 .... 100 24 100000.000 And there's never going to be a formula that uses =index(match()) that will return 100. The first row found that matches those identical conditions will always be the 70. I'm not sure what you're doing, but maybe you could use two tables (one for 70 and one for 100) or add an extra indicator to know what "group" you want to inspect. But it sure looks like you're deciding to bring back 70 or 100. I don't understand what you really want. scottgorilla wrote: I'm sorry for the confusion Dave but actually I have all of the information and formula on the same sheet now and it still returns the wrong answer..... =IF(OR(Master!C18=ALUM.050DADE,Master!C18=ALUM.050 PALM,Master!C18=STEEL24ga,Master!C18=CLEARPANELS,M aster!C18=IMPBAHAMA,Master!C18=IMPCOLONIAL,Master! C18=DECOBAHAMA,Master!C18=DECOCOLONIAL,Master!C18= ACC6_8,Master!C18=ACC6_8BL,Master!C18=ACC6_8HR,Mas ter!C18=ACC6_8HRB,Master!C18=BULLDOG,Master!C18="" )," ",INDEX($AP$3:$AP$36,MATCH(1,(AL5<=FORMULAS!$AQ$3: $AQ$36)*(FORMULAS!AG5=$AR$3:$AR$36),0))) AL5= 139.50 here is an actual copy of the table 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 100000.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 "Dave Peterson" wrote: I would think that the table would be on a single worksheet. =INDEX($AP$3:$AP$36, MATCH(1,('ROLL CUT SHEET'!M27<=FORMULAS!$AQ$3:$AQ$36) *(FORMULAS!AG15=$AR$3:$AR$36),0))) And AQ3:AQ36 are on the Formulas worksheet, but AR3:Ar36 and AP3:Ap36 are on the sheet with the formula. If that doesn't help, you may want to rewrite your question. Your sample showed columns A:C, but that's not in your formula. And you'll want to share what's in the values (M27, AG15), too. In fact, if I were you, I'd create a small table in a test workbook/worksheet and try to get it to work there. 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. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Look up one number based on mulitple criteria!!!!
If you base the value to be returned on the last two columns, then I don't see a
way for your formula to ever return the 100. I don't have another suggestion. Good luck. scottgorilla wrote: Dave, Unfortunately it would be nice to say ok use the 100 on anything wider than 72" but it is not cost prohibitive. Thus the reason for trying to create the formula so anyone whom enters the data in the program would not have to have all the knowledge or have to continually look the data up based on size and weight. Have excel read the data and give the solution. I have tried with smaller formulas but unfortunately I'm still getting the same result. It will not pick the correct answer and I can't look over all the employees to make sure they didn't make a mistake because mistakes cost money "Dave Peterson" wrote: It sure looks like you should be changing the table. Maybe... 70 24 100000.000 70 36 3628.000 70 48 2712.950 70 60 2162.080 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 So that any weight greater than or equal to 72 has to return 100. But I'm not sure what happens at 71.99999 Did you try creating the smaller formula to see how that evaluated. scottgorilla wrote: I'm using Excel 2007 and those are named cell references so no need for the double quotations and secondly AQ and AR are on the same sheet. What I am trying to do based on criteria that I have set like as follows if length is for argument sake is 72 and weight is less than 1397.550 the result would be 70 if length is 72 and weight greater than 1397.550 the result would be 100 70 and 100 are diameter of a tube I must use to build my product based on weights and span if length is 133 and weight is less than 122.940 then I can use 70 if the length is 133 and weight is greater than 122.940 then it must be 100. I truly appreciate your assistance and apologize that I seem not to be doing a great job explaining what I'm trying to accomplish. "Dave Peterson" wrote: First, the strings in your formula have to be surrounded by double quotes: =IF(OR(Master!C18="ALUM.050DADE",Master!C18="ALUM. 050PALM", Second, it still looks like AQ3:AQ36 and AR3:AR36 are on different sheets. Third, if all the data and formulas are on the same sheet, then the formula doesn't need the sheet references. Fourth, try this simplified formula: =MATCH(1,(AL5<=$AQ$3:$AQ$36)*(AG5=$AR$3:$AR$36),0 ) (still an array formula) This returns 11. That's 11 rows into the table--or this line of data. 70 144 122.94 The first time that 139.5 is less than or equal to the values in AQ3:AQ36 and at the same time 336.72 is greater than or equal to the values in AR3:AR36. So that 70 is the correct result for your formula. ===== But I think you've got other problems. In your table, you have: 70 24 100000.000 .... 100 24 100000.000 And there's never going to be a formula that uses =index(match()) that will return 100. The first row found that matches those identical conditions will always be the 70. I'm not sure what you're doing, but maybe you could use two tables (one for 70 and one for 100) or add an extra indicator to know what "group" you want to inspect. But it sure looks like you're deciding to bring back 70 or 100. I don't understand what you really want. scottgorilla wrote: I'm sorry for the confusion Dave but actually I have all of the information and formula on the same sheet now and it still returns the wrong answer..... =IF(OR(Master!C18=ALUM.050DADE,Master!C18=ALUM.050 PALM,Master!C18=STEEL24ga,Master!C18=CLEARPANELS,M aster!C18=IMPBAHAMA,Master!C18=IMPCOLONIAL,Master! C18=DECOBAHAMA,Master!C18=DECOCOLONIAL,Master!C18= ACC6_8,Master!C18=ACC6_8BL,Master!C18=ACC6_8HR,Mas ter!C18=ACC6_8HRB,Master!C18=BULLDOG,Master!C18="" )," ",INDEX($AP$3:$AP$36,MATCH(1,(AL5<=FORMULAS!$AQ$3: $AQ$36)*(FORMULAS!AG5=$AR$3:$AR$36),0))) AL5= 139.50 here is an actual copy of the table 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 100000.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 "Dave Peterson" wrote: I would think that the table would be on a single worksheet. =INDEX($AP$3:$AP$36, MATCH(1,('ROLL CUT SHEET'!M27<=FORMULAS!$AQ$3:$AQ$36) *(FORMULAS!AG15=$AR$3:$AR$36),0))) And AQ3:AQ36 are on the Formulas worksheet, but AR3:Ar36 and AP3:Ap36 are on the sheet with the formula. If that doesn't help, you may want to rewrite your question. Your sample showed columns A:C, but that's not in your formula. And you'll want to share what's in the values (M27, AG15), too. In fact, if I were you, I'd create a small table in a test workbook/worksheet and try to get it to work there. 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. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Look up one number based on mulitple criteria!!!!
It looks to me like you are attempting to "reverse engineer" the
requirements. The 100 vs.70 is going to be required due to the "weight times the span". Determine that number and use it as the choice between 70 or 100. scottgorilla wrote: Dave, Unfortunately it would be nice to say ok use the 100 on anything wider than 72" but it is not cost prohibitive. Thus the reason for trying to create the formula so anyone whom enters the data in the program would not have to have all the knowledge or have to continually look the data up based on size and weight. Have excel read the data and give the solution. I have tried with smaller formulas but unfortunately I'm still getting the same result. It will not pick the correct answer and I can't look over all the employees to make sure they didn't make a mistake because mistakes cost money "Dave Peterson" wrote: It sure looks like you should be changing the table. Maybe... 70 24 100000.000 70 36 3628.000 70 48 2712.950 70 60 2162.080 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 So that any weight greater than or equal to 72 has to return 100. But I'm not sure what happens at 71.99999 Did you try creating the smaller formula to see how that evaluated. scottgorilla wrote: I'm using Excel 2007 and those are named cell references so no need for the double quotations and secondly AQ and AR are on the same sheet. What I am trying to do based on criteria that I have set like as follows if length is for argument sake is 72 and weight is less than 1397.550 the result would be 70 if length is 72 and weight greater than 1397.550 the result would be 100 70 and 100 are diameter of a tube I must use to build my product based on weights and span if length is 133 and weight is less than 122.940 then I can use 70 if the length is 133 and weight is greater than 122.940 then it must be 100. I truly appreciate your assistance and apologize that I seem not to be doing a great job explaining what I'm trying to accomplish. "Dave Peterson" wrote: First, the strings in your formula have to be surrounded by double quotes: =IF(OR(Master!C18="ALUM.050DADE",Master!C18="A LUM.050PALM", Second, it still looks like AQ3:AQ36 and AR3:AR36 are on different sheets. Third, if all the data and formulas are on the same sheet, then the formula doesn't need the sheet references. Fourth, try this simplified formula: =MATCH(1,(AL5<=$AQ$3:$AQ$36)*(AG5=$AR$3:$AR$3 6),0) (still an array formula) This returns 11. That's 11 rows into the table--or this line of data. 70 144 122.94 The first time that 139.5 is less than or equal to the values in AQ3:AQ36 and at the same time 336.72 is greater than or equal to the values in AR3:AR36. So that 70 is the correct result for your formula. ===== But I think you've got other problems. In your table, you have: 70 24 100000.000 .... 100 24 100000.000 And there's never going to be a formula that uses =index(match()) that will return 100. The first row found that matches those identical conditions will always be the 70. I'm not sure what you're doing, but maybe you could use two tables (one for 70 and one for 100) or add an extra indicator to know what "group" you want to inspect. But it sure looks like you're deciding to bring back 70 or 100. I don't understand what you really want. scottgorilla wrote: I'm sorry for the confusion Dave but actually I have all of the information and formula on the same sheet now and it still returns the wrong answer..... =IF(OR(Master!C18=ALUM.050DADE,Master!C18=ALU M.050PALM,Master!C18=STEEL24ga,Master!C18=CLEARPAN ELS,Master!C18=IMPBAHAMA,Master!C18=IMPCOLONIAL,Ma ster!C18=DECOBAHAMA,Master!C18=DECOCOLONIAL,Master !C18=ACC6_8,Master!C18=ACC6_8BL,Master!C18=ACC6_8H R,Master!C18=ACC6_8HRB,Master!C18=BULLDOG,Master!C 18="")," ",INDEX($AP$3:$AP$36,MATCH(1,(AL5<=FORMULAS!$ AQ$3:$AQ$36)*(FORMULAS!AG5=$AR$3:$AR$36),0))) AL5= 139.50 here is an actual copy of the table 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 100000.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 "Dave Peterson" wrote: I would think that the table would be on a single worksheet. =INDEX($AP$3:$AP$36, MATCH(1,('ROLL CUT SHEET'!M27<=FORMULAS!$AQ$3:$AQ$36) *(FORMULAS!AG15=$AR$3:$AR$36),0))) And AQ3:AQ36 are on the Formulas worksheet, but AR3:Ar36 and AP3:Ap36 are on the sheet with the formula. If that doesn't help, you may want to rewrite your question. Your sample showed columns A:C, but that's not in your formula. And you'll want to share what's in the values (M27, AG15), too. In fact, if I were you, I'd create a small table in a test workbook/worksheet and try to get it to work there. 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)*(FORMULA S!AG15=$AR$3:$AR$36),0))) using ctrl+shift+enter. Thank you so much for assistance in advance. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Look up one number based on mulitple criteria!!!!
Mike,
If you call it reverse engineering I'll except that. You say determine that number (what number????) and use it as the choice between 100 v 70. I don't think it is weight times span .... so much as span can't exceed weight and then choose between the two. Thank You for your time and response. Scottgorilla "Bob I" wrote: It looks to me like you are attempting to "reverse engineer" the requirements. The 100 vs.70 is going to be required due to the "weight times the span". Determine that number and use it as the choice between 70 or 100. scottgorilla wrote: Dave, Unfortunately it would be nice to say ok use the 100 on anything wider than 72" but it is not cost prohibitive. Thus the reason for trying to create the formula so anyone whom enters the data in the program would not have to have all the knowledge or have to continually look the data up based on size and weight. Have excel read the data and give the solution. I have tried with smaller formulas but unfortunately I'm still getting the same result. It will not pick the correct answer and I can't look over all the employees to make sure they didn't make a mistake because mistakes cost money "Dave Peterson" wrote: It sure looks like you should be changing the table. Maybe... 70 24 100000.000 70 36 3628.000 70 48 2712.950 70 60 2162.080 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 So that any weight greater than or equal to 72 has to return 100. But I'm not sure what happens at 71.99999 Did you try creating the smaller formula to see how that evaluated. scottgorilla wrote: I'm using Excel 2007 and those are named cell references so no need for the double quotations and secondly AQ and AR are on the same sheet. What I am trying to do based on criteria that I have set like as follows if length is for argument sake is 72 and weight is less than 1397.550 the result would be 70 if length is 72 and weight greater than 1397.550 the result would be 100 70 and 100 are diameter of a tube I must use to build my product based on weights and span if length is 133 and weight is less than 122.940 then I can use 70 if the length is 133 and weight is greater than 122.940 then it must be 100. I truly appreciate your assistance and apologize that I seem not to be doing a great job explaining what I'm trying to accomplish. "Dave Peterson" wrote: First, the strings in your formula have to be surrounded by double quotes: =IF(OR(Master!C18="ALUM.050DADE",Master!C18="A LUM.050PALM", Second, it still looks like AQ3:AQ36 and AR3:AR36 are on different sheets. Third, if all the data and formulas are on the same sheet, then the formula doesn't need the sheet references. Fourth, try this simplified formula: =MATCH(1,(AL5<=$AQ$3:$AQ$36)*(AG5=$AR$3:$AR$3 6),0) (still an array formula) This returns 11. That's 11 rows into the table--or this line of data. 70 144 122.94 The first time that 139.5 is less than or equal to the values in AQ3:AQ36 and at the same time 336.72 is greater than or equal to the values in AR3:AR36. So that 70 is the correct result for your formula. ===== But I think you've got other problems. In your table, you have: 70 24 100000.000 .... 100 24 100000.000 And there's never going to be a formula that uses =index(match()) that will return 100. The first row found that matches those identical conditions will always be the 70. I'm not sure what you're doing, but maybe you could use two tables (one for 70 and one for 100) or add an extra indicator to know what "group" you want to inspect. But it sure looks like you're deciding to bring back 70 or 100. I don't understand what you really want. scottgorilla wrote: I'm sorry for the confusion Dave but actually I have all of the information and formula on the same sheet now and it still returns the wrong answer..... =IF(OR(Master!C18=ALUM.050DADE,Master!C18=ALU M.050PALM,Master!C18=STEEL24ga,Master!C18=CLEARPAN ELS,Master!C18=IMPBAHAMA,Master!C18=IMPCOLONIAL,Ma ster!C18=DECOBAHAMA,Master!C18=DECOCOLONIAL,Master !C18=ACC6_8,Master!C18=ACC6_8BL,Master!C18=ACC6_8H R,Master!C18=ACC6_8HRB,Master!C18=BULLDOG,Master!C 18="")," ",INDEX($AP$3:$AP$36,MATCH(1,(AL5<=FORMULAS!$ AQ$3:$AQ$36)*(FORMULAS!AG5=$AR$3:$AR$36),0))) AL5= 139.50 here is an actual copy of the table 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 100000.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 "Dave Peterson" wrote: I would think that the table would be on a single worksheet. =INDEX($AP$3:$AP$36, MATCH(1,('ROLL CUT SHEET'!M27<=FORMULAS!$AQ$3:$AQ$36) *(FORMULAS!AG15=$AR$3:$AR$36),0))) And AQ3:AQ36 are on the Formulas worksheet, but AR3:Ar36 and AP3:Ap36 are on the sheet with the formula. If that doesn't help, you may want to rewrite your question. Your sample showed columns A:C, but that's not in your formula. And you'll want to share what's in the values (M27, AG15), too. In fact, if I were you, I'd create a small table in a test workbook/worksheet and try to get it to work there. 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)*(FORMULA S!AG15=$AR$3:$AR$36),0))) using ctrl+shift+enter. Thank you so much for assistance in advance. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Look up one number based on mulitple criteria!!!!
I'm Bob not Mike,
I am just looking at what you say you are trying to accomplish. IF the weight is X and the span is less than Y you may use 70 otherwise you must use 100. Correct? So, this requirement comes from the strength required and the span times the weight sets the minimum tube size to do that. Who made the table up? Where did it come from, there is a mathematical formula that it was created from. You are attempting to figure that formula out by trial and error. Can you get that formula from the creator of the table? Otherwise you will need to play with the numbers to find the line that creates the breakpoint between needing 100 instead of 70. scottgorilla wrote: Mike, If you call it reverse engineering I'll except that. You say determine that number (what number????) and use it as the choice between 100 v 70. I don't think it is weight times span .... so much as span can't exceed weight and then choose between the two. Thank You for your time and response. Scottgorilla "Bob I" wrote: It looks to me like you are attempting to "reverse engineer" the requirements. The 100 vs.70 is going to be required due to the "weight times the span". Determine that number and use it as the choice between 70 or 100. scottgorilla wrote: Dave, Unfortunately it would be nice to say ok use the 100 on anything wider than 72" but it is not cost prohibitive. Thus the reason for trying to create the formula so anyone whom enters the data in the program would not have to have all the knowledge or have to continually look the data up based on size and weight. Have excel read the data and give the solution. I have tried with smaller formulas but unfortunately I'm still getting the same result. It will not pick the correct answer and I can't look over all the employees to make sure they didn't make a mistake because mistakes cost money "Dave Peterson" wrote: It sure looks like you should be changing the table. Maybe... 70 24 100000.000 70 36 3628.000 70 48 2712.950 70 60 2162.080 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 So that any weight greater than or equal to 72 has to return 100. But I'm not sure what happens at 71.99999 Did you try creating the smaller formula to see how that evaluated. scottgorilla wrote: I'm using Excel 2007 and those are named cell references so no need for the double quotations and secondly AQ and AR are on the same sheet. What I am trying to do based on criteria that I have set like as follows if length is for argument sake is 72 and weight is less than 1397.550 the result would be 70 if length is 72 and weight greater than 1397.550 the result would be 100 70 and 100 are diameter of a tube I must use to build my product based on weights and span if length is 133 and weight is less than 122.940 then I can use 70 if the length is 133 and weight is greater than 122.940 then it must be 100. I truly appreciate your assistance and apologize that I seem not to be doing a great job explaining what I'm trying to accomplish. "Dave Peterson" wrote: First, the strings in your formula have to be surrounded by double quotes: =IF(OR(Master!C18="ALUM.050DADE",Master!C18= "ALUM.050PALM", Second, it still looks like AQ3:AQ36 and AR3:AR36 are on different sheets. Third, if all the data and formulas are on the same sheet, then the formula doesn't need the sheet references. Fourth, try this simplified formula: =MATCH(1,(AL5<=$AQ$3:$AQ$36)*(AG5=$AR$3:$AR $36),0) (still an array formula) This returns 11. That's 11 rows into the table--or this line of data. 70 144 122.94 The first time that 139.5 is less than or equal to the values in AQ3:AQ36 and at the same time 336.72 is greater than or equal to the values in AR3:AR36. So that 70 is the correct result for your formula. ===== But I think you've got other problems. In your table, you have: 70 24 100000.000 .... 100 24 100000.000 And there's never going to be a formula that uses =index(match()) that will return 100. The first row found that matches those identical conditions will always be the 70. I'm not sure what you're doing, but maybe you could use two tables (one for 70 and one for 100) or add an extra indicator to know what "group" you want to inspect. But it sure looks like you're deciding to bring back 70 or 100. I don't understand what you really want. scottgorilla wrote: I'm sorry for the confusion Dave but actually I have all of the information and formula on the same sheet now and it still returns the wrong answer..... =IF(OR(Master!C18=ALUM.050DADE,Master!C18=A LUM.050PALM,Master!C18=STEEL24ga,Master!C18=CLEARP ANELS,Master!C18=IMPBAHAMA,Master!C18=IMPCOLONIAL, Master!C18=DECOBAHAMA,Master!C18=DECOCOLONIAL,Mast er!C18=ACC6_8,Master!C18=ACC6_8BL,Master!C18=ACC6_ 8HR,Master!C18=ACC6_8HRB,Master!C18=BULLDOG,Master !C18="")," ",INDEX($AP$3:$AP$36,MATCH(1,(AL5<=FORMULAS !$AQ$3:$AQ$36)*(FORMULAS!AG5=$AR$3:$AR$36),0))) AL5= 139.50 here is an actual copy of the table 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 100000.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 "Dave Peterson" wrote: I would think that the table would be on a single worksheet. =INDEX($AP$3:$AP$36, MATCH(1,('ROLL CUT SHEET'!M27<=FORMULAS!$AQ$3:$AQ$36) *(FORMULAS!AG15=$AR$3:$AR$36),0))) And AQ3:AQ36 are on the Formulas worksheet, but AR3:Ar36 and AP3:Ap36 are on the sheet with the formula. If that doesn't help, you may want to rewrite your question. Your sample showed columns A:C, but that's not in your formula. And you'll want to share what's in the values (M27, AG15), too. In fact, if I were you, I'd create a small table in a test workbook/worksheet and try to get it to work there. 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)*(FORMU LAS!AG15=$AR$3:$AR$36),0))) using ctrl+shift+enter. Thank you so much for assistance in advance. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Look up one number based on mulitple criteria!!!!
Sorry Bob,
The list was given to me in PDF format and was done by an engineer that I will not be able to get the calculations from. What I am trying to accomplish is not try and find the fomula for the table but based on results that are determined in another part of my program. I'm am trying to get Excel to pick the appropriate size 70 or 100. If the results are as follows say up to 71.5" and up to 1397.00lbs then pick 70 and again if the results are up to 71.5" and greater than 1397.001 then pick 100 and so on.... It must be possible but I just can't seem to find a way unless I write every possioble scenario like this. IF(and(d1<=a,e1<=b),70,if(and(d1<=a,e1b),100 and so on...... Thank You again, Scott "Bob I" wrote: I'm Bob not Mike, I am just looking at what you say you are trying to accomplish. IF the weight is X and the span is less than Y you may use 70 otherwise you must use 100. Correct? So, this requirement comes from the strength required and the span times the weight sets the minimum tube size to do that. Who made the table up? Where did it come from, there is a mathematical formula that it was created from. You are attempting to figure that formula out by trial and error. Can you get that formula from the creator of the table? Otherwise you will need to play with the numbers to find the line that creates the breakpoint between needing 100 instead of 70. scottgorilla wrote: Mike, If you call it reverse engineering I'll except that. You say determine that number (what number????) and use it as the choice between 100 v 70. I don't think it is weight times span .... so much as span can't exceed weight and then choose between the two. Thank You for your time and response. Scottgorilla "Bob I" wrote: It looks to me like you are attempting to "reverse engineer" the requirements. The 100 vs.70 is going to be required due to the "weight times the span". Determine that number and use it as the choice between 70 or 100. scottgorilla wrote: Dave, Unfortunately it would be nice to say ok use the 100 on anything wider than 72" but it is not cost prohibitive. Thus the reason for trying to create the formula so anyone whom enters the data in the program would not have to have all the knowledge or have to continually look the data up based on size and weight. Have excel read the data and give the solution. I have tried with smaller formulas but unfortunately I'm still getting the same result. It will not pick the correct answer and I can't look over all the employees to make sure they didn't make a mistake because mistakes cost money "Dave Peterson" wrote: It sure looks like you should be changing the table. Maybe... 70 24 100000.000 70 36 3628.000 70 48 2712.950 70 60 2162.080 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 So that any weight greater than or equal to 72 has to return 100. But I'm not sure what happens at 71.99999 Did you try creating the smaller formula to see how that evaluated. scottgorilla wrote: I'm using Excel 2007 and those are named cell references so no need for the double quotations and secondly AQ and AR are on the same sheet. What I am trying to do based on criteria that I have set like as follows if length is for argument sake is 72 and weight is less than 1397.550 the result would be 70 if length is 72 and weight greater than 1397.550 the result would be 100 70 and 100 are diameter of a tube I must use to build my product based on weights and span if length is 133 and weight is less than 122.940 then I can use 70 if the length is 133 and weight is greater than 122.940 then it must be 100. I truly appreciate your assistance and apologize that I seem not to be doing a great job explaining what I'm trying to accomplish. "Dave Peterson" wrote: First, the strings in your formula have to be surrounded by double quotes: =IF(OR(Master!C18="ALUM.050DADE",Master!C18= "ALUM.050PALM", Second, it still looks like AQ3:AQ36 and AR3:AR36 are on different sheets. Third, if all the data and formulas are on the same sheet, then the formula doesn't need the sheet references. Fourth, try this simplified formula: =MATCH(1,(AL5<=$AQ$3:$AQ$36)*(AG5=$AR$3:$AR $36),0) (still an array formula) This returns 11. That's 11 rows into the table--or this line of data. 70 144 122.94 The first time that 139.5 is less than or equal to the values in AQ3:AQ36 and at the same time 336.72 is greater than or equal to the values in AR3:AR36. So that 70 is the correct result for your formula. ===== But I think you've got other problems. In your table, you have: 70 24 100000.000 .... 100 24 100000.000 And there's never going to be a formula that uses =index(match()) that will return 100. The first row found that matches those identical conditions will always be the 70. I'm not sure what you're doing, but maybe you could use two tables (one for 70 and one for 100) or add an extra indicator to know what "group" you want to inspect. But it sure looks like you're deciding to bring back 70 or 100. I don't understand what you really want. scottgorilla wrote: I'm sorry for the confusion Dave but actually I have all of the information and formula on the same sheet now and it still returns the wrong answer..... =IF(OR(Master!C18=ALUM.050DADE,Master!C18=A LUM.050PALM,Master!C18=STEEL24ga,Master!C18=CLEARP ANELS,Master!C18=IMPBAHAMA,Master!C18=IMPCOLONIAL, Master!C18=DECOBAHAMA,Master!C18=DECOCOLONIAL,Mast er!C18=ACC6_8,Master!C18=ACC6_8BL,Master!C18=ACC6_ 8HR,Master!C18=ACC6_8HRB,Master!C18=BULLDOG,Master !C18="")," ",INDEX($AP$3:$AP$36,MATCH(1,(AL5<=FORMULAS !$AQ$3:$AQ$36)*(FORMULAS!AG5=$AR$3:$AR$36),0))) AL5= 139.50 here is an actual copy of the table 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 100000.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 "Dave Peterson" wrote: I would think that the table would be on a single worksheet. =INDEX($AP$3:$AP$36, MATCH(1,('ROLL CUT SHEET'!M27<=FORMULAS!$AQ$3:$AQ$36) *(FORMULAS!AG15=$AR$3:$AR$36),0))) And AQ3:AQ36 are on the Formulas worksheet, but AR3:Ar36 and AP3:Ap36 are on the sheet with the formula. If that doesn't help, you may want to rewrite your question. Your sample showed columns A:C, but that's not in your formula. And you'll want to share what's in the values (M27, AG15), too. In fact, if I were you, I'd create a small table in a test workbook/worksheet and try to get it to work there. 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)*(FORMU LAS!AG15=$AR$3:$AR$36),0))) using ctrl+shift+enter. Thank you so much for assistance in advance. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Look up one number based on mulitple criteria!!!!
Ok, then I understand the end result correctly, the issue is that the
table is actually discrete points on the line created by that formula. That's the only other way to get there other than listing and comparing values. Are you looking up an arbitrary weight and then a span on the list? Or are the weight and the span both allowed to be any number at all? scottgorilla wrote: Sorry Bob, The list was given to me in PDF format and was done by an engineer that I will not be able to get the calculations from. What I am trying to accomplish is not try and find the fomula for the table but based on results that are determined in another part of my program. I'm am trying to get Excel to pick the appropriate size 70 or 100. If the results are as follows say up to 71.5" and up to 1397.00lbs then pick 70 and again if the results are up to 71.5" and greater than 1397.001 then pick 100 and so on.... It must be possible but I just can't seem to find a way unless I write every possioble scenario like this. IF(and(d1<=a,e1<=b),70,if(and(d1<=a,e1b),100 and so on...... Thank You again, Scott "Bob I" wrote: I'm Bob not Mike, I am just looking at what you say you are trying to accomplish. IF the weight is X and the span is less than Y you may use 70 otherwise you must use 100. Correct? So, this requirement comes from the strength required and the span times the weight sets the minimum tube size to do that. Who made the table up? Where did it come from, there is a mathematical formula that it was created from. You are attempting to figure that formula out by trial and error. Can you get that formula from the creator of the table? Otherwise you will need to play with the numbers to find the line that creates the breakpoint between needing 100 instead of 70. scottgorilla wrote: Mike, If you call it reverse engineering I'll except that. You say determine that number (what number????) and use it as the choice between 100 v 70. I don't think it is weight times span .... so much as span can't exceed weight and then choose between the two. Thank You for your time and response. Scottgorilla "Bob I" wrote: It looks to me like you are attempting to "reverse engineer" the requirements. The 100 vs.70 is going to be required due to the "weight times the span". Determine that number and use it as the choice between 70 or 100. scottgorilla wrote: Dave, Unfortunately it would be nice to say ok use the 100 on anything wider than 72" but it is not cost prohibitive. Thus the reason for trying to create the formula so anyone whom enters the data in the program would not have to have all the knowledge or have to continually look the data up based on size and weight. Have excel read the data and give the solution. I have tried with smaller formulas but unfortunately I'm still getting the same result. It will not pick the correct answer and I can't look over all the employees to make sure they didn't make a mistake because mistakes cost money "Dave Peterson" wrote: It sure looks like you should be changing the table. Maybe... 70 24 100000.000 70 36 3628.000 70 48 2712.950 70 60 2162.080 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 So that any weight greater than or equal to 72 has to return 100. But I'm not sure what happens at 71.99999 Did you try creating the smaller formula to see how that evaluated. scottgorilla wrote: I'm using Excel 2007 and those are named cell references so no need for the double quotations and secondly AQ and AR are on the same sheet. What I am trying to do based on criteria that I have set like as follows if length is for argument sake is 72 and weight is less than 1397.550 the result would be 70 if length is 72 and weight greater than 1397.550 the result would be 100 70 and 100 are diameter of a tube I must use to build my product based on weights and span if length is 133 and weight is less than 122.940 then I can use 70 if the length is 133 and weight is greater than 122.940 then it must be 100. I truly appreciate your assistance and apologize that I seem not to be doing a great job explaining what I'm trying to accomplish. "Dave Peterson" wrote: First, the strings in your formula have to be surrounded by double quotes: =IF(OR(Master!C18="ALUM.050DADE",Master!C1 8="ALUM.050PALM", Second, it still looks like AQ3:AQ36 and AR3:AR36 are on different sheets. Third, if all the data and formulas are on the same sheet, then the formula doesn't need the sheet references. Fourth, try this simplified formula: =MATCH(1,(AL5<=$AQ$3:$AQ$36)*(AG5=$AR$3:$ AR$36),0) (still an array formula) This returns 11. That's 11 rows into the table--or this line of data. 70 144 122.94 The first time that 139.5 is less than or equal to the values in AQ3:AQ36 and at the same time 336.72 is greater than or equal to the values in AR3:AR36. So that 70 is the correct result for your formula. ===== But I think you've got other problems. In your table, you have: 70 24 100000.000 .... 100 24 100000.000 And there's never going to be a formula that uses =index(match()) that will return 100. The first row found that matches those identical conditions will always be the 70. I'm not sure what you're doing, but maybe you could use two tables (one for 70 and one for 100) or add an extra indicator to know what "group" you want to inspect. But it sure looks like you're deciding to bring back 70 or 100. I don't understand what you really want. scottgorilla wrote: I'm sorry for the confusion Dave but actually I have all of the information and formula on the same sheet now and it still returns the wrong answer..... =IF(OR(Master!C18=ALUM.050DADE,Master!C18 =ALUM.050PALM,Master!C18=STEEL24ga,Master!C18=CLEA RPANELS,Master!C18=IMPBAHAMA,Master!C18=IMPCOLONIA L,Master!C18=DECOBAHAMA,Master!C18=DECOCOLONIAL,Ma ster!C18=ACC6_8,Master!C18=ACC6_8BL,Master!C18=ACC 6_8HR,Master!C18=ACC6_8HRB,Master!C18=BULLDOG,Mast er!C18="")," ",INDEX($AP$3:$AP$36,MATCH(1,(AL5<=FORMUL AS!$AQ$3:$AQ$36)*(FORMULAS!AG5=$AR$3:$AR$36),0))) AL5= 139.50 here is an actual copy of the table 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 100000.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 "Dave Peterson" wrote: I would think that the table would be on a single worksheet. =INDEX($AP$3:$AP$36, MATCH(1,('ROLL CUT SHEET'!M27<=FORMULAS!$AQ$3:$AQ$36) *(FORMULAS!AG15=$AR$3:$AR$36),0))) And AQ3:AQ36 are on the Formulas worksheet, but AR3:Ar36 and AP3:Ap36 are on the sheet with the formula. If that doesn't help, you may want to rewrite your question. Your sample showed columns A:C, but that's not in your formula. And you'll want to share what's in the values (M27, AG15), too. In fact, if I were you, I'd create a small table in a test workbook/worksheet and try to get it to work there. 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)*(FOR MULAS!AG15=$AR$3:$AR$36),0))) using ctrl+shift+enter. Thank you so much for assistance in advance. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Look up one number based on mulitple criteria!!!!
Bob,
Thank you again for your patience and assistance. The weight and span vary constantly. We are building a custom product that constantly changes based size of the application. So the only constant is the table because these are maximums. Scottgorilla "Bob I" wrote: Ok, then I understand the end result correctly, the issue is that the table is actually discrete points on the line created by that formula. That's the only other way to get there other than listing and comparing values. Are you looking up an arbitrary weight and then a span on the list? Or are the weight and the span both allowed to be any number at all? scottgorilla wrote: Sorry Bob, The list was given to me in PDF format and was done by an engineer that I will not be able to get the calculations from. What I am trying to accomplish is not try and find the fomula for the table but based on results that are determined in another part of my program. I'm am trying to get Excel to pick the appropriate size 70 or 100. If the results are as follows say up to 71.5" and up to 1397.00lbs then pick 70 and again if the results are up to 71.5" and greater than 1397.001 then pick 100 and so on.... It must be possible but I just can't seem to find a way unless I write every possioble scenario like this. IF(and(d1<=a,e1<=b),70,if(and(d1<=a,e1b),100 and so on...... Thank You again, Scott "Bob I" wrote: I'm Bob not Mike, I am just looking at what you say you are trying to accomplish. IF the weight is X and the span is less than Y you may use 70 otherwise you must use 100. Correct? So, this requirement comes from the strength required and the span times the weight sets the minimum tube size to do that. Who made the table up? Where did it come from, there is a mathematical formula that it was created from. You are attempting to figure that formula out by trial and error. Can you get that formula from the creator of the table? Otherwise you will need to play with the numbers to find the line that creates the breakpoint between needing 100 instead of 70. scottgorilla wrote: Mike, If you call it reverse engineering I'll except that. You say determine that number (what number????) and use it as the choice between 100 v 70. I don't think it is weight times span .... so much as span can't exceed weight and then choose between the two. Thank You for your time and response. Scottgorilla "Bob I" wrote: It looks to me like you are attempting to "reverse engineer" the requirements. The 100 vs.70 is going to be required due to the "weight times the span". Determine that number and use it as the choice between 70 or 100. scottgorilla wrote: Dave, Unfortunately it would be nice to say ok use the 100 on anything wider than 72" but it is not cost prohibitive. Thus the reason for trying to create the formula so anyone whom enters the data in the program would not have to have all the knowledge or have to continually look the data up based on size and weight. Have excel read the data and give the solution. I have tried with smaller formulas but unfortunately I'm still getting the same result. It will not pick the correct answer and I can't look over all the employees to make sure they didn't make a mistake because mistakes cost money "Dave Peterson" wrote: It sure looks like you should be changing the table. Maybe... 70 24 100000.000 70 36 3628.000 70 48 2712.950 70 60 2162.080 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 So that any weight greater than or equal to 72 has to return 100. But I'm not sure what happens at 71.99999 Did you try creating the smaller formula to see how that evaluated. scottgorilla wrote: I'm using Excel 2007 and those are named cell references so no need for the double quotations and secondly AQ and AR are on the same sheet. What I am trying to do based on criteria that I have set like as follows if length is for argument sake is 72 and weight is less than 1397.550 the result would be 70 if length is 72 and weight greater than 1397.550 the result would be 100 70 and 100 are diameter of a tube I must use to build my product based on weights and span if length is 133 and weight is less than 122.940 then I can use 70 if the length is 133 and weight is greater than 122.940 then it must be 100. I truly appreciate your assistance and apologize that I seem not to be doing a great job explaining what I'm trying to accomplish. "Dave Peterson" wrote: First, the strings in your formula have to be surrounded by double quotes: =IF(OR(Master!C18="ALUM.050DADE",Master!C1 8="ALUM.050PALM", Second, it still looks like AQ3:AQ36 and AR3:AR36 are on different sheets. Third, if all the data and formulas are on the same sheet, then the formula doesn't need the sheet references. Fourth, try this simplified formula: =MATCH(1,(AL5<=$AQ$3:$AQ$36)*(AG5=$AR$3:$ AR$36),0) (still an array formula) This returns 11. That's 11 rows into the table--or this line of data. 70 144 122.94 The first time that 139.5 is less than or equal to the values in AQ3:AQ36 and at the same time 336.72 is greater than or equal to the values in AR3:AR36. So that 70 is the correct result for your formula. ===== But I think you've got other problems. In your table, you have: 70 24 100000.000 .... 100 24 100000.000 And there's never going to be a formula that uses =index(match()) that will return 100. The first row found that matches those identical conditions will always be the 70. I'm not sure what you're doing, but maybe you could use two tables (one for 70 and one for 100) or add an extra indicator to know what "group" you want to inspect. But it sure looks like you're deciding to bring back 70 or 100. I don't understand what you really want. scottgorilla wrote: I'm sorry for the confusion Dave but actually I have all of the information and formula on the same sheet now and it still returns the wrong answer..... =IF(OR(Master!C18=ALUM.050DADE,Master!C18 =ALUM.050PALM,Master!C18=STEEL24ga,Master!C18=CLEA RPANELS,Master!C18=IMPBAHAMA,Master!C18=IMPCOLONIA L,Master!C18=DECOBAHAMA,Master!C18=DECOCOLONIAL,Ma ster!C18=ACC6_8,Master!C18=ACC6_8BL,Master!C18=ACC 6_8HR,Master!C18=ACC6_8HRB,Master!C18=BULLDOG,Mast er!C18="")," ",INDEX($AP$3:$AP$36,MATCH(1,(AL5<=FORMUL AS!$AQ$3:$AQ$36)*(FORMULAS!AG5=$AR$3:$AR$36),0))) AL5= 139.50 here is an actual copy of the table 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 100000.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 "Dave Peterson" wrote: I would think that the table would be on a single worksheet. =INDEX($AP$3:$AP$36, MATCH(1,('ROLL CUT SHEET'!M27<=FORMULAS!$AQ$3:$AQ$36) *(FORMULAS!AG15=$AR$3:$AR$36),0))) And AQ3:AQ36 are on the Formulas worksheet, but AR3:Ar36 and AP3:Ap36 are on the sheet with the formula. If that doesn't help, you may want to rewrite your question. Your sample showed columns A:C, but that's not in your formula. And you'll want to share what's in the values (M27, AG15), too. In fact, if I were you, I'd create a small table in a test workbook/worksheet and try to get it to work there. 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)*(FOR MULAS!AG15=$AR$3:$AR$36),0))) using ctrl+shift+enter. Thank you so much for assistance in advance. -- Dave Peterson |
Look up one number based on mulitple criteria!!!!
This doesn't check for maximums, only whether 70 can be used. Span is A1 Weight is B1 =IF(((A1^3)*B1*0.0000007)1000,100,70) See if it does what you intended. scottgorilla wrote: Bob, Thank you again for your patience and assistance. The weight and span vary constantly. We are building a custom product that constantly changes based size of the application. So the only constant is the table because these are maximums. Scottgorilla "Bob I" wrote: Ok, then I understand the end result correctly, the issue is that the table is actually discrete points on the line created by that formula. That's the only other way to get there other than listing and comparing values. Are you looking up an arbitrary weight and then a span on the list? Or are the weight and the span both allowed to be any number at all? scottgorilla wrote: Sorry Bob, The list was given to me in PDF format and was done by an engineer that I will not be able to get the calculations from. What I am trying to accomplish is not try and find the fomula for the table but based on results that are determined in another part of my program. I'm am trying to get Excel to pick the appropriate size 70 or 100. If the results are as follows say up to 71.5" and up to 1397.00lbs then pick 70 and again if the results are up to 71.5" and greater than 1397.001 then pick 100 and so on.... It must be possible but I just can't seem to find a way unless I write every possioble scenario like this. IF(and(d1<=a,e1<=b),70,if(and(d1<=a,e1b),100 and so on...... Thank You again, Scott "Bob I" wrote: I'm Bob not Mike, I am just looking at what you say you are trying to accomplish. IF the weight is X and the span is less than Y you may use 70 otherwise you must use 100. Correct? So, this requirement comes from the strength required and the span times the weight sets the minimum tube size to do that. Who made the table up? Where did it come from, there is a mathematical formula that it was created from. You are attempting to figure that formula out by trial and error. Can you get that formula from the creator of the table? Otherwise you will need to play with the numbers to find the line that creates the breakpoint between needing 100 instead of 70. scottgorilla wrote: Mike, If you call it reverse engineering I'll except that. You say determine that number (what number????) and use it as the choice between 100 v 70. I don't think it is weight times span .... so much as span can't exceed weight and then choose between the two. Thank You for your time and response. Scottgorilla "Bob I" wrote: It looks to me like you are attempting to "reverse engineer" the requirements. The 100 vs.70 is going to be required due to the "weight times the span". Determine that number and use it as the choice between 70 or 100. scottgorilla wrote: Dave, Unfortunately it would be nice to say ok use the 100 on anything wider than 72" but it is not cost prohibitive. Thus the reason for trying to create the formula so anyone whom enters the data in the program would not have to have all the knowledge or have to continually look the data up based on size and weight. Have excel read the data and give the solution. I have tried with smaller formulas but unfortunately I'm still getting the same result. It will not pick the correct answer and I can't look over all the employees to make sure they didn't make a mistake because mistakes cost money "Dave Peterson" wrote: It sure looks like you should be changing the table. Maybe... 70 24 100000.000 70 36 3628.000 70 48 2712.950 70 60 2162.080 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 So that any weight greater than or equal to 72 has to return 100. But I'm not sure what happens at 71.99999 Did you try creating the smaller formula to see how that evaluated. scottgorilla wrote: I'm using Excel 2007 and those are named cell references so no need for the double quotations and secondly AQ and AR are on the same sheet. What I am trying to do based on criteria that I have set like as follows if length is for argument sake is 72 and weight is less than 1397.550 the result would be 70 if length is 72 and weight greater than 1397.550 the result would be 100 70 and 100 are diameter of a tube I must use to build my product based on weights and span if length is 133 and weight is less than 122.940 then I can use 70 if the length is 133 and weight is greater than 122.940 then it must be 100. I truly appreciate your assistance and apologize that I seem not to be doing a great job explaining what I'm trying to accomplish. "Dave Peterson" wrote: First, the strings in your formula have to be surrounded by double quotes: =IF(OR(Master!C18="ALUM.050DADE",Master! C18="ALUM.050PALM", Second, it still looks like AQ3:AQ36 and AR3:AR36 are on different sheets. Third, if all the data and formulas are on the same sheet, then the formula doesn't need the sheet references. Fourth, try this simplified formula: =MATCH(1,(AL5<=$AQ$3:$AQ$36)*(AG5=$AR$3 :$AR$36),0) (still an array formula) This returns 11. That's 11 rows into the table--or this line of data. 70 144 122.94 The first time that 139.5 is less than or equal to the values in AQ3:AQ36 and at the same time 336.72 is greater than or equal to the values in AR3:AR36. So that 70 is the correct result for your formula. ===== But I think you've got other problems. In your table, you have: 70 24 100000.000 .... 100 24 100000.000 And there's never going to be a formula that uses =index(match()) that will return 100. The first row found that matches those identical conditions will always be the 70. I'm not sure what you're doing, but maybe you could use two tables (one for 70 and one for 100) or add an extra indicator to know what "group" you want to inspect. But it sure looks like you're deciding to bring back 70 or 100. I don't understand what you really want. scottgorilla wrote: I'm sorry for the confusion Dave but actually I have all of the information and formula on the same sheet now and it still returns the wrong answer..... =IF(OR(Master!C18=ALUM.050DADE,Master!C 18=ALUM.050PALM,Master!C18=STEEL24ga,Master!C18=CL EARPANELS,Master!C18=IMPBAHAMA,Master!C18=IMPCOLON IAL,Master!C18=DECOBAHAMA,Master!C18=DECOCOLONIAL, Master!C18=ACC6_8,Master!C18=ACC6_8BL,Master!C18=A CC6_8HR,Master!C18=ACC6_8HRB,Master!C18=BULLDOG,Ma ster!C18="")," ",INDEX($AP$3:$AP$36,MATCH(1,(AL5<=FORM ULAS!$AQ$3:$AQ$36)*(FORMULAS!AG5=$AR$3:$AR$36),0) )) AL5= 139.50 here is an actual copy of the table 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 100000.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 "Dave Peterson" wrote: I would think that the table would be on a single worksheet. =INDEX($AP$3:$AP$36, MATCH(1,('ROLL CUT SHEET'!M27<=FORMULAS!$AQ$3:$AQ$36) *(FORMULAS!AG15=$AR$3:$AR$36),0))) And AQ3:AQ36 are on the Formulas worksheet, but AR3:Ar36 and AP3:Ap36 are on the sheet with the formula. If that doesn't help, you may want to rewrite your question. Your sample showed columns A:C, but that's not in your formula. And you'll want to share what's in the values (M27, AG15), too. In fact, if I were you, I'd create a small table in a test workbook/worksheet and try to get it to work there. 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)*(F ORMULAS!AG15=$AR$3:$AR$36),0))) using ctrl+shift+enter. Thank you so much for assistance in advance. -- Dave Peterson |
Look up one number based on mulitple criteria!!!!
Bob,
You deserve a medal for even messing with this. Ok so I tried your formula based on the sizes actually kicked out by my program and it looked like this IF(((AL6^3)*AG6*0.0000007)1000,100,70)) AL6=127.50 which is actually determined by width AG6=308.464 which is determined by sq. ft. times product weight mathematically your formula returns 447.54 which is less than 1000 so it did return 70 but since the 1000 number is a constant and my numbers must fluctuate because of the span how can I get that to happen??? Thank You again "Bob I" wrote: This doesn't check for maximums, only whether 70 can be used. Span is A1 Weight is B1 =IF(((A1^3)*B1*0.0000007)1000,100,70) See if it does what you intended. scottgorilla wrote: Bob, Thank you again for your patience and assistance. The weight and span vary constantly. We are building a custom product that constantly changes based size of the application. So the only constant is the table because these are maximums. Scottgorilla "Bob I" wrote: Ok, then I understand the end result correctly, the issue is that the table is actually discrete points on the line created by that formula. That's the only other way to get there other than listing and comparing values. Are you looking up an arbitrary weight and then a span on the list? Or are the weight and the span both allowed to be any number at all? scottgorilla wrote: Sorry Bob, The list was given to me in PDF format and was done by an engineer that I will not be able to get the calculations from. What I am trying to accomplish is not try and find the fomula for the table but based on results that are determined in another part of my program. I'm am trying to get Excel to pick the appropriate size 70 or 100. If the results are as follows say up to 71.5" and up to 1397.00lbs then pick 70 and again if the results are up to 71.5" and greater than 1397.001 then pick 100 and so on.... It must be possible but I just can't seem to find a way unless I write every possioble scenario like this. IF(and(d1<=a,e1<=b),70,if(and(d1<=a,e1b),100 and so on...... Thank You again, Scott "Bob I" wrote: I'm Bob not Mike, I am just looking at what you say you are trying to accomplish. IF the weight is X and the span is less than Y you may use 70 otherwise you must use 100. Correct? So, this requirement comes from the strength required and the span times the weight sets the minimum tube size to do that. Who made the table up? Where did it come from, there is a mathematical formula that it was created from. You are attempting to figure that formula out by trial and error. Can you get that formula from the creator of the table? Otherwise you will need to play with the numbers to find the line that creates the breakpoint between needing 100 instead of 70. scottgorilla wrote: Mike, If you call it reverse engineering I'll except that. You say determine that number (what number????) and use it as the choice between 100 v 70. I don't think it is weight times span .... so much as span can't exceed weight and then choose between the two. Thank You for your time and response. Scottgorilla "Bob I" wrote: It looks to me like you are attempting to "reverse engineer" the requirements. The 100 vs.70 is going to be required due to the "weight times the span". Determine that number and use it as the choice between 70 or 100. scottgorilla wrote: Dave, Unfortunately it would be nice to say ok use the 100 on anything wider than 72" but it is not cost prohibitive. Thus the reason for trying to create the formula so anyone whom enters the data in the program would not have to have all the knowledge or have to continually look the data up based on size and weight. Have excel read the data and give the solution. I have tried with smaller formulas but unfortunately I'm still getting the same result. It will not pick the correct answer and I can't look over all the employees to make sure they didn't make a mistake because mistakes cost money "Dave Peterson" wrote: It sure looks like you should be changing the table. Maybe... 70 24 100000.000 70 36 3628.000 70 48 2712.950 70 60 2162.080 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 So that any weight greater than or equal to 72 has to return 100. But I'm not sure what happens at 71.99999 Did you try creating the smaller formula to see how that evaluated. scottgorilla wrote: I'm using Excel 2007 and those are named cell references so no need for the double quotations and secondly AQ and AR are on the same sheet. What I am trying to do based on criteria that I have set like as follows if length is for argument sake is 72 and weight is less than 1397.550 the result would be 70 if length is 72 and weight greater than 1397.550 the result would be 100 70 and 100 are diameter of a tube I must use to build my product based on weights and span if length is 133 and weight is less than 122.940 then I can use 70 if the length is 133 and weight is greater than 122.940 then it must be 100. I truly appreciate your assistance and apologize that I seem not to be doing a great job explaining what I'm trying to accomplish. "Dave Peterson" wrote: First, the strings in your formula have to be surrounded by double quotes: =IF(OR(Master!C18="ALUM.050DADE",Master! C18="ALUM.050PALM", Second, it still looks like AQ3:AQ36 and AR3:AR36 are on different sheets. Third, if all the data and formulas are on the same sheet, then the formula doesn't need the sheet references. Fourth, try this simplified formula: =MATCH(1,(AL5<=$AQ$3:$AQ$36)*(AG5=$AR$3 :$AR$36),0) (still an array formula) This returns 11. That's 11 rows into the table--or this line of data. 70 144 122.94 The first time that 139.5 is less than or equal to the values in AQ3:AQ36 and at the same time 336.72 is greater than or equal to the values in AR3:AR36. So that 70 is the correct result for your formula. ===== But I think you've got other problems. In your table, you have: 70 24 100000.000 .... 100 24 100000.000 And there's never going to be a formula that uses =index(match()) that will return 100. The first row found that matches those identical conditions will always be the 70. I'm not sure what you're doing, but maybe you could use two tables (one for 70 and one for 100) or add an extra indicator to know what "group" you want to inspect. But it sure looks like you're deciding to bring back 70 or 100. I don't understand what you really want. scottgorilla wrote: I'm sorry for the confusion Dave but actually I have all of the information and formula on the same sheet now and it still returns the wrong answer..... =IF(OR(Master!C18=ALUM.050DADE,Master!C 18=ALUM.050PALM,Master!C18=STEEL24ga,Master!C18=CL EARPANELS,Master!C18=IMPBAHAMA,Master!C18=IMPCOLON IAL,Master!C18=DECOBAHAMA,Master!C18=DECOCOLONIAL, Master!C18=ACC6_8,Master!C18=ACC6_8BL,Master!C18=A CC6_8HR,Master!C18=ACC6_8HRB,Master!C18=BULLDOG,Ma ster!C18="")," ",INDEX($AP$3:$AP$36,MATCH(1,(AL5<=FORM ULAS!$AQ$3:$AQ$36)*(FORMULAS!AG5=$AR$3:$AR$36),0) )) AL5= 139.50 here is an actual copy of the table 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 100000.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 "Dave Peterson" wrote: I would think that the table would be on a single worksheet. =INDEX($AP$3:$AP$36, MATCH(1,('ROLL CUT SHEET'!M27<=FORMULAS!$AQ$3:$AQ$36) *(FORMULAS!AG15=$AR$3:$AR$36),0))) |
Look up one number based on mulitple criteria!!!!
In case anyone was interested I found the solution it looks like this
=INDEX($AP$3:$AP$36,MATCH(1,(AL3<=FORMULAS!$AQ$3:$ AQ$36)*(FORMULAS!AG3<=$AR$3:$AR$36),0) followed by CONTROL+SHIFT+ENTER Well thanks all for the help Scottgorilla "scottgorilla" wrote: Bob, You deserve a medal for even messing with this. Ok so I tried your formula based on the sizes actually kicked out by my program and it looked like this IF(((AL6^3)*AG6*0.0000007)1000,100,70)) AL6=127.50 which is actually determined by width AG6=308.464 which is determined by sq. ft. times product weight mathematically your formula returns 447.54 which is less than 1000 so it did return 70 but since the 1000 number is a constant and my numbers must fluctuate because of the span how can I get that to happen??? Thank You again "Bob I" wrote: This doesn't check for maximums, only whether 70 can be used. Span is A1 Weight is B1 =IF(((A1^3)*B1*0.0000007)1000,100,70) See if it does what you intended. scottgorilla wrote: Bob, Thank you again for your patience and assistance. The weight and span vary constantly. We are building a custom product that constantly changes based size of the application. So the only constant is the table because these are maximums. Scottgorilla "Bob I" wrote: Ok, then I understand the end result correctly, the issue is that the table is actually discrete points on the line created by that formula. That's the only other way to get there other than listing and comparing values. Are you looking up an arbitrary weight and then a span on the list? Or are the weight and the span both allowed to be any number at all? scottgorilla wrote: Sorry Bob, The list was given to me in PDF format and was done by an engineer that I will not be able to get the calculations from. What I am trying to accomplish is not try and find the fomula for the table but based on results that are determined in another part of my program. I'm am trying to get Excel to pick the appropriate size 70 or 100. If the results are as follows say up to 71.5" and up to 1397.00lbs then pick 70 and again if the results are up to 71.5" and greater than 1397.001 then pick 100 and so on.... It must be possible but I just can't seem to find a way unless I write every possioble scenario like this. IF(and(d1<=a,e1<=b),70,if(and(d1<=a,e1b),100 and so on...... Thank You again, Scott "Bob I" wrote: I'm Bob not Mike, I am just looking at what you say you are trying to accomplish. IF the weight is X and the span is less than Y you may use 70 otherwise you must use 100. Correct? So, this requirement comes from the strength required and the span times the weight sets the minimum tube size to do that. Who made the table up? Where did it come from, there is a mathematical formula that it was created from. You are attempting to figure that formula out by trial and error. Can you get that formula from the creator of the table? Otherwise you will need to play with the numbers to find the line that creates the breakpoint between needing 100 instead of 70. scottgorilla wrote: Mike, If you call it reverse engineering I'll except that. You say determine that number (what number????) and use it as the choice between 100 v 70. I don't think it is weight times span .... so much as span can't exceed weight and then choose between the two. Thank You for your time and response. Scottgorilla "Bob I" wrote: It looks to me like you are attempting to "reverse engineer" the requirements. The 100 vs.70 is going to be required due to the "weight times the span". Determine that number and use it as the choice between 70 or 100. scottgorilla wrote: Dave, Unfortunately it would be nice to say ok use the 100 on anything wider than 72" but it is not cost prohibitive. Thus the reason for trying to create the formula so anyone whom enters the data in the program would not have to have all the knowledge or have to continually look the data up based on size and weight. Have excel read the data and give the solution. I have tried with smaller formulas but unfortunately I'm still getting the same result. It will not pick the correct answer and I can't look over all the employees to make sure they didn't make a mistake because mistakes cost money "Dave Peterson" wrote: It sure looks like you should be changing the table. Maybe... 70 24 100000.000 70 36 3628.000 70 48 2712.950 70 60 2162.080 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 So that any weight greater than or equal to 72 has to return 100. But I'm not sure what happens at 71.99999 Did you try creating the smaller formula to see how that evaluated. scottgorilla wrote: I'm using Excel 2007 and those are named cell references so no need for the double quotations and secondly AQ and AR are on the same sheet. What I am trying to do based on criteria that I have set like as follows if length is for argument sake is 72 and weight is less than 1397.550 the result would be 70 if length is 72 and weight greater than 1397.550 the result would be 100 70 and 100 are diameter of a tube I must use to build my product based on weights and span if length is 133 and weight is less than 122.940 then I can use 70 if the length is 133 and weight is greater than 122.940 then it must be 100. I truly appreciate your assistance and apologize that I seem not to be doing a great job explaining what I'm trying to accomplish. "Dave Peterson" wrote: First, the strings in your formula have to be surrounded by double quotes: =IF(OR(Master!C18="ALUM.050DADE",Master! C18="ALUM.050PALM", Second, it still looks like AQ3:AQ36 and AR3:AR36 are on different sheets. Third, if all the data and formulas are on the same sheet, then the formula doesn't need the sheet references. Fourth, try this simplified formula: =MATCH(1,(AL5<=$AQ$3:$AQ$36)*(AG5=$AR$3 :$AR$36),0) (still an array formula) This returns 11. That's 11 rows into the table--or this line of data. 70 144 122.94 The first time that 139.5 is less than or equal to the values in AQ3:AQ36 and at the same time 336.72 is greater than or equal to the values in AR3:AR36. So that 70 is the correct result for your formula. ===== But I think you've got other problems. In your table, you have: 70 24 100000.000 .... 100 24 100000.000 And there's never going to be a formula that uses =index(match()) that will return 100. The first row found that matches those identical conditions will always be the 70. I'm not sure what you're doing, but maybe you could use two tables (one for 70 and one for 100) or add an extra indicator to know what "group" you want to inspect. But it sure looks like you're deciding to bring back 70 or 100. I don't understand what you really want. scottgorilla wrote: I'm sorry for the confusion Dave but actually I have all of the information and formula on the same sheet now and it still returns the wrong answer..... =IF(OR(Master!C18=ALUM.050DADE,Master!C 18=ALUM.050PALM,Master!C18=STEEL24ga,Master!C18=CL EARPANELS,Master!C18=IMPBAHAMA,Master!C18=IMPCOLON IAL,Master!C18=DECOBAHAMA,Master!C18=DECOCOLONIAL, Master!C18=ACC6_8,Master!C18=ACC6_8BL,Master!C18=A CC6_8HR,Master!C18=ACC6_8HRB,Master!C18=BULLDOG,Ma ster!C18="")," ",INDEX($AP$3:$AP$36,MATCH(1,(AL5<=FORM ULAS!$AQ$3:$AQ$36)*(FORMULAS!AG5=$AR$3:$AR$36),0) )) AL5= 139.50 here is an actual copy of the table 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 100000.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 |
Look up one number based on mulitple criteria!!!!
Glad you have the solution that works for you. If you make a chart of
the two lines you will have a feel for why the table and formula are dissimilar. scottgorilla wrote: In case anyone was interested I found the solution it looks like this =INDEX($AP$3:$AP$36,MATCH(1,(AL3<=FORMULAS!$AQ$3:$ AQ$36)*(FORMULAS!AG3<=$AR$3:$AR$36),0) followed by CONTROL+SHIFT+ENTER Well thanks all for the help Scottgorilla "scottgorilla" wrote: Bob, You deserve a medal for even messing with this. Ok so I tried your formula based on the sizes actually kicked out by my program and it looked like this IF(((AL6^3)*AG6*0.0000007)1000,100,70)) AL6=127.50 which is actually determined by width AG6=308.464 which is determined by sq. ft. times product weight mathematically your formula returns 447.54 which is less than 1000 so it did return 70 but since the 1000 number is a constant and my numbers must fluctuate because of the span how can I get that to happen??? Thank You again "Bob I" wrote: This doesn't check for maximums, only whether 70 can be used. Span is A1 Weight is B1 =IF(((A1^3)*B1*0.0000007)1000,100,70) See if it does what you intended. scottgorilla wrote: Bob, Thank you again for your patience and assistance. The weight and span vary constantly. We are building a custom product that constantly changes based size of the application. So the only constant is the table because these are maximums. Scottgorilla "Bob I" wrote: Ok, then I understand the end result correctly, the issue is that the table is actually discrete points on the line created by that formula. That's the only other way to get there other than listing and comparing values. Are you looking up an arbitrary weight and then a span on the list? Or are the weight and the span both allowed to be any number at all? scottgorilla wrote: Sorry Bob, The list was given to me in PDF format and was done by an engineer that I will not be able to get the calculations from. What I am trying to accomplish is not try and find the fomula for the table but based on results that are determined in another part of my program. I'm am trying to get Excel to pick the appropriate size 70 or 100. If the results are as follows say up to 71.5" and up to 1397.00lbs then pick 70 and again if the results are up to 71.5" and greater than 1397.001 then pick 100 and so on.... It must be possible but I just can't seem to find a way unless I write every possioble scenario like this. IF(and(d1<=a,e1<=b),70,if(and(d1<=a,e1b),10 0 and so on...... Thank You again, Scott "Bob I" wrote: I'm Bob not Mike, I am just looking at what you say you are trying to accomplish. IF the weight is X and the span is less than Y you may use 70 otherwise you must use 100. Correct? So, this requirement comes from the strength required and the span times the weight sets the minimum tube size to do that. Who made the table up? Where did it come from, there is a mathematical formula that it was created from. You are attempting to figure that formula out by trial and error. Can you get that formula from the creator of the table? Otherwise you will need to play with the numbers to find the line that creates the breakpoint between needing 100 instead of 70. scottgorilla wrote: Mike, If you call it reverse engineering I'll except that. You say determine that number (what number????) and use it as the choice between 100 v 70. I don't think it is weight times span .... so much as span can't exceed weight and then choose between the two. Thank You for your time and response. Scottgorilla "Bob I" wrote: It looks to me like you are attempting to "reverse engineer" the requirements. The 100 vs.70 is going to be required due to the "weight times the span". Determine that number and use it as the choice between 70 or 100. scottgorilla wrote: Dave, Unfortunately it would be nice to say ok use the 100 on anything wider than 72" but it is not cost prohibitive. Thus the reason for trying to create the formula so anyone whom enters the data in the program would not have to have all the knowledge or have to continually look the data up based on size and weight. Have excel read the data and give the solution. I have tried with smaller formulas but unfortunately I'm still getting the same result. It will not pick the correct answer and I can't look over all the employees to make sure they didn't make a mistake because mistakes cost money "Dave Peterson" wrote: It sure looks like you should be changing the table. Maybe... 70 24 100000.000 70 36 3628.000 70 48 2712.950 70 60 2162.080 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 So that any weight greater than or equal to 72 has to return 100. But I'm not sure what happens at 71.99999 Did you try creating the smaller formula to see how that evaluated. scottgorilla wrote: I'm using Excel 2007 and those are named cell references so no need for the double quotations and secondly AQ and AR are on the same sheet. What I am trying to do based on criteria that I have set like as follows if length is for argument sake is 72 and weight is less than 1397.550 the result would be 70 if length is 72 and weight greater than 1397.550 the result would be 100 70 and 100 are diameter of a tube I must use to build my product based on weights and span if length is 133 and weight is less than 122.940 then I can use 70 if the length is 133 and weight is greater than 122.940 then it must be 100. I truly appreciate your assistance and apologize that I seem not to be doing a great job explaining what I'm trying to accomplish. "Dave Peterson" wrote: First, the strings in your formula have to be surrounded by double quotes: =IF(OR(Master!C18="ALUM.050DADE",Mast er!C18="ALUM.050PALM", Second, it still looks like AQ3:AQ36 and AR3:AR36 are on different sheets. Third, if all the data and formulas are on the same sheet, then the formula doesn't need the sheet references. Fourth, try this simplified formula: =MATCH(1,(AL5<=$AQ$3:$AQ$36)*(AG5=$A R$3:$AR$36),0) (still an array formula) This returns 11. That's 11 rows into the table--or this line of data. 70 144 122.94 The first time that 139.5 is less than or equal to the values in AQ3:AQ36 and at the same time 336.72 is greater than or equal to the values in AR3:AR36. So that 70 is the correct result for your formula. ===== But I think you've got other problems. In your table, you have: 70 24 100000.000 .... 100 24 100000.000 And there's never going to be a formula that uses =index(match()) that will return 100. The first row found that matches those identical conditions will always be the 70. I'm not sure what you're doing, but maybe you could use two tables (one for 70 and one for 100) or add an extra indicator to know what "group" you want to inspect. But it sure looks like you're deciding to bring back 70 or 100. I don't understand what you really want. scottgorilla wrote: I'm sorry for the confusion Dave but actually I have all of the information and formula on the same sheet now and it still returns the wrong answer..... =IF(OR(Master!C18=ALUM.050DADE,Maste r!C18=ALUM.050PALM,Master!C18=STEEL24ga,Master!C18 =CLEARPANELS,Master!C18=IMPBAHAMA,Master!C18=IMPCO LONIAL,Master!C18=DECOBAHAMA,Master!C18=DECOCOLONI AL,Master!C18=ACC6_8,Master!C18=ACC6_8BL,Master!C1 8=ACC6_8HR,Master!C18=ACC6_8HRB,Master!C18=BULLDOG ,Master!C18="")," ",INDEX($AP$3:$AP$36,MATCH(1,(AL5<=F ORMULAS!$AQ$3:$AQ$36)*(FORMULAS!AG5=$AR$3:$AR$36) ,0))) AL5= 139.50 here is an actual copy of the table 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 100000.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 |
Look up one number based on mulitple criteria!!!!
Bob,
You lost me with the reply what do you mean by???? "If you make a chart of the two lines you will have a feel for why the table and formula are dissimilar." Thanks, Scottgorilla. "Bob I" wrote: Glad you have the solution that works for you. If you make a chart of the two lines you will have a feel for why the table and formula are dissimilar. scottgorilla wrote: In case anyone was interested I found the solution it looks like this =INDEX($AP$3:$AP$36,MATCH(1,(AL3<=FORMULAS!$AQ$3:$ AQ$36)*(FORMULAS!AG3<=$AR$3:$AR$36),0) followed by CONTROL+SHIFT+ENTER Well thanks all for the help Scottgorilla "scottgorilla" wrote: Bob, You deserve a medal for even messing with this. Ok so I tried your formula based on the sizes actually kicked out by my program and it looked like this IF(((AL6^3)*AG6*0.0000007)1000,100,70)) AL6=127.50 which is actually determined by width AG6=308.464 which is determined by sq. ft. times product weight mathematically your formula returns 447.54 which is less than 1000 so it did return 70 but since the 1000 number is a constant and my numbers must fluctuate because of the span how can I get that to happen??? Thank You again "Bob I" wrote: This doesn't check for maximums, only whether 70 can be used. Span is A1 Weight is B1 =IF(((A1^3)*B1*0.0000007)1000,100,70) See if it does what you intended. scottgorilla wrote: Bob, Thank you again for your patience and assistance. The weight and span vary constantly. We are building a custom product that constantly changes based size of the application. So the only constant is the table because these are maximums. Scottgorilla "Bob I" wrote: Ok, then I understand the end result correctly, the issue is that the table is actually discrete points on the line created by that formula. That's the only other way to get there other than listing and comparing values. Are you looking up an arbitrary weight and then a span on the list? Or are the weight and the span both allowed to be any number at all? scottgorilla wrote: Sorry Bob, The list was given to me in PDF format and was done by an engineer that I will not be able to get the calculations from. What I am trying to accomplish is not try and find the fomula for the table but based on results that are determined in another part of my program. I'm am trying to get Excel to pick the appropriate size 70 or 100. If the results are as follows say up to 71.5" and up to 1397.00lbs then pick 70 and again if the results are up to 71.5" and greater than 1397.001 then pick 100 and so on.... It must be possible but I just can't seem to find a way unless I write every possioble scenario like this. IF(and(d1<=a,e1<=b),70,if(and(d1<=a,e1b),10 0 and so on...... Thank You again, Scott "Bob I" wrote: I'm Bob not Mike, I am just looking at what you say you are trying to accomplish. IF the weight is X and the span is less than Y you may use 70 otherwise you must use 100. Correct? So, this requirement comes from the strength required and the span times the weight sets the minimum tube size to do that. Who made the table up? Where did it come from, there is a mathematical formula that it was created from. You are attempting to figure that formula out by trial and error. Can you get that formula from the creator of the table? Otherwise you will need to play with the numbers to find the line that creates the breakpoint between needing 100 instead of 70. scottgorilla wrote: Mike, If you call it reverse engineering I'll except that. You say determine that number (what number????) and use it as the choice between 100 v 70. I don't think it is weight times span .... so much as span can't exceed weight and then choose between the two. Thank You for your time and response. Scottgorilla "Bob I" wrote: It looks to me like you are attempting to "reverse engineer" the requirements. The 100 vs.70 is going to be required due to the "weight times the span". Determine that number and use it as the choice between 70 or 100. scottgorilla wrote: Dave, Unfortunately it would be nice to say ok use the 100 on anything wider than 72" but it is not cost prohibitive. Thus the reason for trying to create the formula so anyone whom enters the data in the program would not have to have all the knowledge or have to continually look the data up based on size and weight. Have excel read the data and give the solution. I have tried with smaller formulas but unfortunately I'm still getting the same result. It will not pick the correct answer and I can't look over all the employees to make sure they didn't make a mistake because mistakes cost money "Dave Peterson" wrote: It sure looks like you should be changing the table. Maybe... 70 24 100000.000 70 36 3628.000 70 48 2712.950 70 60 2162.080 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 So that any weight greater than or equal to 72 has to return 100. But I'm not sure what happens at 71.99999 Did you try creating the smaller formula to see how that evaluated. scottgorilla wrote: I'm using Excel 2007 and those are named cell references so no need for the double quotations and secondly AQ and AR are on the same sheet. What I am trying to do based on criteria that I have set like as follows if length is for argument sake is 72 and weight is less than 1397.550 the result would be 70 if length is 72 and weight greater than 1397.550 the result would be 100 70 and 100 are diameter of a tube I must use to build my product based on weights and span if length is 133 and weight is less than 122.940 then I can use 70 if the length is 133 and weight is greater than 122.940 then it must be 100. I truly appreciate your assistance and apologize that I seem not to be doing a great job explaining what I'm trying to accomplish. "Dave Peterson" wrote: First, the strings in your formula have to be surrounded by double quotes: =IF(OR(Master!C18="ALUM.050DADE",Mast er!C18="ALUM.050PALM", Second, it still looks like AQ3:AQ36 and AR3:AR36 are on different sheets. Third, if all the data and formulas are on the same sheet, then the formula doesn't need the sheet references. Fourth, try this simplified formula: =MATCH(1,(AL5<=$AQ$3:$AQ$36)*(AG5=$A R$3:$AR$36),0) (still an array formula) This returns 11. That's 11 rows into the table--or this line of data. 70 144 122.94 The first time that 139.5 is less than or equal to the values in AQ3:AQ36 and at the same time 336.72 is greater than or equal to the values in AR3:AR36. So that 70 is the correct result for your formula. ===== But I think you've got other problems. In your table, you have: 70 24 100000.000 .... 100 24 100000.000 And there's never going to be a formula that uses =index(match()) that will return 100. The first row found that matches those identical conditions will always be the 70. I'm not sure what you're doing, but maybe you could use two tables (one for 70 and one for 100) or add an extra indicator to know what "group" you want to inspect. But it sure looks like you're deciding to bring back 70 or 100. I don't understand what you really want. scottgorilla wrote: |
Look up one number based on mulitple criteria!!!!
If you chart/graph the table, the resultant lines are not smooth but
jerk around as opposed to the nice continuous line a mathematical expression will create. scottgorilla wrote: Bob, You lost me with the reply what do you mean by???? "If you make a chart of the two lines you will have a feel for why the table and formula are dissimilar." Thanks, Scottgorilla. "Bob I" wrote: Glad you have the solution that works for you. If you make a chart of the two lines you will have a feel for why the table and formula are dissimilar. scottgorilla wrote: In case anyone was interested I found the solution it looks like this =INDEX($AP$3:$AP$36,MATCH(1,(AL3<=FORMULAS!$AQ$ 3:$AQ$36)*(FORMULAS!AG3<=$AR$3:$AR$36),0) followed by CONTROL+SHIFT+ENTER Well thanks all for the help Scottgorilla "scottgorilla" wrote: Bob, You deserve a medal for even messing with this. Ok so I tried your formula based on the sizes actually kicked out by my program and it looked like this IF(((AL6^3)*AG6*0.0000007)1000,100,70)) AL6=127.50 which is actually determined by width AG6=308.464 which is determined by sq. ft. times product weight mathematically your formula returns 447.54 which is less than 1000 so it did return 70 but since the 1000 number is a constant and my numbers must fluctuate because of the span how can I get that to happen??? Thank You again "Bob I" wrote: This doesn't check for maximums, only whether 70 can be used. Span is A1 Weight is B1 =IF(((A1^3)*B1*0.0000007)1000,100,70) See if it does what you intended. scottgorilla wrote: Bob, Thank you again for your patience and assistance. The weight and span vary constantly. We are building a custom product that constantly changes based size of the application. So the only constant is the table because these are maximums. Scottgorilla "Bob I" wrote: Ok, then I understand the end result correctly, the issue is that the table is actually discrete points on the line created by that formula. That's the only other way to get there other than listing and comparing values. Are you looking up an arbitrary weight and then a span on the list? Or are the weight and the span both allowed to be any number at all? scottgorilla wrote: Sorry Bob, The list was given to me in PDF format and was done by an engineer that I will not be able to get the calculations from. What I am trying to accomplish is not try and find the fomula for the table but based on results that are determined in another part of my program. I'm am trying to get Excel to pick the appropriate size 70 or 100. If the results are as follows say up to 71.5" and up to 1397.00lbs then pick 70 and again if the results are up to 71.5" and greater than 1397.001 then pick 100 and so on.... It must be possible but I just can't seem to find a way unless I write every possioble scenario like this. IF(and(d1<=a,e1<=b),70,if(and(d1<=a,e1b), 100 and so on...... Thank You again, Scott "Bob I" wrote: I'm Bob not Mike, I am just looking at what you say you are trying to accomplish. IF the weight is X and the span is less than Y you may use 70 otherwise you must use 100. Correct? So, this requirement comes from the strength required and the span times the weight sets the minimum tube size to do that. Who made the table up? Where did it come from, there is a mathematical formula that it was created from. You are attempting to figure that formula out by trial and error. Can you get that formula from the creator of the table? Otherwise you will need to play with the numbers to find the line that creates the breakpoint between needing 100 instead of 70. scottgorilla wrote: Mike, If you call it reverse engineering I'll except that. You say determine that number (what number????) and use it as the choice between 100 v 70. I don't think it is weight times span .... so much as span can't exceed weight and then choose between the two. Thank You for your time and response. Scottgorilla "Bob I" wrote: It looks to me like you are attempting to "reverse engineer" the requirements. The 100 vs.70 is going to be required due to the "weight times the span". Determine that number and use it as the choice between 70 or 100. scottgorilla wrote: Dave, Unfortunately it would be nice to say ok use the 100 on anything wider than 72" but it is not cost prohibitive. Thus the reason for trying to create the formula so anyone whom enters the data in the program would not have to have all the knowledge or have to continually look the data up based on size and weight. Have excel read the data and give the solution. I have tried with smaller formulas but unfortunately I'm still getting the same result. It will not pick the correct answer and I can't look over all the employees to make sure they didn't make a mistake because mistakes cost money "Dave Peterson" wrote: It sure looks like you should be changing the table. Maybe... 70 24 100000.000 70 36 3628.000 70 48 2712.950 70 60 2162.080 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 So that any weight greater than or equal to 72 has to return 100. But I'm not sure what happens at 71.99999 Did you try creating the smaller formula to see how that evaluated. scottgorilla wrote: I'm using Excel 2007 and those are named cell references so no need for the double quotations and secondly AQ and AR are on the same sheet. What I am trying to do based on criteria that I have set like as follows if length is for argument sake is 72 and weight is less than 1397.550 the result would be 70 if length is 72 and weight greater than 1397.550 the result would be 100 70 and 100 are diameter of a tube I must use to build my product based on weights and span if length is 133 and weight is less than 122.940 then I can use 70 if the length is 133 and weight is greater than 122.940 then it must be 100. I truly appreciate your assistance and apologize that I seem not to be doing a great job explaining what I'm trying to accomplish. "Dave Peterson" wrote: First, the strings in your formula have to be surrounded by double quotes: =IF(OR(Master!C18="ALUM.050DADE",Ma ster!C18="ALUM.050PALM", Second, it still looks like AQ3:AQ36 and AR3:AR36 are on different sheets. Third, if all the data and formulas are on the same sheet, then the formula doesn't need the sheet references. Fourth, try this simplified formula: =MATCH(1,(AL5<=$AQ$3:$AQ$36)*(AG5= $AR$3:$AR$36),0) (still an array formula) This returns 11. That's 11 rows into the table--or this line of data. 70 144 122.94 The first time that 139.5 is less than or equal to the values in AQ3:AQ36 and at the same time 336.72 is greater than or equal to the values in AR3:AR36. So that 70 is the correct result for your formula. ===== But I think you've got other problems. In your table, you have: 70 24 100000.000 .... 100 24 100000.000 And there's never going to be a formula that uses =index(match()) that will return 100. The first row found that matches those identical conditions will always be the 70. I'm not sure what you're doing, but maybe you could use two tables (one for 70 and one for 100) or add an extra indicator to know what "group" you want to inspect. But it sure looks like you're deciding to bring back 70 or 100. I don't understand what you really want. scottgorilla wrote: |
| All times are GMT +1. The time now is 06:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com