Nested formula allows us to use the multiple conditions in a cell. It helps us to create the flexibility of the function in Microsoft Excel. This function is very useful when we want to check the multiple conditions. Also, Excel allows us to check 64 conditions in a cell.
In this article, we will use the IF function along with OR & AND function.
IF function is used to check whether a condition is met and returns one value if True and another value if False.
The syntax of “IF” function =if(logical test,[value_if_true],[value_if_false])
AND function is used to check whether all arguments are true and returns TRUE.
The syntax of AND function =AND(logical1,[logical2],….)
OR function is used to check whether any of the arguments is true and returns true or false.
The syntax of OR function =OR(logical1,[logical2],….)
Let’s take an example to understand how the nesting condition works in Microsoft Excel.
We have data in range A1:H11 in which column A contains Date, Column B designation, column C Sales, column D contains city name, column E units.
We want to create a formula that returns “1? if Unit=10 and either Designation=Marketing Leader or Sales=Direct.
Follow below given steps:-
- Enter the formula in cell F2.
- =IF(AND(E2=10,OR(B2=”Marketing Leader”,C2=”Direct”)),1,0)
- Press Enter.
- Copy the same formula in the range.
Note: In this formula, AND function is used for checking the multiple criteria such as if Unit is 10, then with or function formula will check the designation and Sales if anyone will be correct then formula will return the result accordingly.
Conclusion:- When we want that multiple conditions should be true, we will use AND function and to know that is any one condition is true or not, we use OR function and IF function always to check whether the condition is met or not.
This is the way we can use OR & AND functions to meet multiple criteria in Microsoft Excel.