Excel IF AND function: explanation and examples

Excel Nested IF and AND Functions: What are they?¶

In the popular Excel spreadsheet software, the IF function and the AND function can be nested to combine their usefulness. The latter is used to check the accuracy of the values. You define in the corresponding formula the conditions that a value must meet to be displayed as true. If you combine the AND function with the IF function, you trigger actions that Excel must perform based on the result. This is why the combination of IF and AND is a simple way to check your data according to several criteria and thus quickly obtain an overview.

The structure of the nested IF and AND functions is as follows:

=SI(ET(condition1; condition2); valeur_si_vrai;valeur_si_faux)

Imagine that you want to check that the values ​​of a data collection meet conditions A and B. The result C should only be displayed only if both conditions are met, otherwise result D must be displayed. In the formula above, the element condition1 therefore corresponds to condition A and condition2 under condition B. valeur_si_vrai is the result C and the valeur_si_faux corresponds to result D.

Using Excel's IF AND function: practical example¶

In our example, we use an Excel table with five orders received. We indicate their amount in Euros and if they were carried out by new customers (value oui) or existing customers (value non). In an additional column, we want to know if the order in question is linked to a discount or not:

THE new customers receive a discount if their order is of at least €50.00. On the other hand, existing customers must pay the full price. To benefit from a reduced price, you must therefore validate two conditions. To determine which orders are discounted using Excel's IF AND function, click cell D2 and insert the following formula into the formula bar:

=SI(ET(B2="oui";C2>49,99);"OUI";"NON")

After confirming the formula with the Enter key, an answer to your query will automatically be displayed in cell D2:

To also get an answer in cells D3 to D6, now drag the green frame to the last row of the table to fill. Since it is a formula with a relative cell referencecells B2 and C2 will automatically be adjusted to B3 and C3, and so on:

Combine Excel's IF AND function with other functions¶

Excel formulas have the advantage of being able to be combined with each other if necessary. This gives you the opportunity to facilitate more complex situations. In the following example, we nest the IF AND function with the closely related IF OR function.

Unlike the previous example, the table below contains an additional column that indicates whether the order in question was placed by an employee. Just like new customers, employees receive a discount if their order reaches the value of €50.00. Must therefore either answer only column B either answer column C with oui.

Use the following formula to start the search:

=SI(ET(OU(B2="oui";C2="oui");(D2>49,99));"OUI";"NON")

The first value of Excel's IF AND function has been replaced by the IF OR function and its two conditions. This time, only one of them must be completed for the discount to be issued. The prerequisite, however, is always that the order value in column D is at least €50.00. After entering the combined function, you get the following responses:

Excel allows you to nest functions up to 64 levels. Multiple IF statements in a formula are complex to handle and require great concentration. In many cases it is possible to use alternatives such as the SEARCH function or the INDEX function, which however require additional reference tables.