XLOOKUP value between two numbers

You can use the XLOOKUP function and a sorted list to look up values between two numbers and return a corresponding result.

Formula to apply XLOOKUP between two values

=XLOOKUP(lookup value, mins, category,,-1)

In our example, enter the formula in D3:

=XLOOKUP(C3, mins, category,,-1)

We use the following named ranges:

Mins= E5:E9,
Results= G5:G9

Explanation

You can use the XLOOKUP to find values between two numbers. The function provides optional arguments; in this case, use the 5th argument (match_mode) to control the result. The main point is that if no exact match is found, the function will use approximate matches, and you’ll get the next smaller item.

In the case of XLOOKUP, we need to use only the lower bands (minimum values in a given range).

Configure the function arguments using the following way:

lookup values are scores in column C
lookup array is a named range “lows” (F3:F10)
return array is column D
match mode: next smaller (-1)

Good to know; you must sort values in ascending order in the lookup array.

lookup-a-numeric-value-between-two-numbers

Evaluate the formula:

XLOOKUP finds an exact match by default in the lookup array, and it will return the corresponding values from the category column.
If the function does not find an exact match, XLOOKUP uses the next smaller array as a lookup array and returns the matching value.
If the lookup value is greater than the maximum value in a range, XLOOKUP will use the last category as a lookup array and get the proper result.