Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

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:
Solution examples
I have a list or people who are ranked in numbers from 3 to 6 I need to recognize the contents of each multiple cells and create an equivalent letter. for that value into another column. e.g. Cell E1 = 3 to show in new cell that row (H1) the letter "C" I have multiple rows with different values in column "E" Can this be done?
Solved by I. J. in 30 mins
Hello good afternoon, I have a problem with excel. I need a formula to compare data between 3 columns and give me the result in a fourth column
Solved by X. B. in 58 mins
I want to display a range of cells if they meet a specific criteria. I want to display a name IF it does not equal one of three options AND there are any numbers contained in a range of cells
Solved by G. F. in 60 mins
can you teach me the steps to create a nested if function, and to nest an AND function inside of an IF function?
Solved by A. Q. in 22 mins
Could you help me by explaining the IF function?
Solved by G. L. in 24 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc