Calculate between two values in a range

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

=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

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

And of course, if your table reference has more rows, you just have to extend the references. With a IF or IFS functions, you have to write a condition for each entry. 😕😱

Same function but with the exact match

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

Data must be sorted ⚠⚠⚠

One thing very important. It is compulsory that your data inside the reference table are sorted.

If your data are not sorted, you will return a wrong result

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.