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
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 or Office 365, you can create 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.
So in our example, the writing of this formula is
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 parameter
- 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 parameter 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
Then, our reference table
And, we indicate the column to return (the second in our reference table)
We finish by the type or research ; 1 or TRUE
It's so easy !! 😎😎😎
Same function but with the last parameter = 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