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.

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

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

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

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

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

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,

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

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

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