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

Get FREE Advanced Excel Exercises with Solutions!

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.


Download Practice Workbook

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


5 Methods to Check If a Value Is Between Two Numbers in Excel

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.

Excel Check if Value is Between Two Numbers

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


1. Using IF Function to Check If Value Is Between Two Numbers

We can use Excel 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.

=IF(C5>=$G$7,C5<=$G$6)

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.

Using IF Function to check if value is between two numbers

Using IF Function to Check if Value is Between Two Numbers 

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

Read More: How to Check If Value Exists in Range in Excel (8 Ways)


2. Implementing 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.

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

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.

Implementing AND Function to Find Out Value Between Two Numbers

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

Read More: How to Check If a Value is in List in Excel (10 Ways)


3. Using IF and AND Functions to Check If 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.

=IF(AND(C5>=$G$6,C5<=$G$5),”Yes”,”NO”)

C5=Mark of the student.

G7=Lower side of the mark.

G6=upper side of the mark.

  • Press ENTER.

Formula Breakdown:

AND(C5>=$G$6,C5<=$G$5)→checks whether the value of C5 lies between cell G5 and G6.

IF(AND(C5>=$G$6,C5<=$G$5),”Yes”,”NO”)→If the logic is true then it will return YES and if the logic is not true it will return NO.

Using IF and AND Functions to Check If Value Is Between Two Numbers

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

Read More:How to Check If a Cell is Empty in Excel (7 Methods)


4. Using AND, MIN, and MAX Functions

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.

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

Here,

B5=Start Mark.

C5=End Mark.

D5=Obtained Mark.

  • Press ENTER.

Formula Breakdown:

AND(D5>=MIN(B5,C5),D5<=MAX(B5,C5))→checks if D5 is between the minimum value of B5 and C5 and the maximum value of B5 and C5. If the logic is true, it will return to TRUE. Else, it will return FALSE.

Using AND, MIN, MAX Function to Check Value Is Between Two Numbers

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

Read More: VBA to Check If Cell is Empty in Excel (5 Methods)


5. Applying Conditional Formatting

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.

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

Applying Conditional Formatting to check if value is between two numbers

  • 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.

Applying Conditional Formatting

  • 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.

Applying Conditional Formatting

  • Then click Format.

Applying Conditional Formatting

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


Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Practice Section


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 your better understanding, please download the Practice Workbook. Visit our website Exceldemy, a one-stop Excel solution provider, to find out diverse kinds of excel methods. Thanks for your patience in reading this article.


Related Articles

Fahim Shahriyar Dipto
Fahim Shahriyar Dipto

Hello! Welcome to my Excel blog! I am a big fan of MS Excel. I am learning new and exciting things in Excel and writing the process here. I think this will be helpful for you to get used to Excel. Keep visiting our website for new and updated Excel methods.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo