XLOOKUP Approximate Match: Next Smaller or Larger

An XLOOKUP approximate match finds the closest value when there is no exact one, using the fifth argument, match_mode. Set it to -1 for the exact value or the next smaller one, or to 1 for the exact value or the next larger one. This guide shows when to use each, with a commission tier table and a shipping rate table, and how it compares with VLOOKUP's approximate match.

XLOOKUP needs Excel 2021, Excel 2024, Microsoft 365 or Excel for the web. If you haven't used it yet, read the XLOOKUP guide for beginners first.

What match_mode does in XLOOKUP#

match_mode tells XLOOKUP what to do when the exact value isn't in the list. It is the fifth argument:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Modes -1 and 1 are the approximate ones. In both, an exact match always wins. Only when the exact value is missing does XLOOKUP move to the nearest item in the direction you chose. Wildcard mode 2 is covered in XLOOKUP wildcards and match modes.

Next smaller (-1): tiers that start at a lower limit#

Use -1 when your table lists the starting point of each band. Commission tiers are a classic example:

Each row means "from this amount up". A rep who sold 32,000 is past the 25,000 line but hasn't reached 50,000, so she earns 6%. With her sales in B2:

=XLOOKUP(B2,D2:D5,E2:E5,,-1)

XLOOKUP looks for 32,000, doesn't find it, and takes the next smaller value, 25,000. The result is 6%. The two commas leave the fourth argument, if_not_found, empty.

The row for 0 matters. Without it, sales of 8,500 would have no next smaller value and XLOOKUP would return #N/A. The same pattern handles tax brackets and grade bands; see tax bracket and tier lookups for a full worked example.

Next larger (1): limits that say "up to"#

Use 1 when your table lists the upper limit of each band. Shipping rates often work this way:

A 6.5 kg parcel is too heavy for the 5 kg band, so it goes in the "up to 10 kg" band. With the weight in B2:

=XLOOKUP(B2,D2:D5,E2:E5,,1)

XLOOKUP takes the next larger value, 10, and returns 12.00. A parcel of exactly 5 kg matches the 5 row and costs 7.00.

To pick the right mode, read your table's first column aloud. If it says "from", use -1. If it says "up to", use 1.

Add a message for values outside the table#

Approximate modes still return #N/A when there is nothing in the chosen direction. With -1, that happens below the smallest value. With 1, it happens above the largest. A 25 kg parcel is heavier than every limit in the shipping table, so:

=XLOOKUP(B2,D2:D5,E2:E5,"Quote needed",1)

returns Quote needed instead of #N/A. The message goes in the fourth argument, before the match mode. More options are in XLOOKUP if not found.

XLOOKUP vs VLOOKUP approximate match#

VLOOKUP's approximate match (TRUE, or a blank fourth argument) behaves like XLOOKUP's -1, with two important limits:

The sorting point is the big one. If a VLOOKUP tier table isn't sorted smallest to largest, VLOOKUP can return a wrong rate with no error at all. XLOOKUP's default search checks every row and finds the true nearest value, so a shuffled table still gives the right answer. The comparison of INDEX MATCH vs XLOOKUP covers the same idea for MATCH, whose type 1 and -1 also need sorted data.

You may still want to sort the table anyway, because it is much easier for people to read.

Speed up big lookups with search_mode#

The sixth argument, search_mode, can switch to a binary search. Modes 2 and -2 search a sorted list very quickly, which helps on tens of thousands of rows. They need the list sorted ascending (2) or descending (-2), and give wrong results if it isn't. For normal-sized tables, leave search_mode out.

search_mode -1 searches from the bottom up, which is how you find the most recent entry rather than the first one. The guide to looking up the last match shows that technique.

Common mistakes with XLOOKUP approximate match#

1. Wrong direction. Using 1 on a "from" table shifts every result up one band. Check one value that falls between two limits by hand.
2. Missing the bottom or top row. A -1 table needs a 0 row; a 1 table needs a row large enough for every value.
3. Too few commas. =XLOOKUP(B2,D2:D5,E2:E5,-1) puts -1 in the if_not_found slot, so you get an exact match. You need two commas: ,,-1.
4. Text numbers in the table. Limits stored as text never compare as numbers. Convert them first.

For the full list of arguments, see the XLOOKUP function guide.

Practise it#

Tier lookups come up in finance and operations job tests all the time. Practise XLOOKUP match modes in a live spreadsheet: you type the formula, choose -1 or 1, and it checks the result instantly. When you're ready, try the finance Excel mock test to use it under time pressure.