Excel AND Function

Excel function tutorials

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

Today, I decided to look at the excel function AND. What does it do exactly? First, I would like to clarify that there are two main ways you would use the “AND” function. You can either use it in a nested if statement or you can use by itself. You might not understand what I mean but I will explain this much more clearly with examples.

Why You Would Use The Excel AND Function

There are many different ways to use the “AND” function. Basically, it is used to verify if a number of conditions are ALL met at once. It’s important to understand this. The objective of the function is to verify that all conditions are met. You could use 2 5 or even 10 conditions to verify. If only one of those is false, the function will return a negative value.

How Do I Use The And Function?

The first question to ask yourself is: “What type of value do you need the function to return?”

Basically you have two options:

#1-The function will return “TRUE” (if all conditions are met) or “FALSE” (if at least 1 condition is not met)

#2-You can decide what the function will return in both cases

The solution #1 only requires the use of “AND” while the solution #2 uses a nested function “IF(AND)”. If ever you are interested in reading more about nested functions in excel, you can visit a post we wrote about it here. Obviously, the second one is much more flexible and that is the one I would normally use. However, let’s start with a quick example of the “AND” function.

If I had this table of rankings and wanted to easily see which players are Russian, have played under 20 tournaments and whose name is less than 18 characters, here is what I could do:

AND (condition #1, condition #2, condition #3)

In this case, I would do:

=AND(E2=”RUS”,G2<20,LEN(C2)>18)

You can see the result here and in fact I could use conditional formatting to make it even more obvious:

Let’s imagine however that I would want to use either a formula or change the way the information is presented. Then, it would be easier to do an “IF AND” excel function as follows:

=IF(AND(E2=”RUS”,G2<20,LEN(C2)>18),”OK”,””)

As you can see, the difference is very small between the two so I think in most cases, it makes a lot more sense to use the “If And” function.

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

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