How to use the Excel AND function

Summary

The Excel AND function is a logical function used to require more than one condition at the same time. AND returns either TRUE or FALSE. To test if a number in A1 is greater than zero and less than 10, use =AND(A1>0,A1<10). The AND function can be used as the logical test inside the IF function to avoid extra nested IFs, and can be combined with the OR function.

Purpose

Test multiple conditions with AND

Return value

TRUE if all arguments evaluate TRUE; FALSE if not

Syntax

=AND (logical1, [logical2], ...)

Arguments

logical1 - The first condition or logical value to evaluate.
logical2 - [optional] The second condition or logical value to evaluate.

Version

Excel 2003

Usage notes

The AND function is used to check more than one logical condition at the same time, up to 255 conditions, supplied as arguments. Each argument (logical1, logical2, etc.) must be an expression that returns TRUE or FALSE or a value that can be evaluated as TRUE or FALSE. The arguments provided to the AND function can be constants, cell references, arrays, or logical expressions.

The purpose of the AND function is to evaluate more than one logical test at the same time and return TRUE only if all results are TRUE. For example, if A1 contains the number 50, then:

=AND(A1>0,A1>10,A1<100) // returns TRUE
=AND(A1>0,A1>10,A1<30) // returns FALSE

The AND function will evaluate all values supplied and return TRUE only if all values evaluate to TRUE. If any value evaluates to FALSE, the AND function will return FALSE. Note: Excel will evaluate any number except zero (0) as TRUE.

Both the AND function and the OR function will aggregate results to a single value. This means they can't be used in array operations that need to deliver an array of results. To work around this limitation, you can use Boolean logic. For more information, see: Array formulas with AND and OR logic.

Examples

To test if the value in A1 is greater than 0 and less than 5, you can use AND like this:

=AND(A1>0,A1<5)

You can embed the AND function inside the IF function. Using the above example, you can supply AND as the logical_test for the IF function like so:

=IF(AND(A1>0,A1<5), "Approved", "Denied")

This formula will return "Approved" only if the value in A1 is greater than 0 and less than 5.

You can combine the AND function with the OR function. The formula below returns TRUE when A1 > 100 and B1 is "complete" or "pending":

=AND(A1>100,OR(B1="complete",B1="pending"))

See below for many more examples of how the AND function can be used.

Notes

The AND function is not case-sensitive.
The AND function does not support wildcards.
Text values or empty cells supplied as arguments are ignored.
The AND function will return #VALUE if no logical values are found or created during evaluation.

Related functions

Excel IF Function

The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF...

Excel OR Function

The Excel OR function returns TRUE if any given argument evaluates to TRUE, and returns FALSE if all supplied arguments evaluate to FALSE. For example, to test A1 for either "x" or "y", use =OR(A1="x",A1="y"). The OR function...

Excel XOR Function

The XOR function performs what is called "exclusive OR". With two logical statements, XOR returns TRUE if either statement is TRUE, but returns FALSE if both statements are TRUE. If neither is TRUE, XOR also returns FALSE.