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

## 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. 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.  • Finally, your result will look like the one below. ### 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. • 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. • 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. • Finally, drag down and get the formula for other cells. ### 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) • 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. ## Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself. ## Related Articles 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 Advanced Excel Exercises with Solutions PDF  