Quick, clean, and to the point

Calculate shipping cost with VLOOKUP

Excel formula: Calculate shipping cost with VLOOKUP
Generic formula 

To calculate shipping cost based on weight, you can use the VLOOKUP function. In the example shown, the formula in F8 is:


This formula uses the weight to find the correct "cost per kg" then calculates the final cost.


The core of the formula is VLOOKUP, which is configured in approximate match mode by setting the forth 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.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.