April 21, 2021

AND function in Excel

In this lesson we will learn how the AND function works in Excel. AND function is used when you want to check multiple conditions. It returns TRUE if all the conditions evaluate to TRUE or returns FALSE even if one condition is FALSE.

=AND(logical1, [logical2],…)
  • logical1 – This is the first condition, you want to evaluate for TRUE or FALSE.
  • [logical2] – Second and rest conditions are optional, that you want to evaluate for TRUE or FALSE.

Let’s understand with a simple example. In below table the price has been mentioned as 15 in cell A2. We have used AND function in cell B2 with 2 conditions as follows.

=AND(A2>10,A2<30)
  • The first condition returns TRUE as 15>10.
  • The second condition also returns TRUE as 15<30.

Hence the result of the AND function is TRUE.

See below we have changed the price as 5 in cell A2.

  • Now the first condition returns FALSE as 5 is not greater than 10.
  • The second condition returns TRUE as 5<30.

Hence the result of AND function is FALSE.

Notes :

  • AND function can be used with other formulas like, in an IF Function, we can test a condition and then specify a value when it’s TRUE and a value when it is FALSE. Using AND function within IF enables users to check multiple conditions at one go.
      • For example, if you have to test whether A1 is greater than 20 and less than 200, here is how you can do it in an IF function:
        =IF(AND(A1>20,A1<200),”Approve”,”Reject”)
  • The arguments must either evaluate to logical values (TRUE/FALSE), or must be arrays/references of logical values.
  • Maximum of 255 conditions can be tested in a single AND function.
  • If the specified range contains no logical value, the AND function returns #VALUE! error.
  • Text & empty cells are ignored.

Check here to learn how the AND function works in Google Sheet.

You can also check here to understand how the OR function works in Excel.

You can also check here to understand how the OR function works in Google Sheet.

You can also check here to understand how the NOT function works in Excel.

You can also check here to understand how the NOT function works in Google Sheet.

Leave a Reply

Your email address will not be published. Required fields are marked *