How to Check If a Value Is Between Two Numbers in Excel

Excel introduces diverse formulas to check if a value is between two numbers. Do you want to learn the formulas to check if the value is between two numbers?? If your answer is ‘yes’, this article is for you. Here we have discussed 5 easy and simple methods to do that.

We have taken a dataset of students’ exam marks. We would like to know whose mark is between 40 to 60. Now we will use different formulas and methods to check if the value is between the two numbers.

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

1. Using the IF Function to Check If a Value Is Between Two Numbers in Excel

We can use the IF function to check if the value is between two numbers. You need to follow the simple steps stated below.

📌 Steps:

• Select cell D5 and write the formula below.

Here,

C5=Mark of the student.

G7=Lower side of the mark.

G6=upper side of the mark.

• Press ENTER.

The syntax =IF(C5>=$G$7,C5<=$G$6) checks if C5 lies between cell G6 & G7. If the logic is actual, it will return TRUE. Otherwise, it will return FALSE.

• Then drag down the Fill Handle tool for other cells.

• Finally, your result will look like the one below.

2. Applying AND Function to Find out Value Between Two Numbers

We can also use only AND function to check if a value is between two numbers. Follow the steps.

📌 Steps:

• Move to cell D5 and write down the formula.

Here,

C5=Mark of the student.

G7=Lower side of the mark.

G6=upper side of the mark.

• Press ENTER.

The syntax =AND(C5>=$G$7,C5<=$G$6) checks if C5 lies between cells G6 and G7. If the logic is true, it will return TRUE. Otherwise, it will return FALSE.

• Drag down the formula for other cells.
• Finally, you will get your expected result.

3. Combining IF and AND Functions to Check If the Value Is Between Two Numbers

We can combine Excel IF and AND functions to check if the value is between the two numbers.

📌 Steps:

• Initially, write down the formula in cell D5.

C5=Mark of the student.

G7=Lower side of the mark.

G6=upper side of the mark.

• Press ENTER.

• Finally, drag down the formula for other cells and the result will look like the below.

Read More: If a Value Lies Between Two Numbers Then Return Result in Excel

4. Using AND, MIN, and MAX Functions to Check If a Value Is Between Two Numbers

In Excel, you can use a limit of maximum value and minimum value range and check if a value is between two numbers of the assigned minimum and maximum value. For this, you can use AND, MIN, and MAX functions.

📌 Steps:

• Firstly, select cell E5 and write the following formula.

B5=Start Mark.

C5=End Mark.

D5=Obtained Mark.

• Press ENTER.

• Finally, drag down and get the formula for other cells.

5. Applying Excel Conditional Formatting to Highlight to Check If a Value Is Between Two Numbers

Excel has a built-in feature, Conditional Formatting to check if a value is between two numbers. This feature formats the cell with color to make you realize the difference in the result.

📌 Steps:

• First, go to cell D5 and write down the formula.

• Press ENTER.
• Drag down Fill Handle for other cells.
• Then select the entire cell range D5:D11 >> go to HOME tab >> click Conditional Formatting >> select New Rule.

• A dialog box will appear. Click Use a formula to determine which cells to format.
• In the box, write the above formula and start format.

• A new dialog wizard will open. Select Fill >> Select a color >> Click OK.

• Then click Format.

• Finally, your result will look like the screenshot below.

Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.

Conclusion

That’s all about today’s session. And these are some easy methods to check if the value is between two numbers. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the Practice Workbook. Thanks for your patience in reading this article.

Related Articles

<< Go Back to Excel IF Function | Excel Functions | Learn Excel