You can return a value between two values in Excel with two functions. Both VLOOKUP and XLOOKUP are helpful when searching for approximate matches within a range.
Don't use the IF function ⛔
Many Excel users try this approach to solving this problem. It works but is not efficient. With the IF function, you must handle every case. This is called nested IF.
Or, you can simply the number of parenthesis with the IFS function.
Each function returns the correct result, but there are issues:
- Each function is difficult to create.
- The risk of making mistakes is high.
- Anyone unfamiliar with Excel could easily make errors when updating.
Solution #1: Search between two values with VLOOKUP
For many years, the best way to find a value between two values was to use VLOOKUP. By setting the last argument to TRUE (or 1), you can perform an approximate match. This allows you to search between values 😉. The major advantages of this approach are:
- You don't need to write out each condition, unlike with the IF or IFS functions.
- Simply select the reference table that contains all the values.
- The only requirement is that your reference table values must be sorted in ascending order, from smallest to largest.
How to write the VLOOKUP is this situation?
- lookup_value: The value you want to search for (B2)
- table_array: Your reference table ($E$4:$F$8)
- col_index_num: The column to return in the reference table (here the second column)
- range_lookup: TRUE for an approximate match.
Solution #2: Use the XLOOKUP function
You can also use the XLOOKUP function. As this as been seen in this article, the XLOOKUP function provides more options than the VLOOKUP function. For instance, the XLOOKUP function can perform an approximate search in both direction (next smaller item or next larger item). And also, you lookup values don't need to be sorted 😃
But you can also use the XLOOKUP the other way round. That means from the next larger item.
