How to Use the AND Function Inside an IF Statement in Excel

スポンサーリンク
スポンサーリンク

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

  1. Select the cell where you want to use the combination of IF and AND (e.g., B2).
  2. 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.