Tiered Pricing

Looking for an elegant way to handle tiered pricing.

In other words, if you buy <10, your price is $x. But if you buy 11-25, the price is $y, and if you buy 26-50, the price is $z.

This seems to be pretty simple, but doing it is taking on the form of nested IFs.

=IF(A1<11,10,IF(A1<26,9,IF(A1<51,8)))

Something like that. The problem is that the list is rather long, and I'd much rather have a way to look it up instead of having to fix a bunch of nested IFs every time the prices change. What I'd prefer would be a lookup table, where I can say something like, "If the value is between C1:C20 to D1:D20, then the price is E1:E20" (with each individual lookup being on the same row.)

I think it should be do-able through some sort of VLOOKUP, but I don't know how to do it.

Any thoughts?

Hi and welcome to the forum. Please upload the tiered pricing table.

Sometimes if the pricing table has a consitent change from one tier to another it's not even necessary for a Vlookup. However upload the details and we can then offer a suggestion.

depending on the list, you could use a table like structure. So the quantity in one cell and across from it the number, if set up in ascending order and a vlookup.
so for example say the value you are looking up to get the price on is in A2 and the table with 0 in K2 and 10 in L2 and 10 in K3 and 9 in L3 etc. Then a vlookup like
=A2*VLOOKUP(A2,$K$2:$L$4,2,TRUE) should handle your pricing.

couple ways.

You can manually enter them in a Lookup formula
=lookup(A1,{10,26,51},{10,9,8}) etc etc

or you can make a table to do a vlookup up off and use TRUE for [range lookup]



EDIT: I'm half asleep

@dosydos, I believe you will have to use a TRUE on a vlookup in order for it to work, otherwise with FALSE it will look for exact matches and values not in the range will return #N/A.

Originally Posted by Sambo kid

@dosydos, I believe you will have to use a TRUE on a vlookup in order for it to work, otherwise with FALSE it will look for exact matches and values not in the range will return #N/A.

wow... i must be asleep at my desk as that was a poor mistake. Fixed it in my original. nice catch sambo

Not a problem, we all make mistakes, I've made my share of sleep deprived mistakes.

Glad I (we) could help, AND thank you for the rep!