Difficult Formula lookup

N

nelly

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
 
E

Elkar

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
 
N

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!
 
E

Elkar

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
 
E

Elkar

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple lookup values and adding multiple rates across together 5
LOOKIN G FOR EMPLOYMENT LOOK HERE!!!!!!!!!! 1
Outlook crashes with Outlook connector 2002 0
Outlook connector crashes outlook 0

Top