Look up a shipping rate by weight band in Excel with VLOOKUP
Parcels almost never weigh exactly 1, 2.5, or 5 kg — but the rate card is priced at those weights. The lookup that prices a parcel is an approximate match: it finds the band the parcel's weight falls into, and charges that band's rate.
Written 12 November 2026
Result
| Parcel P-01, 0.4 kg | $6.95 |
| Parcel P-04, 7.5 kg | $14.60 |
| Parcel P-07, 31 kg | $49.90 |
| All seven parcels | $146.80 |
Download the spreadsheet: shipping-rate-by-weight.xlsx (5,375 bytes)
It contains the rate card and the seven parcels, each with its rate filled in by the formula.
1What you need
- Excel, Google Sheets, or LibreOffice. VLOOKUP works the same in all three for this tutorial.
- The file above, or a blank workbook if you want to type the formulas in yourself.
2The rate card
The carrier prices by weight band: a parcel pays the rate for the band it falls into, and a band starts at its minimum weight. Seven bands cover every parcel the shop sends:
| Min weight (kg) | Rate |
|---|---|
| 0 | $6.95 |
| 1 | $8.40 |
| 2.5 | $10.90 |
| 5 | $14.60 |
| 10 | $21.30 |
| 20 | $34.75 |
| 30 | $49.90 |
Two things about this table matter. The minimum weights run in ascending order, smallest first — an approximate-match lookup assumes the first column is sorted, and Microsoft's guidance is explicit that the return value might be something you do not expect when it is not (VLOOKUP function — Microsoft Support). And the first band starts at 0, for a reason step 5 comes back to.
3Price one parcel
The parcels sit beside the card, in columns D to F. The rate for the first parcel is one formula:
F4 =VLOOKUP(E4,$A$4:$B$10,2,TRUE)Read it left to right: look up this parcel's weight (E4), in the rate card ($A$4:$B$10), return the value from column 2 of the card, and TRUE asks for an approximate match. The dollar signs pin the card in place, so the formula can be filled down the parcel list. Parcel P-01 weighs 0.4 kg and returns $6.95.
4What approximate match actually picks
An approximate match does not round to the nearest band. It returns the rate for the largest band start that is not above the parcel's weight. Parcel P-03 weighs 3.2 kg: no band starts at 3.2, so the formula settles on the 2.5 band and returns $10.90. Parcel P-04 at 7.5 kg lands in the 5 band, $14.60. Parcel P-07 at 31 kg lands in the top band, $49.90 — every weight from 30 kg up pays the top rate, because there is no band above it to fall into.
The exact-match setting, FALSE, would fail here: with no band starting at exactly 3.2 kg, it returns #N/A. Band tables are the job approximate match exists for.
5Why the card starts at 0
With TRUE, a lookup value smaller than the smallest value in the first column returns the #N/A error (VLOOKUP function — Microsoft Support). A card that started at 1 kg would leave every parcel under a kilo unpriced. Starting the first band at 0 closes that hole: the lightest parcel the shop can send still lands in a band.
6The total
The bottom cell adds the seven rates:
F11 =SUM(F4:F10)Seven parcels, $146.80 in total. Change any parcel's weight and its rate — and the total — follow on their own.
Traps
Bands out of order. Approximate match assumes the first column is sorted ascending. A card typed in price order, or with the 2.5 band below the 5 band, returns plausible rates for the wrong bands, with no error to warn you.
Reaching for FALSE out of habit. Exact match is right for IDs and SKUs. On a band table it returns #N/A for every weight that is not itself a band start — most of the list.
Weights stored as text. A weight imported as text does not compare properly against the numeric band starts, and the lookup can return a wrong rate. Microsoft's best-practice list for VLOOKUP includes not storing number values as text (VLOOKUP function — Microsoft Support).
Sources
- VLOOKUP function — Microsoft Support — syntax, approximate match and the sorted first column, the #N/A result when the lookup value is smaller than the smallest first-column value, and the note on numbers stored as text.