Hi folks

Here is a cut down version of a lookup im using to find the best choice for
a customer, as you can see from the row that starts 30 the customer requires
650 units and require the extra A and extra B package. The extra cost for 50
units on the 30 package is 25p (pence) therefore with the extra's this works
out at a total of £39.25.

What I need is a formula to lookup this value but then also show the next
best value plan, in this case it would be the 40 package as they would get
1100 units instead of 600 and as extra A & B are free on this package the
additional cost is only 75p


core plan core units core cost extra A extra B units req. extra cost total
cost
40 1100 £40.00 free free £40.00
35 850 £34.50 £5.00 free £39.50
30 600 £29.00 £5.00 £5.00 650 £0.25 £39.25
25 400 £24.75 £5.00 £5.00 £25.75
20 200 £20.00 £5.00 £5.00 £20.00

Hope this make sense

Nelly

I don't understand your criteria for "next best" plan. It seems to me that
the 35 plan would be next best, since it's only 25p more as opposed to 75p
more. A little more clarification would be helpful in order to suggest a
formula.

HTH
Elkar


"nelly" wrote:

Hi folks

Here is a cut down version of a lookup im using to find the best choice for
a customer, as you can see from the row that starts 30 the customer requires
650 units and require the extra A and extra B package. The extra cost for 50
units on the 30 package is 25p (pence) therefore with the extra's this works
out at a total of £39.25.

What I need is a formula to lookup this value but then also show the next
best value plan, in this case it would be the 40 package as they would get
1100 units instead of 600 and as extra A & B are free on this package the
additional cost is only 75p


core plan core units core cost extra A extra B units req. extra cost total
cost
40 1100 £40.00 free free £40.00
35 850 £34.50 £5.00 free £39.50
30 600 £29.00 £5.00 £5.00 650 £0.25 £39.25
25 400 £24.75 £5.00 £5.00 £25.75
20 200 £20.00 £5.00 £5.00 £20.00

Hope this make sense

Nelly

Hi yes you are correct in that fact but value for money the 75p is greater
but in short if I could just get a formula to look up the 35 plan in this
case case that would be great!

"Elkar" wrote:

I don't understand your criteria for "next best" plan. It seems to me that
the 35 plan would be next best, since it's only 25p more as opposed to 75p
more. A little more clarification would be helpful in order to suggest a
formula.

HTH
Elkar


"nelly" wrote:

Hi folks

Here is a cut down version of a lookup im using to find the best choice for
a customer, as you can see from the row that starts 30 the customer requires
650 units and require the extra A and extra B package. The extra cost for 50
units on the 30 package is 25p (pence) therefore with the extra's this works
out at a total of £39.25.

What I need is a formula to lookup this value but then also show the next
best value plan, in this case it would be the 40 package as they would get
1100 units instead of 600 and as extra A & B are free on this package the
additional cost is only 75p


core plan core units core cost extra A extra B units req. extra cost total
cost
40 1100 £40.00 free free £40.00
35 850 £34.50 £5.00 free £39.50
30 600 £29.00 £5.00 £5.00 650 £0.25 £39.25
25 400 £24.75 £5.00 £5.00 £25.75
20 200 £20.00 £5.00 £5.00 £20.00

Hope this make sense

Nelly

Ok, well I'm still unclear on the details, but see if this is something you
can work with.

=INDEX(A2:H6,MATCH(A1,H2:H6,-1),1)

This assumes your plans are stored in cells A2:A5, your total costs are
stored in cells H2:H6 and your current cost (39.25) is stored in cell A1.
Adjust these cell references to meet your needs.

HTH
Elkar


"nelly" wrote:

Hi yes you are correct in that fact but value for money the 75p is greater
but in short if I could just get a formula to look up the 35 plan in this
case case that would be great!

"Elkar" wrote:

I don't understand your criteria for "next best" plan. It seems to me that
the 35 plan would be next best, since it's only 25p more as opposed to 75p
more. A little more clarification would be helpful in order to suggest a
formula.

HTH
Elkar


"nelly" wrote:

Hi folks

Here is a cut down version of a lookup im using to find the best choice for
a customer, as you can see from the row that starts 30 the customer requires
650 units and require the extra A and extra B package. The extra cost for 50
units on the 30 package is 25p (pence) therefore with the extra's this works
out at a total of £39.25.

What I need is a formula to lookup this value but then also show the next
best value plan, in this case it would be the 40 package as they would get
1100 units instead of 600 and as extra A & B are free on this package the
additional cost is only 75p


core plan core units core cost extra A extra B units req. extra cost total
cost
40 1100 £40.00 free free £40.00
35 850 £34.50 £5.00 free £39.50
30 600 £29.00 £5.00 £5.00 650 £0.25 £39.25
25 400 £24.75 £5.00 £5.00 £25.75
20 200 £20.00 £5.00 £5.00 £20.00

Hope this make sense

Nelly

Ok, well I'm still unclear on the details, but see if this is something you
can work with.

=INDEX(A2:H6,MATCH(A1,H2:H6,-1),1)

This assumes your plans are stored in cells A2:A5, your total costs are
stored in cells H2:H6 and your current cost (39.25) is stored in cell A1.
Adjust these cell references to meet your needs.

HTH
Elkar


"nelly" wrote:

Hi yes you are correct in that fact but value for money the 75p is greater
but in short if I could just get a formula to look up the 35 plan in this
case case that would be great!

"Elkar" wrote:

I don't understand your criteria for "next best" plan. It seems to me that
the 35 plan would be next best, since it's only 25p more as opposed to 75p
more. A little more clarification would be helpful in order to suggest a
formula.

HTH
Elkar


"nelly" wrote:

Hi folks

Here is a cut down version of a lookup im using to find the best choice for
a customer, as you can see from the row that starts 30 the customer requires
650 units and require the extra A and extra B package. The extra cost for 50
units on the 30 package is 25p (pence) therefore with the extra's this works
out at a total of £39.25.

What I need is a formula to lookup this value but then also show the next
best value plan, in this case it would be the 40 package as they would get
1100 units instead of 600 and as extra A & B are free on this package the
additional cost is only 75p


core plan core units core cost extra A extra B units req. extra cost total
cost
40 1100 £40.00 free free £40.00
35 850 £34.50 £5.00 free £39.50
30 600 £29.00 £5.00 £5.00 650 £0.25 £39.25
25 400 £24.75 £5.00 £5.00 £25.75
20 200 £20.00 £5.00 £5.00 £20.00

Hope this make sense

Nelly