Need a formula to solve only by using Hlookup or Vlookup or both

[Nitin@210888]

You are thinking of advertising Microsoft products on a popular TV music program. You pay
one price for the first group of ads, but as you buy more ads, the price per ad decreases as
shown in the following table.

Ad number Price per ad
1–5 $12,000
6–10 $11,000
11–20 $10,000
21 or higher $9,000

For example, if you buy 8 ads, you pay $12,000 per ad for the first 5 ads and $11,000 for each of the next 3 ads. 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 of ads. Hint: You probably need at least three columns in your table range, and your formula might involve two lookup functions.

 

[AlanSidman]

Assume your number of adds purchased is in C2, then in D2 =IF(C2<5, C2*12000,IF(AND(C2>5,C2<11),60000+(C2-5)*11000,IF(AND(C2>10,C2<21),115000+(C2-10)*10000,215000+(C2-20)*9000)))

 

[bosco_yip]

Assume your number of adds purchased is in C2, then in D2 =IF(C2<5, C2*12000,IF(AND(C2>5,C2<11),60000+(C2-5)*11000,IF(AND(C2>10,C2<21),115000+(C2-10)*10000,215000+(C2-20)*9000)))

1] Alan's formula is working fine except when C2 =5

I think the formula missing a "=" highlighted in red color as followings :

=IF(C2<=5, C2*12000,IF(AND(C2>5,C2<11),60000+(C2-5)*11000,IF(AND(C2>10,C2<21),115000+(C2-10)*10000,215000+(C2-20)*9000)))

2] Another option formula,

In D2 :

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

Regards
Bosco

 

[vletm]

Nitin@210888 -
I hope that Your ONLY means really ONLY
... then even IF isn't possible to use!