This formula uses the weight to find the correct “cost per kg” then calculates the final cost. 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
