VLOOKUP approximate match: assign parcel rates from sorted weight thresholds

How a minimum-threshold rate table works

A courier charges by parcel weight using four bands. Each row in A2:B5 states the minimum weight (in kg) where that price begins: 0.5 kg starts the 4.50 band, 2 kg starts 7.00, 5 kg starts 11.50, and 10 kg starts 18.00.

Use TRUE as the fourth argument explicitly

Enter the parcel weight 3.4 in D2. With TRUE in the fourth position, VLOOKUP finds the largest threshold less than or equal to 3.4—which is 2 kg in row 3—and returns 7.

Although omitting the fourth argument also triggers approximate match, writing TRUE explicitly tells anyone auditing the sheet that band matching is intentional rather than an accidental omission of FALSE.

=VLOOKUP(D2,A2:B5,2,TRUE)

Check exact boundaries and below-minimum weights

An exact hit on a threshold belongs to that new tier: 0.5 kg returns 4.5, 5.0 kg matches A4 (5) and returns 11.5, and 14.2 kg exceeds the final threshold (10) so it stays in the 18 top band.

What if D2 is 0.2 kg—lighter than the first row (0.5 kg)? Because no threshold in A2:A5 is less than or equal to 0.2, VLOOKUP returns #N/A. Either start your table at 0 kg if tiny parcels are accepted, or catch sub-minimum weights explicitly with IFNA.

=IFNA(VLOOKUP(D2,A2:B5,2,TRUE),"Below minimum weight")

Why unsorted thresholds break approximate lookups

Approximate VLOOKUP is valid only when column A is sorted from smallest to largest. If rows are left in descending order (10, 5, 2, 0.5) or pasted out of sequence, neither Excel nor Arte Sheets raises a sort warning—both can return an unintended tier or #N/A. Sort column A ascending and test at least one weight inside each band before publishing a rate sheet.

Further reading

• Microsoft: VLOOKUP function