Lookup & Reference
Lookup a value between two numbers (explicit Min and Max columns)
Match an input inside inclusive [Min, Max] bracket rows using FILTER—rejecting out-of-bounds values that approximate VLOOKUP accepts.
Why explicit [Min, Max] brackets beat approximate VLOOKUP on capped tables
Approximate VLOOKUP checks only the lower bound—so if your freight table stops at 50 kg ($45), a 65 kg overweight crate silently receives the $45 rate instead of an overweight warning!
Multiplying (PkgKg >= MinCol) * (PkgKg <= MaxCol) inside FILTER enforces BOTH lower and upper bounds simultaneously, works even on unsorted rows, and returns "Out of range" below 0.1 kg or above 50 kg.
| MinKg (A) | MaxKg (B) | Rate (C) | Test 7.5 kg / 65 kg |
|---|---|---|---|
| 0.1 | 5 | 12 | No / No |
| 5.01 | 20 | 24 | Match ($24) / No |
| 20.01 | 50 | 45 | No / Out of range |
Two-sided bracket lookup formula
Wrap FILTER(..., "Out of range") in INDEX(..., 1, 1) so the formula returns a single scalar rate.
=INDEX(FILTER(C2:C4, (D2 >= A2:A4) * (D2 <= B2:B4), "Out of range"), 1, 1)Exact boundary check (20 kg)
Inclusive <= and >= operators assign exact 20.00 kg to row 3 ($24).
=INDEX(FILTER(C2:C4, (20 >= A2:A4) * (20 <= B2:B4), "Out of range"), 1, 1)Inputs and interpretation
Each row must have numeric Min <= Max. INDEX takes the first match when bands overlap; reject overlaps if each input must have exactly one rate. There are intentional gaps between 5 and 5.01 and between 20 and 20.01: values with more than two decimal places can fall there. Use an explicitly defined rounding policy or half-open contiguous bands for continuous measurements.
Try the example
Use the original values or change them to test your understanding.