Presentation of the functions

The AND and OR functions are very useful with logical tests in Excel. Basically, a logical test compares two items only. But if you want to create a test with more than one condition, you must use this function.

• Logical Test – Beginner Level
• Logical Test – Intermediate Level

AND function

A condition AND returns TRUE if and only if all tests in parenthesis are true.

=AND(test 1,test 2,test 3, …)

For instance, you may have a list of hotels based on criteria such as the number of stars (classification), the price of the menu, etc. You want to select a hotel according to these criteria

• At least 3 stars (B2>=3)
• The price of the menu is a maximum of 35 (C2<=35)
• And the hotel has a Fitness room (E2=”Yes”)

So the formula is

=AND(B2>=3,C2<=35,E2=”Yes”)

And when you copy the formula for all the hotels, you have this result

As you can see, the test returns TRUE only if all the 3 criteria are TRUE.

OR function

A condition OR returns TRUE if one (or more) tests of the function is true. So, you use the OR function in a different situation where you accept many cases.

= OR(test 1,test 2,test 3, …)

For instance, here, you want to select only the people who are single or divorced

=OR(F2=”Single”,F2=”Divorced”)

Combination of AND and OR functions

But also, you can combine the 2 functions to create more sophisticated tests. For instance, we still want to select people who are divorced or single but also with no child. The formula is

=AND(G2=0,OR(F2=”Single”,F2=”Divorced”))

Explanations:

• First, the test will check the status Single or Divorced. The OR will return TRUE when one of these values is found.
• Then, we embed the previous in the AND function to return TRUE when both results are TRUE

Related articles

• Training Logical Test – AND and OR functions
• Function XOR in Excel
• Convert TRUE or FALSE to 1 or 0 in Excel