Lookup & Reference
Calculate Carrier Shipping Cost by Weight Ceiling & Zone (2D XLOOKUP Mode 1)
Look up parcel shipping rates from a carrier Zone x Max-Weight rate card using nested XLOOKUP with match_mode = 1 (exact match or next larger weight ceiling)—fixing VLOOKUP's next-smaller bracket bug!
Why approximate VLOOKUP undercharges every 6.2 lb parcel—and XLOOKUP Mode 1 fixes it!
Parcel carrier rate cards (UPS, FedEx, USPS, DHL) quote rates by "Weight Not Over" ceilings: up to 1 lb = $7.50, up to 5 lb = $9.50, up to 10 lb = $12.00, up to 25 lb = $18.00. Notice the classic VLOOKUP(..., TRUE) trap: because approximate VLOOKUP always rounds DOWN to the next SMALLER threshold, a 7.0 lb parcel falls back to the 5 lb row ($9.50) instead of billing at the "up to 10 lb" ceiling ($12.00)—undercharging shipping on every non-integer box!
XLOOKUP's 5th argument match_mode = 1 ("exact match or next LARGER item") was built specifically for ceiling rate cards—and nesting a horizontal Zone lookup XLOOKUP(Zone, B1:D1, B2:D5) inside the return array solves BOTH dimensions at once! Even better, any overweight pallet above 25 lb automatically triggers if_not_found = "LTL Freight Quote" instead of silently capping at the 25 lb rate!
| Weight Not Over (Col A) | Zone_2 (Regional) | Zone_5 (Mid-Country) | Zone_8 (Coast-to-Coast) |
|---|---|---|---|
| Up to 1 lb | $7.50 | $9.00 | $11.50 |
| Up to 5 lb | $9.50 | $12.50 | $16.00 |
| Up to 10 lb (7 lb lands here!) | $12.00 | $16.50 | $22.00 |
| Up to 25 lb | $18.00 | $25.00 | $34.00 |
Two-Way Carrier Rate Lookup (Exact or Next-Larger Weight Ceiling + Zone)
Validates positive weight G2, looks up the Zone column G3 via inner XLOOKUP, and matches the ceiling bracket via outer XLOOKUP(..., 1).
=IF(G2 <= 0, "Invalid Weight", XLOOKUP(G2, A2:A5, XLOOKUP(G3, B1:D1, B2:D5), "LTL Freight Quote", 1))Effective Shipping Cost per Pound ($ / lb)
Divides the matched carrier rate by parcel weight G2 when within standard parcel limits.
=IF(OR(G2 <= 0, G2 > MAX(A2:A5)), "Out of Parcel Range", ROUND(XLOOKUP(G2, A2:A5, XLOOKUP(G3, B1:D1, B2:D5), 0, 1) / G2, 2))Try the example
Use the original values or change them to test your understanding.