Go Back

Learn How to Calculate Shipping Cost with VLOOKUP

We can use the VLOOKUP function to calculate the shipping cost for a specified weight of an item. We can also apply the MAX function to VLOOKUP in situations where we have a fixed minimum shipping cost no matter the weight.

Figure 1: Result of the VLOOKUP function for Calculating Shipping Cost

Setting up the Data

  • We will set up our data by inputting the weight of items and the corresponding cost per kilogram in Column A and Column B respectively

Figure 2: Data for Calculating Shipping Cost with VLOOKUP

Syntax for Calculating Shipping Costs using VLOOKUP

=VLOOKUP(lookup_value,table_array,col_index_number,{range_lookup}* lookup_value


  • Lookup Value

This is the weight (the cell containing the weight) that we are interested in. For instance, if we want to find the cost of a 6 kg item, our lookup value will be will be 6

  • Table Array

This is the range of values from where we will retrieve our value contained in the data. It is noteworthy that we must have at least two columns of data. Our range, in this case, is A4:B8.

  • Column Index Number

This is the number of columns from where we will retrieve our data. In this case, we have just two columns; hence, our column index number is 2.

  • Range Lookup

This will return values as TRUE or FALSE. TRUE (1) means that we want an approximate match and FALSE (2) implies that we want an exact match. We will opt for the approximate match because VLOOKUP is configured here by using the approximate match mode. 

Lookup Cost for 6kg

We will name a Cell that will contain the weight and have an empty cell where our formula will return our lookup value.

  • Cell C5 will be called weight
  • Cell D5 will contain the weight (6kg) whose total cost we intend to calculate
  • Cell C6 will contain the total cost
  • Cell D6 is where we will input our formula

Figure 3: Calculating Shipping Cost with VLOOKUP for 6kg

Based on the explanation of the syntax above, our formula is:


We will input this formula into Cell D6

Figure 4: Inputting the VLOOKUP formula

We will now press ENTER to get the result.

Figure 5: Result of the VLOOKUP

The result has provided the amount for the total cost of shipping 6kg of an item.


The formula is set up such that it would return the value specified in the first row, which turns out to be slightly lesser than or equal to the value retrieved by the lookup value (the weight, 6kg). This simply means that the formula works based on the content of the first row (Cell A4 and Cell B4).  

Using the MAX function

We can use the MAX function for situations where there is a minimum fixed cost no matter the weight. We must remember that the least weight allowed for shipping is 1 kg as shown on the figures. Assuming the minimum fixed cost no matter the weight is $40, the formula becomes this below:


Figure 6: Calculating Shipping Cost with VLOOKUP and MAX function

Press Enter after inserting the formula

Figure 7: Result of Shipping Cost with VLOOKUP and MAX function

If we change the weight to 1 kg in Cell D5, the total cost becomes $40 because that is the least acceptable cost.

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

need help with an IF formula to calculate shipping cost
Solved by C. C. in 29 mins
In column K, enter a formula to calculate the total cost, which is the sum of the rental charges, the insurance cost and the shipping cost.
Solved by I. D. in 26 mins
I need help finding a formula for an order form that will calculate the shipping cost depending on what the order total is. Our shipping costs vary depending on order total. if order is between $0-$50 then shipping costs $10, $50-$100 =$15, $100-$150=$20, and anything over $150=Free Shipping.
Solved by V. J. in 22 mins

Leave a Comment