If statement with range and varying false results

QuestionEdit

How do I write an IF statement that falls within a range? I am trying to create a formula that calculates additional charge in a range per set dollar increments. Example: If our subtotal is less than $500, charge $0, $501-1000 charge $100, $1001-1500 charge $200, $1501-2000 charge $300, over $2000 charge $0.

AnswerEdit

Kristyna,

You would set up a table like this:

0 0 501 100 1001 200 1501 300 2001 0

assume that table is in Sheet2 starting in A2

assume you enter 600 in cell B9 of sheet1, then in C9 you would have

=if(B9<>"",vlookup(B9,Sheet2!$A$2:$B$6,2,True),"")

that should produce the results you describe.

the 4th argument of True tells it to match the highest value less than or equal to the value being looked up.