The AND function is a simple test that returns either TRUE or FALSE. It is a logical function that accommodates many conditions at a time.
Using the AND function in Excel
For instance, it can be used to check if a value in cell D5 is more than 40 (condition 1) but less than 60 (condition 2) by using =AND(D5>40,D5<60). The function can also be used in an IF function to avoid having too many embedded IFs. The AND function can also be used together with the OR function.
The general formula for the AND function
Explanation of the AND formula
logical1 – This is the first condition to test.
logical2, logical3 – These are optional. They are the second and third conditions (if any) to evaluate.
How to use the AND function
The AND function can be used to test up to 255 multiple conditions or logical values at the same time. Each condition (logical1, logical2, logical3, etc.) are tested to ascertain if the conditions are TRUE or FALSE,
For instance, to check if the number in cell B1 is greater than 5 and less than 10, we use this formula:
The AND function can also be used to broaden the functionality of functions like IF. For instance, you can use the AND function as the logical_test for the IF function. Using the example above we have:
The formula will return “Approved” only if the number in cell B1 is greater than 5 and less than 10 and will return “Denied” if either or both of the conditions are not satisfied
- The AND function ignores texts or null cells given as arguments.
- #VALUE will be returned if there are no logical conditions are found.
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.