IF Statement Between Two Numbers

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

Written by CFI Team

Read Time 2 minutes

Over 2 million + professionals use CFI to learn accounting, financial analysis, modeling and more. Unlock the essentials of corporate finance with our free resources and get an exclusive sneak peek at the first module of each course. Start Free

How to Combine IF with AND functions

In Excel, you can combine IF with AND functions[1] to return a value based on two different numbers. It can be very useful when performing financial modeling and when 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 cell B2 and, if the number is between 100 and 999, then the result will be 100. Otherwise, if it is outside that range, then the result will be zero.

Download the Free Excel Template – IF with AND Functions.

IF Statement Between Two Numbers

=IF(AND(C6>=C8,C6<=C9),C11,C12)

(See screenshots below).

Example of how to use the formula:

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

Step 2: Put the criteria in cells C8 and C9 (100 and 999).

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

Step 4: Type the formula =IF(AND(C6>=C8,C6<=C9),C11,C12).

IF with AND Functions

Final Result

Here is a screenshot in Excel after using the formula for an IF statement between two numbers. You can clearly see how the result from the example is 100 because the number 150 is between 100 and 999.

IF Statement in Excel with Two Numbers

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

Download the free template.

If Statement Between Two Numbers (With Text)

You don’t have to limit the resulting output from the model to only numbers. You can also use text, as shown in the example below. This time, instead of producing 100 or 0 as the result, Excel can display YES or NO to show if the argument is true or false.

IF Statement Between Two Numbers with Text

Article Sources

IF with AND Functions

Additional Resources

CFI is a global provider of financial modeling courses and of the FMVA Certification. CFI’s mission is to help all professionals improve their technical skills. If you are a student or looking for a career change, the CFI website has many free resources to help you jumpstart your Career in Finance. If you are seeking to improve your technical skills, check out some of our most popular courses. Below are some additional resources for you to further explore:

The Financial Modeling Certification

Analyst Certification FMVA® Program

The Financial Modeling Certification

Below is a break down of subject weightings in the FMVA® financial analyst program. As you can see there is a heavy focus on financial modeling, finance, Excel, business valuation, budgeting/forecasting, PowerPoint presentations, accounting and business strategy.

A well rounded financial analyst possesses all of the above skills!

Additional Questions & Answers

CFI is the global institution behind the financial modeling and valuation analyst FMVA® Designation. CFI is on a mission to enable anyone to be a great financial analyst and have a great career path. In order to help you advance your career, CFI has compiled many resources to assist you along the path.

In order to become a great financial analyst, here are some more questions and answers for you to discover: