How to look up the next smaller item or the next larger item

The XLOOKUP function searches a range or an array for a match and returns the corresponding item from a second range or array.

The syntax of the XLOOKUP function is as follows:

= XLOOKUP ( lookup_value ; lookup_array ; return_array ; if_not_found ; match_mode ; search_mode )

The lookup value can be either a text or a number, inputted directly into the function or as a cell reference.

 

When looking up numbers, other than match type 0, exact match, we can also use the following types of matches:

• -1, exact match or next smaller item, finds the largest value that is less than or equal to the lookup value,
• 1, exact match or next larger item, finds the smallest value that is greater than or equal to the lookup value.

 

In contrast with the MATCH function, there are no requirements for the data to be presorted in ascending or descending order here.

 

Consider the following example:

 

In column B, we have several months, and in column C, related interest rates.

We need to interpolate rates for the missing months. The rate for the 2nd month will be interpolated based on the rates for the 1st and 3rd months, etc.

In column E, we have a list of the next 12 months.

 

We can use match type -1 (exact match or next smaller item) in order to retrieve the first smaller value than the one we are looking up:

 

We can use match type 1 (exact match or next larger item) in order to retrieve the first larger value than the one we are looking up:

 

We can use match type -1 (exact match or next smaller item) in order to retrieve the first rate related to a smaller period than the one we are looking up:

 

We can use the match type 1 (exact match or next larger item) in order to retrieve the first rate related to a larger period than the one we are looking up:

 

Once we’ve returned all the necessary periods and rates, we can use the FORECAST.LINEAR function in order to interpolate the missing rates:

 

Now consider the following example.

We have a list of exchange rates for several different dates and currencies.

That list has no available dates for weekends. We need to retrieve an exchange rate for a specific Saturday, so we want to use the exchange rate for the previous day, Friday.

We are using structured references:

By using match type -1 (exact match or next smaller item), we’ve retrieved the first smaller value available on the list, i.e., the EUR CAD exchange rate for the previous day.

This is probably not what we want. We also want to specify the currency code available in the column code.

However, we can’t look up multiple criteria here, as this is only possible with match type 0 (exact match).

In order to retrieve the first smaller exchange rate available on the list for, let’s say, the GBP currency code, we will have to first filter that list:

By using the FILTER function, we can generate an array that will contain only exchange rates for the GBP currency code and then retrieve the exchange rate for the previous day, Friday.

 

The logic of the less than or equal to and greater than or equal to matching demonstrated here also applies to the MATCH function, but with differences in syntax and requirements. See this article on how to use the MATCH function for this purpose.

 

Dig deeper:

Lookup with unique identifiers

Lookup with multiple criteria

Lookup the 2nd, the 3rd, or the nth value

How to MATCH numbers formatted as text in Excel

FILTER function