How to calculate shipping cost by weight in Excel

Q: How do I add a per-pound rate above the top limit?

Use IF: =IF(E1>50,39.9+(E1-50)*0.6,XLOOKUP(E1,A2:A7,B2:B7,,1)).

Q: How do I use dimensional weight?

Look up the larger of the two: =XLOOKUP(MAX(E1,L*W*H/139),A2:A7,B2:B7,,1), with the box dimensions in inches.

Formula (Excel and Google Sheets)

=XLOOKUP(E1,A2:A7,B2:B7,"Freight quote",1)

With a rate table of weight limits ("up to"), use XLOOKUP match mode 1, exact or next larger: =XLOOKUP(E1,A2:A7,B2:B7,"Freight quote",1).

Result on the example: $10.95 in E2
Works in: Excel 2021, 2024 and Microsoft 365
Google Sheets: Same formula

See it work

The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.

How it works

Carriers price by weight limit: up to 1 lb, up to 2 lb, up to 5 lb. A 3.4 lb package needs the first limit that is at least 3.4, which is 5 lb. Match mode 1 returns the exact match or the next larger value, so it picks the 5 lb row, $10.95. Anything over 50 lb returns "Freight quote".

=XLOOKUP(E1,A2:A7,B2:B7,"Freight quote",1)

Result10.95

E13.4
The value in E1.
A2:A76 rows × 1 col: 1, 2, 5, 10, …
A range: 6 cells in one column.
B2:B76 rows × 1 col: $5.50, $7.25, $10.95, $15.80, …
A range: 6 cells in one column.
XLOOKUP(E1,A2:A7,B2:B7,"Freight quote",1)10.95
XLOOKUP: Searches one range and returns the matching item from another.
- lookup_valueE13.4
- lookup_arrayA2:A76 rows × 1 col: 1, 2, 5, 10, …
- return_arrayB2:B76 rows × 1 col: $5.50, $7.25, $10.95, $15.80, …
- if_not_found"Freight quote"
- match_mode1

In Google Sheets

The same formula works in Google Sheets: =XLOOKUP(E1,A2:A7,B2:B7,"Freight quote",1). We ran it in Google Sheets mode on the same data and it returns $10.95.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=INDEX(B2:B7,MATCH(TRUE,INDEX(A2:A7>=E1,0),0))	$10.95	Every Excel version: the first limit that is at least the weight, in a table sorted from lightest to heaviest.

Common mistakes

VLOOKUP with TRUE rounds down to the 2 lb row and charges $7.25. It fits tables of lower bounds ("from"), not limits ("up to").
Carriers often bill whole pounds, rounding up. Round the weight first: =XLOOKUP(ROUNDUP(E1,0),A2:A7,B2:B7,"Freight quote",1).

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate shipping cost by weight

Each row of the rate table covers packages up to that weight. In E2, return the shipping cost for the weight in E1.

Questions

How do I add a per-pound rate above the top limit?

Use IF: =IF(E1>50,39.9+(E1-50)*0.6,XLOOKUP(E1,A2:A7,B2:B7,,1)).

How do I use dimensional weight?

Look up the larger of the two: =XLOOKUP(MAX(E1,L*W*H/139),A2:A7,B2:B7,,1), with the box dimensions in inches.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.