You can use the XLOOKUP function and a sorted list to look up values between two numbers and return a corresponding result.
General 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.
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.
Workaround with the LOOKUP function
If your Excel version does not support XLOOKUP, here is the workaround with the LOOKUP function.
The formula below will replace the new one and find the lookup value between two numbers (min and max) in the lookup array.
Create further named ranges:
- Highs: range G3:G9
- Category: range H3:H9
Lookup formula:
=LOOKUP(C3,1/((C3>=lows)*(C3<=highs)),category)
LOOKUP function arguments:
- Lookup value: “score” column
- Lookup vector: 1/((C3>=lows)*(C3<=highs))
- Result vector: H3:H9 range
Additional resources:
- How to use lookups with multiple criteria
- Nested lookup example
