Table of Contents
Excel is a powerful tool that can be used to perform various calculations and tasks. One useful feature of Excel is the ability to return a specific value if a number falls within a certain range. This can be achieved by using the IF and AND functions in combination. By setting certain criteria and conditions, Excel can be instructed to search for a specific number and return a desired value if it falls within a given range. This functionality can be particularly useful in data analysis and decision-making processes, allowing users to quickly and accurately extract relevant information from a large dataset. With its user-friendly interface and customizable formulas, Excel offers a simple yet effective solution for handling data and retrieving specific values based on given parameters.
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)
=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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: