In Excel, you can easily return a result between two values.
Most of the people think that they have to do a lot of IF nested. But you can do the same job with the function VLOOKUP
What is the problem?
Let's say you have this workbook where you want to calculate the commission for each worker in function of their sales.
How to return the commission earned by each saler? 🤨🤔
Solution with the IF function
This is how most of the Excel users try to solve this problem. It works but it's really not the most efficient solution.
With the IF function, we have to consider each case in a specific IF. For instance, for the cell C2, we can write
=IF(B2>=30,F8,IF(B2>=25,F7,IF(B2>=20,F6,IF(B2>=10,F5,F4))))
Yes it works, the result is correct but this formula is horrible to write. And there is a lot of chance to make mistake.
Solution with the IFS function
If you have Excel 2016, Excel 2019 or Microsoft 365, you can avoid a IF nested with the new function IFS.
There is no difference with a standard IF nested in the sense that you have to write all the different case. But the easier to read the formula.
=IFS(test1,result,test2,result,test3,result,...)
So in our example, the writing of this formula is
=IFS(B2>=30,F8,B2>=25,F7,B2>=20,F6,B2>=10,F5,B2>=0,F4)
With the IFS function, you don't have to manage the number of closing parenthesis like with the IF nested 😉
Solution with VLOOKUP
VLOOKUP accepts 2 parameters for its last argument.
- 1 or TRUE for an approximate match
- 0 or FALSE for an exact match
99% of the time, you use VLOOKUP with the parameter FALSE to find a result on the same ID like in a database.
But with the value TRUE, the function doesn't work the same way. In fact the function will consider that the research match a result if the value searched is "between". 😲😍👍
Construction of the VLOOKUP function
Let's start with the value to search
=VLOOKUP(B2,
Then, our reference table
=VLOOKUP(B2,$E$4:$F$8,
And, we indicate the column to return (the second in our reference table)
=VLOOKUP(B2,$E$4:$F$8,2,
We finish by the type or research ; 1 or TRUE
=VLOOKUP(B2,$E$4:$F$8,2,1)
It's so easy !! 😎😎😎
Same function but with the last argument = 0
Just to see the impact of the last parameter of the function VLOOKUP, if you have exactly the same formula but with the parameter FALSE, or 0, the result is wrong
Your data must be sorted
It is compulsory that your data inside the reference table are sorted. If your data are not sorted, you will return a wrong result.
