The AND and OR functions in Excel both belong to the logical function category in the Excel function list.
These functions test multiple conditions and return the logical value TRUE or FALSE dependent upon the result of their logical tests.
Returning TRUE or FALSE is often not sufficient on its own, so these functions are often combined with the IF function in Excel or used with a feature such as Conditional Formatting or Data Validation.
Download the sample file for practise.
AND Function
The AND function in Excel only returns TRUE if all conditions are met. If any condition fails, the AND function returns FALSE.
The syntax for the Excel AND function is:
AND(logical_1, [logical_2], ...)Only one condition is mandatory, but AND can handle up to 255 conditions.
The following AND formula tests the values in columns A and B. Both criteria must be met for the result to be TRUE.
=AND(A2="Platinum",B2>=5)
In this example, only the last formula returns TRUE, as it is the only row where the text string in column A is equal to “Platinum” and the value in column B is greater than or equal to 5.
OR Function
The OR Excel function returns TRUE if one or more conditions are met. OR will return FALSE only if all conditions fail.
The syntax for the Excel OR function is:
OR(logical_1, [logical_2], ...)The following OR formula tests the values in columns A and B. Only one condition needs to be TRUE for the formula to return TRUE.
=OR(A2="Platinum",B2>=5)
In this example, only the third row of the range returns FALSE. This is the only row where neither condition was met. Column A does not equal “Platinum” and column B is not greater than or equal to 5.
Combined with the IF Function
A key role of these functions is as a nested function with IF, as on its own, the IF function can perform only a single logical test.
In this Excel formula, the AND function is used to test if both criteria are TRUE, and if so, the IF function calculates a 10% discount, otherwise, the price remains the same.
=IF(AND(A2="Platinum",B2>=5),C2*(1-10%),C2)
Only the fourth and final row of the range has a 10% discount applied in the result. This is because the AND function returns TRUE when both arguments (Platinum and >=5) evaluate to TRUE.
In this article, you can see examples of OR being used with the IF function for partial text matches. This is a cool technique, as when you perform logical tests on text values, an exact match is required.
Excel AND Function for the Between Condition
The AND function is used to perform the between condition in Excel. For example, to test if a single value is between 60 and 90. This may be an important range for a business KPI to be within.
This formula tests if the value in column A is in the specified range of 60 to 90. If so, the IF function returns “Yes”, otherwise an empty cell is shown.
=IF(AND(A2>=60,A2<=90),"Yes","")
There are many other examples to require between logic including to work with age ranges and date ranges.
Using the VLOOKUP function in Excel as a range lookup is another neat way to perform the between condition in Excel.
OR and the AND Function Together
Using both functions together can get complex, but it enables us to create stronger logic in our formulas.
The following formula uses OR to return TRUE if the tier is either “Platinum” or “Gold”. The AND function uses the result from OR and the function tests if the sessions are greater than or equal to 5.
The If function applies a 10% discount only if the AND function returns TRUE. In this example, that is the first and last rows of the range only.
=IF(
AND(OR(A2="Platinum",A2="Gold"),B2>=5),
C2*(1-10%),C2
)
Multiple Conditions with Conditional Formatting
You can also use the AND function inside a Conditional Formatting rule. Both functions are not limited to returning values to the sheet.
In the following video, the AND function (and OR) are used to test multiple columns and highlight the rows where the functions return TRUE. A COUNTIF function example is also included in the video.
What Does <> Mean in Excel?
The <> symbol means “not equal to”. For example, the formula =A2<>”London” tests if the value in cell A2 is not equal to “London”. Therefore, the formula returns TRUE if cell A2 contains any value except “London”.
Microsoft Excel also has the NOT function to perform a “not equal to” logical test.
Of course, this logic can be used with an OR or AND function in Excel.
Wrap Up
In this tutorial, we have seen a few examples of the AND and OR Microsoft Excel functions to show how useful they are.
In my Advanced Excel Formulas book, you will learn more than 15o of the best Excel functions supported with over 500 examples. Practise files are included.
