How to Match Ranges with XLOOKUP Approximate Match in Excel

Price parcels by weight band, pick the smallest box that fits and total a whole batch with XLOOKUP match modes -1 and 1.

You will learn to

• Find the band a value falls into with match_mode -1
• Find the next size up with match_mode 1
• Look up several values at once and total them

How it works

XLOOKUP’s fifth argument, match_mode, controls what happens when there is no exact match. With -1 it returns the next smaller item, which is how bands work: a 3.2 kg parcel falls into the band that starts at 2 kg, so =XLOOKUP(3.2,A2:A9,B2:B9,,-1) returns that band’s price. With 1 it returns the next larger item, perfect for “smallest box that fits”.

Unlike VLOOKUP’s TRUE mode, XLOOKUP does not need the table sorted for these modes. Give it a range of lookup values and it returns one result per value, so =SUM(XLOOKUP(I4:I7,A2:A9,B2:B9,,-1)) prices a whole batch at once.

=XLOOKUP(I1,A2:A9,B2:B9,,-1)