If Value is >= X & <= Y, then $1234

[Fattie]

Okay very simple problem here. I have a unit sold volume that changes. When said volume changes, as does the price. We have 5 pricing brackets.

0-59 $1000 ea
60-119 $2000 ea
120-179 $3000 ea
180-399 $4000 ea
400-999 $5000 ea

I have one cell that changes in unit volume and I want the adjacent cell to spit out the corresponding price. Based on the pricing matrix above, what is the formula that identifies the one changing unit volume cell and kicks out it's correct price?

Thank you in advance

[wmorrison49]

Is this what you had in mind?

[NBVC]

If you have the ranges split so that the bottoms are in, say A1:A5 and the tops are in B1:B5 and the values are in C1:C5, then try

=Lookup(X1,A1:C5)

where X1 is the cell containing the value to look up.

[Ron Coderre]

With
A1:B6 containing these values:

Please Login or Register  to view this content.

and
C1: (a quantity to purchase....eg 59)

This formula returns the unit price:

Please Login or Register  to view this content.

Is that something you can work with?
Post back with more questions.