IF Statement Between Two Numbers in Excel Formula

In Excel, you often deal with number ranges and need to check if a value is inside a specific range. For example, you might want to mark scores that are between 70 and 90 or highlight sales amounts within a target range. In these cases, the IF function can help return results like In Range or Out of Range based on your conditions.

There are several ways to use an IF formula that checks if a number is between two limits. It can return a text result, assign a numeric value, or even use the formula to group data by category.

In this article, we’ll learn how to use the IF formula to check if a number falls between two values. We’ll go through step-by-step examples using simple Excel functions that work in any version of Excel.

In the following dataset, we have a list of student names along with their exam scores. Column A lists the student names, and Column B contains their scores. Our goal is to mark whether each score is between 70 and 90 using an IF formula.

We’ll apply the formula in Column C labeled Status to show whether the score is In Range or Out of Range.

The most common way to check if a number falls between two values is by combining the IF and AND functions. This method works in all versions of Excel and is great when you want to return a clear label like In Range or Out of Range.

Here’s how to apply this method:

➤ Open your dataset in Excel.
➤ Click on cell C2 and enter the following formula:

➤ Press Enter. Excel will return In Range for any score between 70 and 90, and Out of Range for anything outside that range.

➤ Now, drag the fill handle down to apply the formula to the rest of the rows.

Sometimes, you may want to return numbers like 1 and 0 instead of text labels such as In Range or Out of Range. This is useful when you plan to use the results in further calculations, such as summing or filtering.

You can use the same IF and AND logic but replace the text with numbers.

Here’s how to apply this method using our student scores dataset:

➤ Open your dataset in Excel.
➤ Click on cell C2 and enter this formula:

➤ Press Enter. Now, Excel will return 1 if the score is between 70 and 90, and 0 if it’s outside the range.

➤ Drag the fill handle down to copy the formula to the other rows.

You can also use nested IF formulas to assign custom labels or categories based on different number ranges. This is useful for grading systems, pricing tiers, or any scenario where values fall into more than two groups.

In this method, we’ll assign letter grades based on score ranges.

For example: A for scores 90 and above, B for scores between 80 and 89, C for scores between 70 and 79, and F for scores below 70.

Here’s how to do it:

➤ Open your dataset in Excel.
➤ Click on cell C2 and enter the following formula:

➤ Press Enter. Excel will assign the correct grade to each student based on their score.

➤ Now, drag the fill handle down to apply the formula to the rest of the rows.

Frequently Asked Questions

How do I check if a number falls between two values in Excel?

You can use the IF function with the AND function. Here’s the basic structure:
=IF(AND(A1>=lower, A1<=upper), “Yes”, “No”)
For example:
=IF(AND(A1>=70, A1<=90), “In Range”, “Out of Range”)

How do I exclude the boundary values?

To exclude the lower and upper limits, use greater than and less than symbols:
=IF(AND(B2>70, B2<90), “In Range”, “Out of Range”)
This will only return In Range if the value is strictly between 70 and 90.

Wrapping Up

Excel offers a few simple ways to check if a number falls between two values. If you need to assign categories or labels based on multiple ranges, nested IF formulas work well and are easy to customize.

Each method works best depending on how you want to display or use the result. These IF-based formulas are quick to set up and help you analyze your number-based data more effectively in Excel.