IF Statement Between Two Numbers

This article outlines how to use the IF with AND functions in Excel.

How to combine IF with AND functions

In Excel, you can combine IF with AND functions to return a value based on two different numbers.  This can be very useful when performing financial modeling and you are creating conditional situations.  In this article, learn how to build an IF statement between two numbers so you can easily answer the problem you’re trying to solve.

For example, if you are looking for a formula that will go into to cell B2 and, if the number is between 100 and 999 the result will be 100, otherwise if it is outside that range the result will be zero.

Download the Free Excel Template – IF with AND Functions.


IF statement between two numbers


(See screenshots below)


Example of how to use the formula:

Step 1: Put the number in cell C2 you want to test (150).

Step 2: Put the criteria in cells C4 and C5 (100 and 999).

Step 3: Put the results if true or false in cells C6 and C7 (100 and 0).

Step 4: type the formula =IF(AND(C2>=C4,C2<=C5),C6,C7)


Combining IF with AND function in Excel


Free Excel Tutorial

To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.  

Browse all of CFI’s Excel courses to take your career to the next level!

Final result


IF Statement Between Two Numbers in Excel


Congratulations, you have now combined IF with AND between two numbers in Excel!

Download the free template.


More excel tutorials

Thank you for reading this guide to understanding how to use an IF with AND function in Excel to know if a number is between two other numbers.  To keep learning and developing your career as a financial analyst, these additional resources will help you on your way: