How to use multiple conditions in excel

We have been writing on this blog for a few months now but had not had the opportunity to discuss some excel topics. Well’, we are now ready and should be discussing a few in the next few weeks. First off, we’ll start with a question that we received last week. Please feel free to send us any excel related questions and we’ll do our best!  Here is the first one:

I am trying to find a formula to help me come up with the following results.

Below is the example. Note:Bryan, ryan, Bryce and Ryce are Internal Buyers and rest of them are External buyers.

Basically I am trying to get the following result

Result 1 = If buyer for any of those co. is Internal buyers or if the cell is blank then put an X there.

Result 2 = If buyer is External buyer(at least in one of the co.) then put X there.

The question seems complex but if you read it carefully, this is simply a case of using multiple conditions. What do I mean? For each product, we are attempting to find if at least one of the buyers is “External”. As in any other problem, there are many different ways to resolve this. I will go ahead and show you how I would resolve it.

Step #1 – Determine who the “Internal Buyers” are:

I will simply set up the name of those buyers and will then be able to reference them. So here we go:

Step #2 – For each product, determine if the buyers are external or not.

In this case, what I am looking for is confirmation that at least one buyer is external. How will I do this? Simply by using a vlookup. If the vlookup result is false (the name is not on the list) and there is one buyer, it means that the buyer is external. How am I doing it? Simply by adding a column. I could add one column per Co.A so 4 columns but in this situation, I simply need to verify if at least one of them is external. So here we go:

The formula in H2 is:

=IF(B2<>””,IF(ISERROR(VLOOKUP(B2,J:K,2,FALSE)),1,VLOOKUP(B2,J:K,2,FALSE)),0)

Which means: If Co.A has a buy (is not empty), look in the column j. If you find it, great, put 0. However if you do not find it (and have an error), please put 1 instead of the error. In this way, I can easily tell that for both Apple and Banana, the buyer from Co. A is external.

I will simply do the same thing for the 4 Co.

The result in cell H2 is:

=IF(B2<>””,IF(ISERROR(VLOOKUP(B2,J:K,2,FALSE)),1,VLOOKUP(B2,J:K,2,FALSE)),0)+IF(C2<>””,IF(ISERROR(VLOOKUP(C2,J:K,2,FALSE)),1,VLOOKUP(C2,J:K,2,FALSE)),0)+IF(D2<>””,IF(ISERROR(VLOOKUP(D2,J:K,2,FALSE)),1,VLOOKUP(D2,J:K,2,FALSE)),0)+IF(E2<>””,IF(ISERROR(VLOOKUP(E2,J:K,2,FALSE)),1,VLOOKUP(E2,J:K,2,FALSE)),0)

It seems much more complicated than it really is. And basically, from here, I can easily tell that the Apple, Banana, Grapes and Peach have at least one external buyer.

Step #3 – Use simple conditions in “Result 1” and “Result 2” columns

I will simply replace those 2 columns by formulas:

For result1, I have: =IF(H2=0,”X”,””)

For result2, I have: =IF(H2=0,””,”X”)

That’s it! Not complicated and flexible to change depending on your needs. You can download the spreadsheet used here!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss

***************************************************