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?
Bookmarks