IF with AND Function in Excel

A fruit seller is selling apples. You would only buy Apples if they are Red and Juicy. If an apple is not Red or Juicy, you will not buy it.
So here you have two conditions. How would you do that in Excel without using nested IFs. There is a easy combination of IF and AND function to do so. Let’s see how…

Implementation of IF with AND

Generic Formula

=IF(AND(condition1, condition2,...),value if true, value if false)

Lets consider the example we discussed in the beginning.
We have this table of apple’s color and type.
If color is “Red” and type is “Juicy” then write OK in column D. Else type Not OK.
Write this IF-AND formula in D2 column and drag it down.

=IF(AND(B3="Red",C3="Juicy"),"OK","Not OK")

And you can see now that only apples that are Red and Juicy are marked OK.

How It Works

IF function: You know how IF function in Excel works. It takes a boolean expression as first argument and returns one expression IF TRUE and another if FALSE. Learn more about The Excel IF Function.

=IF(TRUE or FALSE, statement if True, statement if false)

AND Function Function : Checks multiple conditions. Returns TRUE only if all conditions are TRUE else returns FALSE.

=AND(condition1, condition2,....) ==> TRUE/FALSE

In the end, AND function provides IF function TRUE or FALSE argument and based on that IF prints the result.

Alternate Solution

Another way to do this is to use nested IFs for Multiple Conditions.

=IF(B3="Red",IF(C3="Juicy",”OK”,”Not OK”),”Not OK”)

Nested IF is good when we want different results but not when only one result. It will work but for multiple conditions it will make your excel formula to long.

So here we learned about how to use IF with AND to check multiple conditions and show the result only if all of them are true. But what if you want to show the result if at least one condition is true? We will use IF with OR function in excel to do so.

Related Articles:

IF function with Wildcards

IF with OR Function in Excel

IF with AND and OR function in Excel

Popular Articles:

50 Excel Shortcut to Increase Your Productivity

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel



Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube