Check if a Value Is Between Two Numbers in Excel

You can use the IF function to check if a value is between two values. You can also combine IF and AND functions in order to set multiple ranges.

This is the formula we are going to use in our example.

This is how this formula works. It returns TRUE if the number is bigger than 3 and smaller than 6, otherwise, it returns FALSE. In our case, only two numbers: 4 and 5 meet this requirement.

How this formula works

1. Checks whether the value is bigger than 3.
2. If the value is bigger, check if the value is smaller than 6.
3. If it’s smaller return TRUE, otherwise return FALSE.

Check if a value is between using the AND function

In the previous example, we used two if functions, but we can write this formula in a different way. This time we are going to use one IF and one AND function.

This is what our formula looks like.

You can even make this code shorter by using only the AND function.

This formula is much cleaner and more readable than the last one.

Check if a value is between multiple ranges

So far, in both examples, we created formulas that check whether the value is between two numbers. Now, let’s try to create a formula that will check whether a value exists in more than one range.

We could create this formula using only the IF function, but it would be much too complicated, that’s why we are going to use both IF and AND functions, as we did in the previous example.

This is the formula.

And this is the result.

The numbers that meet this condition are 4, 5, 8, and 9. And this is what we can see inside the worksheet.

How this formula works

1. Checks whether the value is bigger than 3 and smaller than 6. If the value meets this criterion it returns TRUE.
2. If the value is not present in the first range, check it for the second range.
3. If it’s not present in any of these ranges return FALSE.