A tool often used in spreadsheets is the combination of IF and AND functions. This allows you to create targeted queries that check multiple conditions and provide immediate results. To learn how the IF and AND functions work when nested in Excel and in which situations to use this combination, read the rest of this article.
Excel with Microsoft 365 for Business and IONOS!
Use Excel to organize your data and save time – included in all Microsoft 365 packages!
Office Online
OneDrive with 1TB
24/7 support
These instructions are valid for Microsoft 365, as well as Excel 2021, 2019, and 2016 versions.
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.
If only one of the two conditions must be met for result C, use the IF OR function. With this function, Excel returns result D only if neither condition A nor condition B are met.
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.
Microsoft 365 for businesses with IONOS!
The latest version of your favorite Office apps on all devices.
Free installation assistance!
Exchange up to 50 GB
OneDrive with 1TB
24/7 support