< Go Back

If this AND that OR that

For an Excel user, sometimes we have to consider two conditions collectively. In such case, we have only two alternatives, AND clause and OR clause. Both the clauses have only two possible results, i.e., TRUE or FALSE. If we make use of OR clause, then the result will be TRUE if either one condition or both conditions are true but in case of AND clause, the result will be true only if both conditions are true. We can also make use of both AND & OR Clause at the same time also along with the IF function.

USE OF IF Function with AND /OR clause

The IF function and AND/ OR clauses are all inbuilt functions of EXCEL. These may be used collectively at times when we have to compare a set of conditions and perform a specific task.

SYNTAX OF AND / OR CLAUSE WITH IF FUNCTION

=IF(AND(A1="x",OR(B1="y",B1="z")),"x","")

Example

In the example given below, it is clearly shown how these operations are helping in generating the desired result.

Figure 1: Using IF, AND and OR

In the above operation where we are checking whether the fruit sellers could fulfill the requirements of the customer.  

In E7, we are using the formula for finding the fruit suppliers who are supplying “red” apples which are either “medium” or “small” in size.

So, we’ll use the formula in E7 as shown:

=IF(AND(C7="red",OR(D7="small",D7="medium")),"YES","NO")

We can also use a logical statement constructed only with the AND function using:

AND (C7=”red”, OR D6=”small”, “medium”))

AND (C7=”red”, D6=”small”)

Thus, using the above formula we can solve all the queries related to this AND that, this OR. We can also change the values which are returned by IF accordingly.

Figure 2: Using IF, AND and OR

Similarly, you can find the results for other entries also by giving the conditions.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar