Correct Usage of AND Within an IF Statement in Excel with Examples
By combining the IF function with the AND function in Excel, you can execute specific actions only when multiple conditions are simultaneously met. This article explains the basic structure, step-by-step instructions, and examples of how to use these functions together.
Basic Syntax of IF and AND Functions
The basic syntax for using the AND function within an IF function is as follows:
=IF(AND(condition1, condition2, …), value_if_TRUE, value_if_FALSE)
The AND function returns TRUE only when all conditions are met; otherwise, it returns FALSE.
Steps
- Select the cell where you want to use the combination of IF and AND (e.g., B2).
- Enter a formula with multiple conditions, as shown below.
Example Formula
For example, to display “OK” when the value in cell A2 is 10 or greater, and the value in cell B2 is 20 or less, use the following formula:
=IF(AND(A2 >= 10, B2 <= 20), “OK”, “NG”)
Explanation of the Formula
- A2 >= 10: Checks if the value in cell A2 is greater than or equal to 10.
- B2 <= 20: Checks if the value in cell B2 is less than or equal to 20.
- AND(A2 >= 10, B2 <= 20): Returns TRUE if both conditions are met; otherwise, returns FALSE.
- IF(…, “OK”, “NG”): Displays “OK” if AND returns TRUE, otherwise displays “NG”.
Example
The table below shows how values in columns A and B are evaluated in column C using the condition “A ≥ 10 and B ≤ 20”:
| A | B | C | |
|---|---|---|---|
| 1 | Value 1 | Value 2 | Result |
| 2 | 15 | 20 | =IF(AND(A2 >= 10, B2 <= 20), “OK”, “NG”) |
| 3 | 8 | 18 | =IF(AND(A3 >= 10, B3 <= 20), “OK”, “NG”) |
| 4 | 12 | 25 | =IF(AND(A4 >= 10, B4 <= 20), “OK”, “NG”) |
| 5 | 10 | 15 | =IF(AND(A5 >= 10, B5 <= 20), “OK”, “NG”) |
Results
- C2: “OK” because A2 ≥ 10 and B2 ≤ 20.
- C3: “NG” because A3 < 10.
- C4: “NG” because B4 > 20.
- C5: “OK” because A5 ≥ 10 and B5 ≤ 20.
Comparison with Programming Syntax
| EXCEL | JavaScript |
|---|---|
| =IF(AND(A2 >= 10, B2 <= 20), “OK”, “NG”) |
if(A2 >= 10 && B2 <= 20) {
console.log("OK");
} else {
console.log("NG");
}
|
Summary
Combining the IF and AND functions in Excel allows you to evaluate multiple conditions and execute specific actions only when all conditions are met. This method simplifies setting up complex conditions in your spreadsheet calculations.