Carriers price by weight band, not exact ounces. An approximate-match VLOOKUP drops any package weight into the right band and returns its rate — no nested IFs.
A 3.2 lb package falls in the 1–5 lb band and returns $8.50. The TRUE finds the largest band floor not exceeding the weight.
The example
Packages on the left; the sorted weight-band rate table on the right (columns F–G).
| A | B | C | |
|---|---|---|---|
| 1 | Item | Weight (lb) | Shipping |
| 2 | Mug | 0.6 | $5.00 |
| 3 | Books | 3.2 | $8.50 |
| 4 | Boots | 12 | $15.00 |
| 5 | Office chair | 25 | $30.00 |
The formula
The band table is sorted by its floor weight so approximate match works:
How it works
Approximate match is built for banded rates:
- The band table lists each band's floor weight (0, 1, 5, 20) and its rate, sorted ascending.
VLOOKUP(B2, …, 2, TRUE)scans down and stops at the largest floor that does not exceed the package weight.- It returns that band's rate from the second column — the right price without a single nested IF.
The fourth argument is the whole trick: TRUE (or 1) means “close enough,” and it requires the table to be sorted ascending.
Try it: interactive demo
Enter a package weight to find its band rate.
Variations
Exact-match price list
For SKU-based flat rates instead of bands, switch the last argument to FALSE.
Modern XLOOKUP
In Excel 365, XLOOKUP with match mode -1 finds the next-smaller band floor.
Pitfalls & errors
With TRUE, an unsorted band table returns wrong rates silently. Always sort the floors ascending.
A weight below the first band floor returns #N/A. Start your table at 0 to catch the lightest packages.
Practice workbook
Frequently asked questions
Why TRUE instead of FALSE?
What if a weight is below the smallest band?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class