When you want to return a value in a range of values, 2 functions do the job: VLOOKUP and better XLOOKUP.
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 like it is explain in this article.
Limits of the use of VLOOKUP
The VLOOKUP function has 2 limitations when you use it to search in a range of values
- Your data MUST be sorted
- The direction of the research is always in the decreasing order
Search in a range of values with XLOOKUP
The XLOOKUP function has been created to correct the limitations of the VLOOKUP function and also simplify the writing.
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 here)
- Select the option of research "exact match or next smaller item"
The complete formula is
=XLOOKUP(B3,$E$3:$E$8,$F$3:$F$8,,-1)
What's happen if the data are unsorted
But now, if the reference table (the Commission table) isn't sorted, the XLOOKUP function still returns the correct result 😃👍
Return the larger value of the range
With VLOOKUP, you can only return the smaller value of the range. But with XLOOKUP, you can also return larger value.
In this example, you want to calculate the price according to the number of people for an event
This time, we have selected the option 1 "Exact match or larger item"
The price of the tent depends on the number of people (B3), so the formula is
=XLOOKUP(B3,Tbl_Tent[Number people],Tbl_Tent[Price],,1)
As you can see, the result returns by the formula is 700 which is the larger value of the range 50-75
And now, what is the price of the food?
=XLOOKUP(B3,Tbl_Food[Number people],Tbl_Food[Price],,1)*B3
