Calculate shipping cost with VLOOKUP
The core of the formula is VLOOKUP, which is configured in approximate match mode by setting the fourth argument to 1 or TRUE.
In approximate match mode, the values in the first column of VLOOKUP must be sorted. VLOOKUP will return a value at the first row that is less than or equal to the lookup value.
With weight as the lookup value, VLOOKUP finds and returns the right cost per kg. This cost is then multiplied by the weight to calculate the final cost.
Adding a minimum charge
What if your business rules dictate a minimum shipping cost of $5.00, no matter what the weight? A clever way to handle this is to wrap the entire formula in the MAX function like so:
Now max will return whichever is greater - the result of the formula or 5.