Microsoft Excel is regarded as one of the universal tools for calculating shipping costs using simple and easy-to-use functions. It features a VLOOKUP function, which enables the users to estimate the overall shipping costs easily. The following simple and easy-to-follow steps show that how you can calculate shipping costs with VLOOKUP
How to calculate shipping cost with VLOOKUP:
Figure 1. Example of calculating shipping cost with VLOOKUP
Formula for Shipping Costs using VLOOKUP:
Explanation of VLOOKUP:
The VLOOKUP function is primarily used for performing a vertical lookup. It searches for a value mentioned in the first column of Excel table, and it returns the value in the row with respect to its index_number position.
The generic formula given above uses weight for estimating the “cost per kg” before it calculates the final shipping cost.
How does the formula work?
The generic formula of the VLOOKUP function comprises of weight, table, and column, which is further supplemented by placing before the given essentials. VLOOKUP serves as the central part of the formula, and it is configured by using the approximate match mode.
The approximate match mode is primarily used for sorting out the values mentioned in the first column prescribed to the VLOOKUP formula. The formula is set up in such a way that it would return the value specified in the first row, which turns out to be slighter lesser than or equal to the value retrieved by the lookup value.
Weight is an essential component of the lookup formula. The VLOOKUP formula is used for finding and calculating the cost per kg. The cost is again multiplied by the weight, which results in calculating the final cost required for shipping the items.
Keeping the above example in mind, it can be further elaborated as follows:
Figure 2. Calculate shipping cost with VLOOKUP
As you can see, the value of weight in F7 is repeated twice, which is further multiplied with the value of cost given in F8. If you pay attention to the core of the formula, it uses a summation of the values given in Column B and Column C respectively.
Changing the values or shipping charges:
What if a business were to add a specific and fixed amount for shipment, regardless of the weight? It could require a user to use the following specified formula for ensuring any changes. For instance, if we add $5 to the previous shipping cost, it would acquire the following results:
Figure 3. Adding extra shipping costs for calculating shipping cost with VLOOKUP
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.