Formula for Trucking Calculator|General Excel Questions & Answers|Excel Forum|My Online Training Hub

HI!

I am creating a trucking calculator in which the user enters a weight, and the amount to pay the trucker is displayed. The user selects the town from a drop down list on a second sheet named "Data"picking the town, and it calculates the rate depending on the town.

I have a VLOOKUP table on sheet 2 (Data) which lists the towns and their rates. There are only 2 rates which I divided into groups (1 and 2), and a third option, “Special” rate which will only show the word special. The columns in the VLOOKUP is:
Column 1: town name,
Column 2: rate
Column 3: either the number 1,or 2 (which classifies the towns into 2 zones), or the word “Special”.

on the main sheet, cell D8 has the drop down where the user picks the town. 

I8 has the rate from the VLOOKUP which pulls from the town. 

And K8 has the group number also pulling from VLOOKUP.

Cell D13 has the total calculated which multiplies the rate (I8) x weight entered in D4

so far I have the calculator showing the rate once the weight, or the word “Special” if it is a special rate:

=IF(I8=”SPECIAL”, “SPECIAL”, D4*I8)

Now I want to use MAX because there are minimum fees associated. So if the value that is calculated in E13 is less than 26.25, it will display 26.25 instead of the total.
now, the minimum in zone 1 (26.25) is different that zone 2 (29.40).

I need to create a formula that can identify what zone is being used, which can be identified by the number 1, or 2 in cell K8, and apply that minimum charge depending on it.

I attached the spreadsheet