How to look up which range a number falls in

Lower limits with VLOOKUP TRUE (every version)

The sheet is a courier's delivery log with a price list on the right: up to 5 km is Local, over 5 up to 15 km is Zone 1, and so on to 60 km. The fees in column F are example inputs; type the ones from your own price list. Keeping them in cells means a price change is one edit, not a hunt through formulas.

You do not need a row for every possible distance. VLOOKUP with TRUE (approximate match) looks down the first column for the largest value that is less than or equal to the number. 14.9 km is not in the table, but 5 is the largest limit at or below it, so VLOOKUP returns that row's fee. The column must be sorted smallest to largest and should start at 0, or numbers below the first limit return #N/A.

TRUE is VLOOKUP's default, so =VLOOKUP(B4,$D$2:$F$5,3) gives the same 7.90. With FALSE it looks for exactly 14.9 and returns #N/A. INDEX and MATCH work the same way with MATCH's last argument set to 1: =INDEX($F$2:$F$5,MATCH(B4,$D$2:$D$5,1)).

XLOOKUP with match mode -1 (Excel 365 and 2021)

XLOOKUP's fifth argument, match_mode, set to -1 means "exact match, or else the next smaller item". That is the same lower-limit logic, with three improvements: the table does not have to be sorted, you point at the result column directly instead of counting columns, and an inserted column does not break it.

Point the return range at more than one column to get several results at once: =XLOOKUP(B9,$D$2:$D$5,$F$2:$G$5,,-1) spills 19 and Regional into two cells.

The boundary: is exactly 5 km Local or Zone 1?

Order D-3012 is exactly 5 km. A lower-limit lookup treats every From value as the start of its band, so 5 km lands in Zone 1 and costs 7.90. But the price list says "up to 5 km" is Local, at 4.50. Both formulas above are wrong for this one row, with no error to warn you.

Match the formula to how the bands are written. If the list says "from X", look up lower limits with -1 (or VLOOKUP TRUE). If it says "up to X", look up the upper limits with match mode 1, "exact match, or else the next larger item": 5 matches the 5 km row exactly and returns Local's fee. The if_not_found argument handles distances above the last limit.

Excel 2019 and earlier have no XLOOKUP, and MATCH's "next larger" mode (-1) needs the list sorted largest to smallest, so with VLOOKUP keep lower limits and decide on purpose which side a boundary belongs to. Test a value exactly on each limit before you trust the table.

Numbers above the top band

D-3016 is 85 km, outside the delivery area. A lower-limit lookup has no upper end: 85 is above 30, the last limit, so VLOOKUP and XLOOKUP with -1 both return the Regional fee of 19.00 as if the order were deliverable. This is the most expensive mistake with band tables, because the answer looks normal.

Check the top limit yourself: =IF(B7>MAX($E$2:$E$5),"Outside area",VLOOKUP(B7,$D$2:$F$5,3,TRUE)) returns Outside area, and gives the normal fee for every distance up to 60. With the upper-limit XLOOKUP from the previous section, the if_not_found text does the same job with no IF.

At the other end, a number below the first limit returns #N/A. If a negative or missing value is possible, that #N/A is useful: it tells you the input is wrong. Do not hide it with IFERROR.

Both limits at once with FILTER (gaps or unsorted bands)

When a table has gaps between bands, or rows in no particular order, test both limits in one go. Each comparison returns TRUE or FALSE for every band; multiplying them keeps only the band where both are true, and FILTER returns its fee. The test mirrors the price list: greater than From, and at most Up to.

If two bands overlap by mistake, FILTER returns both fees and spills them into two cells. That is a useful warning, not a bug. In Excel 2019 and earlier, =SUMPRODUCT((B9>$D$2:$D$5)*(B9<=$E$2:$E$5)*$F$2:$F$5) gives the same 19 for one matching band, but it returns 0 when nothing matches and adds the fees together if bands overlap, so check the table first.

Bands typed as text, and numbers stored as text

Price lists pasted from a PDF or email often have bands as text in one cell, like "5-15" in column I. No lookup can compare a number with "5-15". Split the limits out: =--TEXTAFTER(I3,"-") returns 15 as a number, and TEXTBEFORE gives the lower limit. You can do it inside the lookup, as below, or in helper columns, which are easier to check. TEXTAFTER and TEXTBEFORE need Excel 365 or 2024; in older versions use Data > Text to Columns with - as the delimiter.

The other side fails too. D-3017's 12 km came in as text, so every lookup on B8 returns #N/A even though 12 is clearly inside Zone 1. Convert it in the formula, =XLOOKUP(--B8,$E$2:$E$5,$F$2:$F$5,"Outside area",1) returns 7.90, or convert the column once.

Counts per band have the same weak spot. =COUNTIFS($B$2:$B$9,">"&D3,$B$2:$B$9,"<="&E3) counts Zone 1 orders and returns 1, not 2, because the text 12 is skipped without any error.

Which method to pick

For "from X" bands in any version, VLOOKUP TRUE on sorted lower limits, wrapped in a top-limit check if values can go past the last band. In Excel 365 or 2021, XLOOKUP: -1 on lower limits for "from" bands, 1 on upper limits for "up to" bands, with if_not_found for anything outside. Use FILTER when bands have gaps or are unsorted. For many bands, any of these beats a nested IF, because the limits stay in cells you can see and change.

The same layout works for any published table: postage by weight, age bands, price breaks, or a rate schedule. Copy the published limits and rates into the input cells exactly as published. Finding the band a figure falls in is not the same as calculating an amount across several bands, where each slice gets its own rate; for that, see the tiered commission guide.