Small rate tables do not need their own sheet. LOOKUP with array constants finds the band the distance falls in and returns the matching rate.
120 miles falls in the 50-149 band at $6.00 per pound; 40 lb costs $240.
The example
Three shipments in different bands.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Case | Miles | Lbs | Rate/lb | Cost |
| 2 | Local | 35 | 20 | $4.50 | $90.00 |
| 3 | Regional | 120 | 40 | $6.00 | $240.00 |
| 4 | Cross-country | 420 | 15 | $11.00 | $165.00 |
The formula
Band starts must be sorted ascending:
How it works
Here is the logic, one piece at a time:
{0,50,150,300}lists band start miles.{4.5,6,8.25,11}lists the rates.LOOKUPfinds the last start at or below the distance.- Multiply by weight for the cost.
Keep band starts in ascending order.
Try it: interactive demo
Enter the distance and weight.
Variations
Rate table
Use a range.
Minimum charge
Apply a minimum.
Pitfalls & errors
Unsorted band starts return wrong rates.
A distance below the first band start returns #N/A.
Start the first band at 0.
Practice workbook
Frequently asked questions
Why LOOKUP?
Update rates?
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