# How to calculate shipping cost by weight in Excel

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)`.

**Excel:** `=XLOOKUP(E1,A2:A7,B2:B7,"Freight quote",1)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Up to (lb) | Cost |  | Package weight (lb) | 3.4 |
| 1 | 5.5 |  | Shipping cost |  |
| 2 | 7.25 |  |  |  |
| 5 | 10.95 |  |  |  |
| 10 | 15.8 |  |  |  |
| 20 | 24.4 |  |  |  |
| 50 | 39.9 |  |  |  |

Formula in E2: `=XLOOKUP(E1,A2:A7,B2:B7,"Freight quote",1)`

Result: $10.95

## 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".

## Other ways

- `=INDEX(B2:B7,MATCH(TRUE,INDEX(A2:A7>=E1,0),0))`: 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)`.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/shipping-cost-by-weight