formula finding price based on volume bracket

[thrava]

I want to write a formula, say in cell E2 that picks the
correct price based on valume Entered in Cell D1.

Suppose I have a table set up with volume vs price. sush
as:

Column A B C D E
Row 1 From TO PRICE Volume Entered ??
Row 2 500 600 $1.20
Row 3 601 700 $0.99

Etc... down the row. I know I can use vlookup ordinarily
but now there are two columns. If volumn is between 500
to 600 then the price is one thing, if volumn is between a
different braket is something else.........
What is the best formula to use for this?

I used one of the suggestions: =vlookup(D1,A2:C100,3,1),
however, it gives me wrong answers.
Any other help will be appreciated. Thanks

 

[duane]

I dont know exactly how your sheet is laid out but I think this ma
work

=vlookup(D1,c2:e100,3,1)

 

[Max]

A restructuring of your price table
would enable use of VLOOKUP ..

In Sheet1
-------------
Assume you have the price table below in A1:B5
(where "Vol" in col A is in ascending order)

Vol UnitPrice
1 $1.50
500 $1.20
600 $0.99
700 $0.80

The above pricing structure means:

Vols <500, $1.50
Vols >=500 and <600, $1.20
Vols >=600 and <700, $0.99
Vols >=700 and above, $0.80

In Sheet2
-------------
Assuming the order vols
will be listed in col A, row2 down

To extract the correct unit prices for the vols in col A:

Put in B2: =VLOOKUP(A2,Sheet1!$A$2:$B$5,2,TRUE)
Copy down

 

[Max]

Vols >=700 and above, $0.80
sounds a little redundant ..

should read better as:

 

[thrava]

thank you much Max.

I'll implement it and if there are problems, I'll repost.
thanks again

 

[Max]

Pleasure' Thrava !
Thanks for the feedback

 

[Thrava]

I've tested this Max,and your restructured table works
like a charm.

Thank you so much.
Thrava

 

[Max]

Glad to hear that, Thrava!
The feedback is appreciated ..