The Excel AND function is one of the logical functions. Learn how to determine if all conditions in a test are TRUE.
Using Excel AND Function
Here is how the AND function works:
The AND function tests multiple conditions with AND and returns TRUE if all arguments evaluate TRUE; FALSE if not. The goal of using the AND functions is to perform more than one logical test simultaneously.
AND has two outputs: TRUE or FALSE.
For example, to test if a number in cell C5 is greater than 150 and less than 180, use the formula below:
=AND(C5>150, C5<180)
If both conditions are TRUE, the result is TRUE.
Using the IF logical function with multiple logical tests is a great idea to use the AND logical function instead of a nested IF function.
Let’s see an example:
=AND(C5>150,C5<180) is equivalent to =IF(AND(C5>150,C5<180,TRUE,FALSE))
The first formula is easy to read.
Syntax and Arguments of AND function
Syntax:
=AND(logical test1, logical 2 test,…..)
Arguments:
- logical test1: the function evaluates the first condition
- logical test2: [optional argument], the second condition
Examples
In the first example, you want to test if the value in cell C7 is greater than 100 and less than 160.
Use AND the following formula:
=AND(C7>100, C7<160)
In this case, the result is FALSE. Take a closer look at the formula:
- =C7 > 100 return TRUE
- =C7 < 160 return FALSE
Based on the function’s definition, the result is FALSE because if any value evaluates to FALSE, the AND function will return FALSE.
Combine AND and IF function
In the next example, you can place the AND function inside an IF function.
=IF(AND(C3>100, C3<500), “Yes”, “No”)
This formula will return “Yes” if the value in C3 is greater than 100 and less than 500. Else the formula returns FALSE.
Combine AND and OR function
Logical functions work together without any trouble. Let us combine the AND function with the OR function.
=AND(C3=200,OR(D3=”Product 1″,D3=”Product 2″))
The formula returns TRUE when C3 = 200 and D3 is “Product 1” or “Product 2”
In the example, the only row with values that met the criteria is 4.
AND function returns #VALUE error
Finally, let us see how the function works if the formula does not contain logical values. Type the formula below:
=AND(C4, D4)
Result:
Wrapping things up
The AND function:
- has two outputs: TRUE or FALSE
- not case-sensitive
- can handle up to 255 arguments
- does not support wildcards
- text values or empty cells are ignored
- return the #VALUE error if no logical values are found or created during the evaluation
Additional resources:
