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

Suppose we have the dataset of students’ exam marks below. We want to know whose mark is between 40 and 60. In this article, we will use different formulas and methods to check if the value is between the two numbers.

We used Microsoft 365 version, but the methods should work in other versions.

Method 1 – Using the IF Function

Steps:

Select cell D5 and enter the formula below:

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

Here,

C5 = Mark of the student.

G7 = Lower limit.

G6 = Upper limit.

Press ENTER.

The syntax =IF(C5>=$G$7,C5<=$G$6) checks if the value of C5 lies between the values in cells G6 and G7. If the condition is met, the formula will return TRUE, else FALSE.

Drag down the Fill Handle tool to copy the formula to the cells below.

The result looks as below.

Method 2 – Using the AND Function

Steps:

Select cell D5 and enter the following formula:

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

Here,

C5 = Mark of the student.

G7 = Lower limit.

G6 = Upper limit.

Press ENTER.

The syntax =AND(C5>=$G$7,C5<=$G$6) checks if the value of cell C5 lies between the values in cells G6 and G7. If the condition is met, the formula will return TRUE, else FALSE.

Drag down the formula to the rest of the cells using the Fill Handle.

The correct results are returned.

Method 3 – Combining the IF and AND Functions

Steps:

Enter the following formula in cell D5:

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

C5 = Mark of the student.

G7 = Lower limit.

G6 = Upper limit.

Press ENTER.

Formula Breakdown:

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

IF(AND(C5>=$G$6,C5<=$G$5),”Yes”,”NO”)→If the condition is met then it will return YES else it will return NO.

Drag down the formula for other cells.

The result will look like below.

Method 4 – Using AND, MIN, and MAX Functions

We can use the AND, MIN, and MAX functions to set a limit of a maximum value and minimum value range, then check if a value is between them.

Steps:

Select cell E5 and enter 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 the value of D5 is between the minimum value of B5 and C5 and the maximum value of B5 and C5. If the condition is met, it will return TRUE else FALSE.

Drag down the formula to the rest of the cells with the Fill Handle.

Method 5 – Using Conditional Formatting to Highlight If a Value Is Between Two Numbers

The Conditional Formatting feature can be used to check if a value is between two numbers, then format the cells that match the condition with color to highlight them.

Steps:

In cell D5 enter the following formula.

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

Press ENTER.
Drag down the Fill Handle to fill the other cells.
Select the entire cell range D5:D11.
Go to the 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 Format values where this formula is true box, enter the above formula.
Click Format.