Go Back

If this AND that OR that

Many times, we have to consider 2 conditions collectively (if this and that or that). In such a 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 along with the IF function. 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.

If this AND that OR that

Formula

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

Explanation

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

Example 1

 

Figure 1. If this and that or that

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 that-clauses. We can also change the values which are returned by IF accordingly.

 

Figure 2. If this and that or that

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

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I have a problem regarding to If cell contains this or that
Solved by T. H. in 30 mins
need help with formula i want a formula where if value is that do this or that type problem please help me with that asap
Solved by T. J. in 27 mins
I have one column that has a vlookup that looks up a value from a different tab. I use the iferror to return a zero instead of #N/A. In ta column adjacent to this one I have an IF statement that reads like this: If that cell is equal to zero than return v2 or(to be sorted) else return v3 or(sorting in progress). For some reason this is not recognizing the zero values in my lookup column and returns the same result no matter if I have zero or 10. i'm stumped as to why this is happening
Solved by Z. H. in 23 mins

Leave a Comment

avatar