Explanation of “Range as a Cell Range” and “Range as a Numeric Range”
This page explains two types of ranges: range as a cell range and range as a numeric range.
Using the IF Function to Display “✔” If an Item Is in the Range, or “✘” If Not
This article explains how to use Excel’s IF function to check whether a specific item exists within a range (in this case, column B). If the item is present, “✔” will be displayed; otherwise, “✘” will be shown. This method makes it easy to verify whether a particular item is in a list.
Here is an explanation of range as a cell range.
Solution 1: Using IF and MATCH Functions
By using the IF function, you can check whether an item exists within a specified range and display “✔” or “✘” based on the result. In the example below, we check whether items in column A exist in column B.
In the following table, column C displays “✔” or “✘” to indicate whether items in column A exist within the range in column B.
| A | B | C | |
|---|---|---|---|
| 1 | Item | List | Result |
| 2 | Apple | Apple | =IF(ISNA(MATCH(A2, B:B, 0)), “✘”, “✔”) |
| 3 | Orange | Banana | =IF(ISNA(MATCH(A3, B:B, 0)), “✘”, “✔”) |
* B:B refers to the entire column B. If you want to exclude B1, use a specific range such as B$2:B$999.
Steps (Solution 1)
- Enter the following formula in cell C2:
- =IF(ISNA(MATCH(A2, B:B, 0)), “✘”, “✔”)
- This formula uses the MATCH function to check whether the value in A2 (search item) exists in column B.
- If the item is not found in column B, the MATCH function returns the “#N/A” error, which ISNA detects and displays “✘”.
- If the item exists, “✔” will be displayed.
* If B4 contains “Orange,” C3 will show “✔.” To avoid checking the entire column B, use a range like $B2:$B3.
Solution 2: Using the COUNTIF Function
Another method is to use the COUNTIF function to count how many times a specific item appears in a range. If the count is 0, “✘” will be displayed; otherwise, “✔” will be shown.
| A | B | C | |
|---|---|---|---|
| 1 | Item | List | Result |
| 2 | Apple | Apple | =IF(COUNTIF(B:B, A2) > 0, “✔”, “✘”) |
| 3 | Orange | Banana | =IF(COUNTIF(B:B, A3) > 0, “✔”, “✘”) |
Steps (Solution 2)
- Enter the following formula in cell C2:
- =IF(COUNTIF(B:B, A2) > 0, “✔”, “✘”)
- The COUNTIF function counts how many times the item in A2 (e.g., “Apple”) appears in column B.
- If “Apple” appears one or more times, COUNTIF returns the count, and “✔” is displayed.
- If the item is not in column B, COUNTIF returns 0, and “✘” is displayed.
How to Check If a Number Is “≥ x and < y” and Display “✔” or “✘”
The following section explains how to check whether a number falls within a specific range. For example, if the number is “≥ 15 and < 25,” display “✔”; otherwise, display “✘”.
Setting Conditions with the IF Function
The IF function can be combined with the AND function to set multiple conditions. The AND function defines the conditions, and the IF function displays a value based on the result.
Example: Display “✔” If the Number Is Between 15 and 25, Otherwise Display “✘”
In the following table, we check whether the values in each cell meet the conditions and use the IF function to display “✔” or “✘”.
| A | B | C | |
|---|---|---|---|
| 1 | 10 | =IF(AND(A1>=15, A1<25), “✔”, “✘”) | |
| 2 | 20 | =IF(AND(A2>=15, A2<25), “✔”, “✘”) | |
| 3 | 30 | =IF(AND(A3>=15, A3<25), “✔”, “✘”) |
Steps
- Enter numbers in column A (e.g., 10, 20, 30).
- In cell C1, enter the formula: =IF(AND(A1>=15, A1<25), “✔”, “✘”)
- Press Enter, and C1 will display “✔” or “✘” depending on the value in A1.
- Copy the formula to C2 and C3 to check other values.
Results
If A1 is 10, C1 will display “✘” because it does not meet the conditions. If A2 is 20, C2 will display “✔” because it meets the conditions. If A3 is 30, C3 will display “✘” because it does not meet the conditions.