You can return a result between 2 values in Excel with 2 functions
- Don't use the IF function ⛔
The construction of Nested IFs is way too long and a source of mistakes
- The VLOOKUP function
This function does the job but is limited (sorted data, only one way to search)
- The XLOOKUP function
XLOOKUP function can also calculate between 2 values but it can search from first to last or last to first.
Don't use the IF function ⛔
This is how most of the Excel users try to solve this problem. It works but it's not the most efficient solution. With the IF function, we have to consider each case in a specific IF. For instance, for cell C2, we can write several nested IFs.
=IF(B2>=30,F8,IF(B2>=25,F7,IF(B2>=20,F6,IF(B2>=10,F5,F4))))
Yes it works, and the result is correct but this formula is horrible to write. And there is a lot of chance to make mistakes.
Solution #1: Search in a range of values with VLOOKUP
For years, the only Excel function that returned the result in a range of values was VLOOKUP with the last argument = TRUE (or 1)
- lookup_value: The value you want to search for.
- table_array: The range of cells that contains the data.
- col_index_num: The column number in the table_array from which to retrieve the value.
- range_lookup: TRUE for an approximate match.
But, the VLOOKUP function has 2 limitations
- Your data MUST be sorted in the lookup table
- The direction of the research is always in increasing order
Solution #2: Search in a range of values with XLOOKUP
Microsoft engineers have created the XLOOKUP function to correct the limitations of the VLOOKUP function. Let's redo the same example and see how to write the XLOOKUP function in this situation
- Write the name =XLOOKUP
- Select the cell with the value to search (here B2)
- Then you select only the column with the range of values ($E$3:$E$8)
- And after, the column of the values to return ($F$3:$F$8)
- Skip the next argument (not necessary in this example)
- Select the option of research "exact match or next smaller item" (value -1)
The complete formula is:
If the data in the lookup table is not sorted, it works 😀✌️
XLOOKUP manages both directions 🥳
- With VLOOKUP, you can only return a result when a threshold is reached.
- But with XLOOKUP, you can also return the result in both directions.
- In this example, you want to calculate the price according to the number of people for an event.
To calculate the rental price of the tent, we will use
- The number of people (B3)
- The column with the different steps (D5:D9)
- The column with the price for each threshold
- The formula is
The result returned is the upper value of the range [50-75]. Only XLOOKUP can be used in this situation.
In the same way, the formula to calculate the price of the food for 60 people is:
