Excel: If Number is Between Range Then Return Value

You can use the following formulas to check if a number is between a range in Excel and return a particular value if so:

Method 1: Check if Number is Between Range (Exclusive)

=IF(AND(B2>20, B2<30), "Yes", "No")

This particular formula checks if the value in cell B2 is between 20 and 30 (excluding 20 and 30) and returns “Yes” or “No” as a result.

Method 2: Check if Number is Between Range (Inclusive)

=IF(AND(B2>=20, B2<=30), "Yes", "No")

This particular formula checks if the value in cell B2 is between 20 and 30 (including 20 and 30) and returns “Yes” or “No” as a result.

The following examples show how to use each formula in practice with the following dataset in Excel that contains information about points scored by various basketball players:

Example 1: Check if Number is Between Range (Exclusive)

We can type the following formula into cell C2 to check if the points value in cell B2 is between 20 and 30 (exclusive) and return “Yes” or “No” accordingly:

=IF(AND(B2>20, B2<30), "Yes", "No")

We can then click and drag this formula down to each remaining cell in column C:

The formula returns “Yes” or “No” to indicate if the points value in each row is between 20 and 30 (exclusive).

Notice that any values equal to exactly 20 or 30 receive a value of “No” since the formula checks if the points value is greater than 20 and less than 30.

Example 2: Check if Number is Between Range (Inclusive)

We can type the following formula into cell C2 to check if the points value in cell B2 is between 20 and 30 (inclusive) and return “Yes” or “No” accordingly:

=IF(AND(B2>=20, B2<=30), "Yes", "No")

We can then click and drag this formula down to each remaining cell in column C:

The formula returns “Yes” or “No” to indicate if the points value in each row is between 20 and 30 (inclusive).

Notice that any values equal to exactly 20 or 30 receive a value of “Yes” since the formula checks if the points value is greater than or equal to 20 and less than or equal to 30.