What is IF AND in Excel and how to use it

One of the most useful tools for running cal­cu­la­tions in Excel is the IF AND formula. The nested formula enables you to make precise queries that check numerous criteria and deliver immediate results. Keep reading to find out how Excel’s IF AND function works and which contexts it can be used in.

What is Excel’s IF AND formula?

The IF AND formula is a com­bi­na­tion of the IF function and the AND function. The IF function is used to evaluate whether a statement is true or false. You can set the con­di­tions that a value needs to fulfill in order to be evaluated as true. When you add the AND function, you can add other con­di­tions that need to be fulfilled in order for the value to be con­sid­ered true. This makes the IF AND formula an efficient way to check data against several criteria.

The structure of the IF AND formula is as follows:

=IF(AND(something is true,something else is true),then_value,otherwise_value)

Say you want to check whether the values in a dataset fulfill con­di­tions A and B. The result C will be given as the output only if both of these con­di­tions are met. Otherwise, the result D will be output. In the formula above, the element something is true cor­re­sponds to condition A and something else is true cor­re­sponds to condition B. The then_value is result C, and the otherwise_value is result D.

Example of how to use IF with AND in Excel

We’ll now walk through how this works using an Excel table with five orders. For each order, the total price in dollars is listed, alongside in­for­ma­tion about whether the order was made by a new customer (value yes) or an existing customer (value no). We want to use this in­for­ma­tion to calculate whether each order qualifies for a discount or not.

Close

In order to qualify for a discount, an order must be placed by a new customer and have a total of at least $50. If an order was placed by an existing customer or has a total of less than $50, the customer will have to pay the full price. Thus, there are two con­di­tions for receiving a discount.

In order to use Excel’s IF AND formula to check which orders receive a discount, click on cell D2 and insert the following:

=IF(AND(B2="yes",C2>49.99),"YES","NO")

Insert the formula using the Enter key, and D2 will au­to­mat­i­cal­ly be filled with the answer.

Close

To receive an answer for the remaining cells, simply click on the green box and pull it down to the last relevant cell. Since the formula uses relative reference, cells B2 and C2 will au­to­mat­i­cal­ly be changed to the cor­re­spond­ing cells for each row.

Close

How to combine IF AND with other functions in Excel

One of the best things about Excel functions is that you can combine them freely in order to carry out complex and intricate eval­u­a­tions of the data you have. In the following example, we’ll combine the IF AND function with the IF OR function.

For this example, we’ll add an ad­di­tion­al column to the table from above, which will list whether the order was placed by an employee. Just like new customers, employees receive a discount on orders of $50 or more. To qualify for a discount, either column B or column C needs to be marked with yes.

Close

Insert the following formula to determine which orders receive discounts:

=IF(AND(OR(B2="yes",C2="yes"),(D2>49.99)),"YES","NO")

The first spot in the IF AND formula is now filled with the OR function, which in turn contains two con­di­tions. Only one of these two con­di­tions needs to be fulfilled in order for an order to qualify for a discount. The second condition—the total price of an order must be at least $50—remains the same. After entering the formula and applying it to all cells, your table will look as follows:

Close

In Excel, you can embed functions on up to 64 levels. However, nesting multiple IF functions can quickly become complex. In many cases, al­ter­na­tives like the LOOKUP function or the INDEX function can be useful, although they do require ad­di­tion­al reference tables.