When most people use XLOOKUP in Excel, they rely on exact matches, i.e. looking up a value and returning the corresponding result from another column. But did you know that XLOOKUP also supports non-exact matches, giving you powerful new ways to work with ranges, thresholds, and banded data?
In this blog post, we’ll explore two of the match modes in XLOOKUP: Exact match or next smaller item and Exact match or next larger item. These options are perfect when you’re working with things like tax brackets, commission structures and pricing tiers.
Here’s the general syntax of the XLOOKUP function:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
We’ll focus on the matchmode argument, which determines how Excel searches for the lookup value. Match-Mode can be one of 3 values:
0: Exact match, which is the default
-1: Exact match or next smaller item
1: Exact match or next larger item
Exact Match or Next Smaller Item
In the screenshot above, the formula in B3 is =XLOOKUP(B2,D2:D22,E2:E22,,-1)
If the revenue in B2 matches one of the values in column D, we want it to select the associated bonus from column E – that’s the exact match part. But if the revenue in B2 doesn’t match one of the values in column D, instead of displaying #NA, we want it to select the next lowest value from column D and use that as the lookup value.
In other words…the revenue is 99,000. Working backwards from 99,000 , the first value in column D that is lower than 99,000 is 80,000 so it uses 80,000 as the lookup value and therefore it returns 9% as the bonus percent.
Note – the reason for the 2 commas in the formula is that you need a comma to separate each argument in the function and although we’re not specifying a value for the “if not found” argument, we still need a comma otherwise Excel will think the -1 is the 4th argument not the 5th.
Also note that the values in column D must be in ascending order
Exact Match or Next Larger Item
The above spreadsheet is used by the packing team at a company that sells bottled drinks. They need to know what size box to use and that is based on the number of bottles purchased.
In C4 I’ve used this formula…=XLOOKUP(C3,F3:F7,G3:G7,,-1)
It’s looking for the value in C3, i.e. the number of bottles purchased and then it’s looking for that value in column F. If it finds the value, we want it to return into C4, the corresponding box size but if it can’t find an exact match we want it to return the next largest value.
Why the next largest and not the next smallest? The customer has ordered 50 bottles. At the moment the 5th argument is -1. As Excel can’t find 50 in column F, it selects the first value it finds that is lower than 50 which is 48 and that’s why it’s returned D as the answer.
The problem is that box size D can only hold 48 bottles. We need a box that is bigger than the number of bottles the customer has bought. By changing the -1 to 1, Excel now looks for next highest value after 50, which is 60, and uses that as the lookup value.
Want to see it in action? Watch the video…
Download a copy of the file(s) used in this video: https://share.zight.com/BluerlAv