Excel 2007 : using excel to figure costs if variable in many different ways

[clonken4]

I'm not sure that my title is the greatest, but here goes:
I am trying to use excel to help me calculate snow removal cost for 30 different customers. We have 4 basic contract types, but each customers price is different depending on the size of their property. Pricing also changes for each contract type at a different rate depending on the total snowfall. I have all the data and variables in a separate "Rate Table" and a Template for event reporting. I want to be able to input the total snowfall and have excel populate the amount fields in my copied worksheet correctly so I can use that sheet to generate invoicing. I'm just not sure what my "holy grail" formula will be to get the amounts right. here are 2 examples of the kinds of differences:

Customer A has a standard contract. for 1-3" of snow he pays $25.00 for 4-6" he pays $35.00 for 7-9" he pays $45.00 etc. (until 12"+ then it changes to an hourly rate(which I'm not teriibly worried about having excel calculate)
Customer B has a Standard plus contract. for 1-4" she pays 40.50 and $10.00 per inch after that. (the 12"+ hourly rate applies to all customers)
Customer C has a Premium contract. 1-6" they pay $42.00 and $10.00 per inch after that. (with the hourly rate at 12"+ )

If I get 5" of snow then the following is true
Customer A owes $35.00
Customer B owes $50.50
Customer C owes $42.00

But if I get 7" then the following is true
Customer A owes $45.00
customer B owes $70.50
Customer C owes $52.00


I hope this is enough information.

Thanks in advance for any help!

[clonken4]

Actually, now that I have fully filled out the rate table I need a way for excel to pull the information once I input the number of inches of accumulation. I could use a vlookup and change the formula for every event, but I have a feeling there is an easier way!