Excel Formula: If Between Two Numbers then Return Value

---

You can use the following formula to return a value in Excel only if it falls between two specific numbers:

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

This particular formula checks if the value in cell B2 is between 20 and 30.

If the value is between these numbers, then the formula simply returns the value.

Otherwise, the formula returns a blank.

The following example shows how to use this formula in practice.

Example: Return Value if Between Two Numbers in Excel

Suppose we have the following dataset in Excel that contains information about points scored by various basketball players:

We can use the following formula to check if each value in the Points column is between 20 and 30:

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

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

If the value in column B is between 20 and 30, then column C simply returns the value.

Otherwise, column C returns a blank.

Note that you could also return other values using the IF function if you’d like.

For example, you could instead use the following formula to return either “Yes” or “No” to indicate if a value falls between 20 and 30:

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

The following screenshot shows how to use this formula in practice:

Column C now returns either “Yes” or “No” to indicate if the corresponding value in column B is between 20 and 30.