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
Explanation
- 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:
=VLOOKUP(D5,A4:B8,2,1)*D5
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.
Note
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:
=MAX(VLOOKUP(D5,A4:B8,2,1)*D5,40)
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.
Are you still looking for help with the VLOOKUP function? View our comprehensive round-up of VLOOKUP function tutorials here.
Leave a Comment