Excel AND Function
Summary
The Excel AND function is a logical function used to require more than one condition at the same time. AND returns either TRUE or FALSE. To test if a number in A1 is greater than zero and less than 10, use =AND(A1>0,A1<10). The AND function can be used as the logical test inside the IF function to avoid extra nested IFs, and can be combined with the OR function.
Purpose
Test multiple conditions with AND
Return value
TRUE if all arguments evaluate TRUE; FALSE if not
Syntax
=AND (logical1, [logical2], ...)
Arguments
- logical1 - The first condition or logical value to evaluate.
- logical2 - [optional] The second condition or logical value to evaluate.
Usage notes
The AND function is used to apply more than one condition at the same time, up to 255 conditions. Each logical condition (logical1, logical2, etc.) must return TRUE or FALSE, or be arrays or references that contain logical values.
Examples
To test if the value in A1 is greater than 0 and less than 5, use the following formula:
=AND(A1>0,A1<5)
You can embed the AND function inside the the IF function. Using the above example, you can supply AND as the logical_test for the IF function like so:
This formula will return "Approved" only if the value in A1 is greater than 0 and less than 5.
You can combine the AND function with the OR function. The formula below returns TRUE when A1 > 100 and B1 is "complete" or "pending":
See more examples of the AND function in formulas below.
Notes
- The AND function is not case-sensitive.
- The AND function does not support wildcards.
- Text values or empty cells supplied as arguments are ignored.
- The AND function will return #VALUE if no logical values are found or created during evaluation.
AND formula examples
Data validation rules are triggered when a user adds or changes a cell value.
The AND function takes multiple arguments (logical expressions) and returns TRUE only when all arguments return TRUE. In this case, we need...
The goal of this example is to verify input before calculating a result. The key point to understand is that any valid formula can be substituted. The SUM function is used only as an example. The logic can also be...
In the example shown, row 5 is a header row and which contains a series of valid dates, formatted with the custom number format "d". With a static date in D5, you can use =D5+7 (copied across) to populate the calendar...
In the image shown, the current date is August 19, 2019.
Excel dates are serial numbers, so you can manipulate them with simple math operations. The TODAY function always returns the current date. Inside the AND...
Data validation rules are triggered when a user adds or changes a cell value.
The AND function takes multiple arguments (logicals) and returns TRUE only when all arguments return TRUE. The DATE function creates a...
This formula relies on a technique called "nested IFs" to handle a series of options and results. With nested IFs, one IF function is nested inside another, a process that is explained in some detail here.
The formula...
When you use a formula to apply conditional formatting, the formula is evaluated for each cell in the range, relative to the active cell in the selection at the time the rule is created. So, in this case, if you apply...
In the example shown, we simply want to "mark" or "flag" rows where the color is "red" AND size is either "small" or "medium". To return TRUE when items are red and small, we can use a logical statement constructed...
The value in G5 is hard-coded. The formula picks up the value in G5, then subtracts the value (if any) in E6 and adds the value (if any) in F6. When the credit or debit values are empty, they behave like zero and have...
The challenge with lookup formulas that retrieve more than one match is managing duplicates (i.e. multiple matches). Lookup formulas like VLOOKUP and INDEX + MATCH can easily find the first match, but it's much harder...
In this formula, the logical test is this bit:
AND(OR(B5="red",B5="green"),C5>10)
Note that the "parent" function is AND The OR function is logical1 inside the AND function while C5>10 is...
The purpose of this formula is to demonstrate how other formulas and functions can be nested inside the IF function.
The IF function takes three arguments like this:
=IF(logical_test, value_if_true, value_if_false...
Data validation rules are triggered when a user adds or changes a cell value.
The UPPER function changes text values to uppercase, and the EXACT function performs a case-sensitive comparison.
The AND function takes...
In the example shown, we want to test if all values in each row are equal. To do this, we use an expression that compares the value in the first column (B5) to the rest of the columns (C5:F5):
B5=C5:F5
Because we...
The trick with this approach is the calendar header (row 4), which is just a series of valid dates, formatted with the custom number format "d". With a static date in D4, you can use =D4+1 to populate the calendar....
Related videos
In this video, we'll look at how to extend a conditional formatting formula so that so that it checks more than just one condition.
In this video, we'll look at how to use conditional formatting to highlight entire rows using multiple criteria.
In this video we look at how to combine the IF function with the OR and AND functions to test more than one condition at the same time.
In this video, we show you how to create a conditional formatting rule that uses a formula to highlight cells that meet specific conditions not included in Excel's presets.
In this video, we'll look at how to highlight the results of approximate match lookups with conditional formatting.
Related functions
