# Calculate between two values

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 Office 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 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

=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.