The IF and AND functions can be used together in many functions. However, before we proceed further, lets learn about the two functions individually and understand how they perform in Microsoft Excel 2010.
IF: -IF condition allows us to use multiple conditions in a cell. It helps to make the function flexible. Excel allowsupto a max of 64 conditions which can be checked using this function.
Syntax of “IF” function: =if (logical test, [value_if_true], [value_if_false])
Example:Cell A1 and A2 contain the numbers 3 and 5 respectively. Lets put a condition that if the number in the cell is 3 then the formula should display “Yes” otherwise “No”.
AND: -Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or more arguments evaluate to FALSE. It performs for all logical test.
Example:Column A contains 3 numbers and we need to check if the cell contains anumber greater than 10 and less than or equal to 50.
Syntax of “AND” function: =AND (logical1,[logical2]…)
Here we can see that the function returns False in 2 cells and True in 1 cell. Since both the conditions evaluated to False, the result was False. In the last row, both the conditions evaluated to True, hence the result is True.
Let’s take an example & understand how we can use the “And” function along with the “IF” function.
Our condition is – If A1 is greater than 10 and less than 50, return YES, else NO.
- Select the cell B1 and write the formula
- Press enter on the keyboard.
- If the cell contains the number 60 then the function will return “NO” and if the number in the cell is greater than 10 and less than or equal to 50, then the function will return “YES”.
So this is how we can use the IF and the AND formula together to arrive at the result based on two or more criteria.