VLookup with criteria

[niladrib]

Hello all

I have below table. Now


No. of ads each Price of ads
1-5 $12000
6-10 $11000
11-20 $10000
20 or higher $9000

For example, if you buy 8, ads you pay $12000 per ad for the first 5 ads and $11,000 for each of the next 3ads. If you buy 14 ads, you pay $12,000 for each of the first 5 ads, $11,000 for each of the next 5 ads, and$10,000 for each of the last 4 ads write a formula that yields the total cost of purchasing any number ofads.

How to solve this problem with Vlookup?

Can any one help me on this?

 

[Aaron Blood]

What you are asking about is a tiered pricing example.

The easiest way to do that would be to create a 20x3 table where:
Column 1 is the add count from 1 to 20
Column 2 is the cost of the add for that count
Column 3 is a running total of Column 2

So with that, let's assume you create a table in range A1:C20 that looks like this:

1	12000	12000
2	12000	24000
3	12000	36000
4	12000	48000
5	12000	60000
6	11000	71000
7	11000	82000
8	11000	93000
9	11000	104000
10	11000	115000
11	10000	125000
12	10000	135000
13	10000	145000
14	10000	155000
15	10000	165000
16	10000	175000
17	10000	185000
18	10000	195000
19	10000	205000
20	9000	214000

Assuming cell E1 contains the number of adds purchased:
=VLOOKUP(E1,A1:C20,3)+VLOOKUP(E1,A1:C20,2)*MAX(E1-20,0)

There may be fancier array formula solutions... Personally, I like the layout and simplicity of this construct. Usually tiered pricing structures change from time-to-time and this layout is very easy to maintain, update, and audit. If we were dealing with price-quantity discounts for orders numbering in the thousands, obviously we might want to rethink our pricing table structure a bit.

 

[Aaron Blood]

Funny how my brain works... just noticed I spelled ads "adds"

Ah well, you know what I meant

 

[Aladin Akyurek]

Does this alternative....

=SUMPRODUCT(--(F2>{0;5;10;20}), (F2-{0;5;10;20}),{12000;-1000;-1000;-1000})

satisfy, where F2 houses the number of ads like 8?