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?

How to calculate between 2 values

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

Create IF nested is complex with risk of mistake

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)

Function IFS to avoid IF nested

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". 😲😍👍

How to return the value for 15

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 !! 😎😎😎

VLOOKUP returns the result for any values

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

VLOOKUP must have the argument TRUE

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.

The data must be sorted is your reference table

Related posts


Permanent link to this article: https://www.excel-exercise.com/calculate-between-two-values/

Leave a Reply

Your email address will not be published.