If a Value Lies Between Two Numbers Then Return Expected Output in Excel

Excel is a popular and useful tool for analyzing large sets of data. Now, wouldn’t it be great if we could add decision-making ability to our spreadsheet? Sounds complex, right? Wrong! In this article, we’ll demonstrate 4 easy ways to use if-then formula between two numbers in Excel.


Download Practice Workbook

You can download the practice workbook from the link below.


4 Ways to Return Expected Output If a Value Lies Between Two Numbers in Excel

Firstly, let us dwell a little upon what is the If-Then formula.
In a nutshell, Excel’s IF-THEN formula adds decision-making ability to a worksheet. Simply put, it checks whether a condition is true or false and performs a particular task based on the condition.
Considering the Employees Age List dataset shown in the B4:C13 cells. In this dataset, we have the Names of the employees and their Ages respectively.

excel formula if between two numbers then

Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.


Method-1: Using AND Function to Return Output Based on the Condition

Let’s begin with the simplest way to use the IF-THEN formula between two numbers. Here, we’ll use the AND function to check if the Age of employees lies between 25 and 30 years. So, let’s see the process in detail.

📌 Steps:

  • First of all, go to the D5 cell and enter the formula given below.

=AND(C5>=$G$6,C5<=$G$5)

Here, the C5, G5, and G6 cells refer to the Age, Upper Limit, and Lower Limit respectively.

📃 Note: Please make sure to use Absolute Cell Reference by pressing the F4 key on your keyboard.

Formula Breakdown:

  • AND(C5>=$G$6,C5<=$G$5) → checks whether all the arguments are TRUE, and returns TRUE if all the arguments are TRUE. Here, C5>=$G$6 is the logical1 argument, and C5<=$G$5 is the logical2 argument since both arguments are TRUE so the function returns the output TRUE.
    • Output → TRUE

Using AND Function for If-Then Formula Between Two Numbers

Using AND Function

Finally, your result should look like the image shown below.

Using AND Function

Read More: [Fixed!] CTRL C Not Working in Excel


Method-2: Utilizing IF and AND Functions

The second method for the IF-THEN formula between two numbers combines the AND and IF functions to return a string of text based on the results. It’s simple & easy, just follow along.

📌 Steps:

  • First and foremost, move to the D5 cell and type in the expression given below.

=IF(AND(C5>=$G$6,C5<=$G$5),"Yes","No")

In this formula, the C5, G5, and G6 cells refer to the Age, Upper Limit, and Lower Limit respectively.

Formula Breakdown:

  • AND(C5>=$G$6,C5<=$G$5) → checks whether all the arguments are TRUE, and returns TRUE if all the arguments are TRUE. Here, C5>=$G$6 is the logical1 argument and C5<=$G$5 is the logical2 argument since both arguments are TRUE so the AND function returns the output TRUE.
    • Output → TRUE
  • =IF(AND(C5>=$G$6,C5<=$G$5), “Yes”, “No”) → checks whether a condition is met and returns one value if TRUE and another value if FALSE. Here, AND(C5>=$G$6,C5<=$G$5) is the logical_test argument that compares if the age in the C5 cell is greater than equal to Upper Limit in the G6 cell and if the date in C5 is less than equal to the Lower Limit in the G5 cell. If this statement is TRUE, then the function returns “Yes” (value_if_true argument) otherwise it returns “No” (value_if_false argument).
    • Output → Yes

Utilizing IF and AND Functions for If-Then Formula Between Two Numbers

Lastly, the output should look like the picture given below.

Using IF and AND Function


Similar Readings


Method-3: Applying Conditional Formatting

Another way to use if-then formula between two numbers in Excel involves utilizing the Conditional Formatting option. So, let’s begin.

📌 Steps:

  • Firstly, jump to the D5 cell and enter the following formula.

=AND(C5>=$G$6,C5<=$G$5)

In the above formula, the C5, G5, and G6 cells indicate the Age, Upper Limit, and Lower Limit respectively.

Applying Conditional Formatting for If-Then Formula Between Two Numbers

  • Next, select the range of cells D5:D13 >> under the Home tab, click the Conditional Formatting drop-down >> select the New Rule option.

Applying Conditional Formatting

In an instant, the New Formatting Rule wizard pops up.

  • Next, choose the Use a formula to determine which cells to format option.
  • Then, in the Rule Description enter the following formula.

=AND(C5>=$G$6,C5<=$G$5)

  • Now, click on the Format box to specify the cell color.

Applying Conditional Formatting

This opens the Format Cells wizard.

  • In turn, click the Fill tab >> choose a color of your liking, for example, we’ve chosen Light Green color >> hit the OK button.

Applying Conditional Formatting

Consequently, the results should look like the screenshot shown below.

Applying Conditional Formatting for If-Then Formula Between Two Numbers

Read More: How to Fix Formula in Excel (9 Easy Methods)


Method-4: Using AND, MIN, and MAX Functions

What if the two numbers are in different columns? Our next method is the answers this exact question. Here, we’ll combine the AND, MIN, and MAX functions to check if a third number lies between these two numbers.
Let’s consider the Numbers List dataset in the B4:D13 cells. Here, the dataset shows a Start Value, End Value, and Number respectively.

Using AND, MIN, and MAX Functions for If-Then Formula in Excel

📌 Steps:

  • In the first place, proceed to the E5 cell and type in the expression given below.

=AND(D5>=MIN(B5,C5),D5<=MAX(B5,C5))

Here, the B5, C5, and D5 cells represent the Start Value, End Value, and Number.

Formula Breakdown:

  • AND(D5>=MIN(B5,C5),D5<=MAX(B5,C5)) → checks whether all the arguments are TRUE, and returns TRUE if all the arguments are TRUE. Here, D5>=MIN(B5,C5) is the logical1 argument that checks if the value in the D5 cell is greater than equal to the larger of the two values in the B5 and C5 cells. Likewise, D5<=MAX(B5,C5) is the logical2 argument that checks if the value in the D5 cell is less than equal to the smaller of the two values in the B5 and C5 cells. If both the arguments are TRUE the function returns TRUE.
    • Output → TRUE

Using AND, MIN, and MAX Functions for If-Then Formula in Excel

Finally, the output should look like the image given below.

Using AND, MIN, and MAX Functions for If-Then Formula in Excel

Read More: How to Move Data from Row to Column in Excel (4 Easy Ways)


Employing COUNTIFS Function to Count Between Two Numbers

If you want to count the number of occurrences between two numbers, you can use the COUNTIFS function. So, let’s see it in action.

📌 Steps:

  • At the very beginning, navigate to the F6 cell and type in the expression given below.

=COUNTIFS(C5:C13,"<=30",C5:C13,">=25")

Here, the C5:C13 range of cells represents the Age of the employees, while 30 and 25 are the Upper and Lower Limits respectively.

Formula Breakdown:

  • COUNTIFS(C5:C13,”<=30″,C5:C13,”>=25″) → counts the number of cells specified by a given set of conditions or criteria. Here, C5:C13 is the criteria_range1 argument, and “<=30” is the criteria1 argument which counts all the Age values that are less than equal to 30. Next, the second set of C5:C13 is the criteria_range2 argument, and “<=30” is the criteria2 argument which counts the values that are greater than equal to 25. The Age values between 25 and 30 are shown in the output.
    • Output → 5

Employing COUNTIFS Function to Count Between Two Numbers

Subsequently, the results should look like the screenshot given below.

Employing COUNTIFS Function to Count Between Two Numbers


Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself to utilize the Excel formula if a value exists between two numbers then return desired output.

Practice Section


Conclusion

This article provides quick and easy answers on how to use if-then formula between two numbers in Excel. Make sure to download the practice files. Hope you found it helpful. Please inform us in the comment section about your experience. We, the Exceldemy team, are happy to answer your queries. Keep learning and keep growing!


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo