Vlookup formula – the shipping cost calculator

The VLOOKUP function can be used to calculate shipping cost based on specified weight of an item in Excel.

How to calculate shipping cost based on certain weight in Excel?

Please create a table containing two lists “Weight” and “Cost” as the below sample table shown. In this case, to calculate the total shipping cost based on the specified weight in F4, please do as follows.

Generic formula

=VLOOKUP(lookup_value,table_array,col_index,[range_lookup])*lookup_value

Arguments

• Lookup_value: The criteria value you are searching for. It must be in the first column of the table_array range.
• Table_array: The table contains two or more columns where the lookup value column and the result value column locating.
• Col_index: The specific column number (it is an integer) of the table_array, which you will return the matched value from.
• Range_lookup: This is a logical value that determines whether this VLOOKUP function will return an exact match or an approximate match.

1. Select a blank cell to output the result (here I select F5), copy the below formula into it.

=VLOOKUP(F4,B3:C7,2,1)*F4

2. Press the Enter key to get the total shipping cost.

Notes: in the above formula

• F4 is the cell containing the specified weight you will calculate the total shipping cost based on;
• B3:C7 is the table_array containing the weight column and the cost column;
• Number 2 represents the cost column, which you will return the matched cost from;
• Number 1 (can be replaced with TRUE) means that it will return an approximate match if an exact match cannot be found.

How this formula works

• Because of the approximate match mode, the values in the first column must be sorted in ascending order in case of returning the incorrect value.
• Here the formula =VLOOKUP(F4,B3:C7,2,1) lookups the cost for certain weight 7.5 in the first column of the table range. As 7.5 haven’t been found, it matches the next smallest weight 5 and gets its cost \$7.00. And this cost is finally multiplied by the certain weight 7.5 to get the total shipping cost.

How to calculate shipping cost with minimum charge in Excel?

Supposing the shipping company has a rule stipulates that the minimum shipping cost is \$7.00 no matter what the weight is. Here is the formula for you:

=MAX(VLOOKUP(F4,B3:C7,2,1)*F4,7)

Applying the above formula, the result of the total shipping cost will no less than \$7.00.

Related functions

The VLOOKUP function
The Excel VLOOKUP function searches for a value by matching on the first column of a table and returns the corresponding value from a certain column in the same row.

Related formulas

Lookup values from another worksheet or workbook
This tutorial explains how to use VLOOKUP function to search for values from another worksheet or workbook.
Click to know more...

Lookup values across multiple worksheets with dynamic sheet name
In many cases, you may need to collect data across multiple worksheets for summary. This article provides methods for you to easily lookup specific values across worksheets with dynamic sheet name.
Click to know more...

Vlookup and return matched values in multiple columns
Normally, applying the Vlookup function can only return the matched value from one column. Sometimes, you may need to extract matched values from multiple columns based on the criteria. Here is the solution for you.
Click to know more...

Vlookup to return multiple values in one cell
Normally, when applying the VLOOKUP function, if there are multiple values that match the criteria, you can only get the result of the first one. If you want to return all matched results and display them all in a single cell, how can you achieve?
Click to know more...

Vlookup and return entire row of a matched value
Normally, using the vlookup function can only return a result from a certain column in the same row. This article is going to show you how to return the whole row of data based on specific criteria.
Click to know more...

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

 🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions… Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ... Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup... Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List... Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Columns to Select Same & Different Cells ... Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ... Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

• One second to switch between dozens of open documents!
• Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
• Increases your productivity by 50% when viewing and editing multiple documents.
• Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.