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: 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: 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: 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”: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
