Formula for Greater , LEss than

[Preatorian]

Hi,

I have this table with min and max amounts that requires a fixed amount when when the condition is met.

How do I write a formula for this. If result is >$0 but < $100 = $15 and so on...

I canlt get it right..

Min Max Equals
$0 $100 $15
$101 $200 $20
$201 $1,000 $ 30
$1,001 $3,500 $50
$3,501 $10,000 $75
$10,001 $50,000 $120
$50,001 $100,000 $180
$100,001 and over $230

[Shijesh Kumar]

u can achieve this by using using sumproduct formula..
see the attachement

[shg]

=lookup(a1, {0,100,200,1000,3500,10000,50000,100000} + 0.01, {15,20,30,50,75,120,180,230})

You can delete the + 0.01part if the fee for $100 is $20 rather than $15.

[Preatorian]

Thanks fellas. Both work great. Had no idea this can be achieved in so many different ways...