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.

## Leave a Comment