Lookup & Reference
Approximate Tier Lookup with Multiple Criteria (FILTER + XLOOKUP Weight Breaks)
Look up volume-discounted freight or commission rates where exact Zone and Service criteria combine with an approximate weight-break threshold—using FILTER inside XLOOKUP(..., -1).
Combining exact categorical filters with approximate numeric bracket matching
Real-world carrier rate cards mix two lookup modes at once: Zone ("West" vs "East") and Service ("Ground" vs "Air") must match exactly, while Shipment_Kg (for example, 78 kg) must match the largest Min_Kg breakpoint <= 78 kg within that exact lane.
Filtering both the Min_Kg column and Rate_Per_Kg column down to the matching lane first—FILTER(MinKg, (Zone=z)*(Svc=s))—and passing those filtered sub-vectors into XLOOKUP(Weight, sub_breaks, sub_rates, "NO_TIER", -1) solves exact + approximate multi-criteria pricing cleanly.
| Step | Expression | Role in Pricing |
|---|---|---|
| 1. Lane mask | (A2:A7 = G2) * (B2:B7 = G3) | Isolates rows for exact Zone & Service |
| 2. Filtered breaks & rates | FILTER(C2:C7, mask), FILTER(D2:D7, mask) | Extracts only that lane's weight tiers |
| 3. Bracket lookup | XLOOKUP(G4, breaks, rates, "NO_LANE", -1) | Finds exact or next-smaller weight break |
Multi-Criteria Weight-Break Rate Formula
Bind mask inside LET so the boolean lane filter is evaluated once and reused for both breakpoints and unit rates.
=IF(COUNTIFS(A2:A7, G2, B2:B7, G3) = 0, "NO_LANE", LET(lane_mask, (A2:A7 = G2) * (B2:B7 = G3), XLOOKUP(G4, FILTER(C2:C7, lane_mask), FILTER(D2:D7, lane_mask), "NO_TIER", -1)))Try the example
Use the original values or change them to test your understanding.