You can return a result between 2 values in Excel with 2 functions
- The VLOOKUP function
This function does the job but is limited (sorted data, only one way to search)
- The XLOOKUP function
XLOOKUP function can calculate between 2 values with more options
Search in a range of values with VLOOKUP
For years, the only Excel function to return the result in a range of values was VLOOKUP as it is explained in this article.
But, the VLOOKUP function has 2 limitations
- Your data MUST be sorted
- The direction of the research is always in decreasing order
Search in a range of values with XLOOKUP
To correct the limitations of the VLOOKUP function, Microsoft's engineers have created the XLOOKUP 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
=XLOOKUP(B3,$E$3:$E$8,$F$3:$F$8,,-1)
If the data in the table with the percentages is not sorted, it's not a problem. The XLOOKUP still returns the correct result.
Specify the direction of the search with the 5th argument
With VLOOKUP, you can only return the smaller value of the range. 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 step
The formula is
=XLOOKUP(B3,Tbl_Tent[Number people],Tbl_Tent[Price],,1)
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:
=XLOOKUP(B3,Tbl_Food[Number people],Tbl_Food[Price],,1)*B3
