AND Function

The Excel AND function is one of the logical functions. Learn how to determine if all conditions in a test are TRUE.

Using Excel AND Function

Here is how the AND function works:

The AND function tests multiple conditions with AND and returns TRUE if all arguments evaluate TRUE; FALSE if not. The goal to use the AND functions is to perform more than one logical test simultaneously.

AND has two outputs: TRUE or FALSE.

For example, to test if a number in cell C5 is greater than 150 and less than 180, use the formula below:

=AND(C5>150, C5<180)

If both conditions are TRUE, the result is TRUE.

If you use the IF logical function with multiple logical tests, it’s a great idea to use the AND logical function instead of a nested IF function.

Let’s see an example:

=AND(C5>150,C5<180) is equivalent to =IF(AND(C5>150,C5<180,TRUE,FALSE))

The first formula is easy-to-readable.

Syntax and Arguments of AND function

Syntax:

=AND(logical test1, logical 2 test,…..)

Arguments:

  • logical test1: the function evaluate the first condition
  • logical test2: [optional argument], the second condition

Examples

In the first example, you want to test if the value in cell C7 is greater than 100 and less than 160.

Use AND the following formula:

=AND(C7>100, C7<160)

excel and function example 1

In this case, the result is FALSE. Take a closer look at the formula:

  • =C7 > 100 return TRUE
  • =C7 < 160 return FALSE

Based on the function’s definition the result is FALSE because if any value evaluates to FALSE, the AND function will return FALSE.

Combine AND and IF function

In the next example, you can place the AND function inside an IF function.

=IF(AND(C3>100, C3<500), “Yes”, “No”)

This formula will return “Yes” if the value in C3 is greater than 100 and less than 500. Else the formula returns FALSE.

combine IF and AND function

Combine AND and OR function

Logical functions work together without any trouble. Let us combine the AND function with the OR function.

=AND(C3=200,OR(D3=”Product 1″,D3=”Product 2″))

The formula returns TRUE when C3 = 200 and D3 is “Product 1” or “Product 2”

excel combine OR and AND function

In the example, the only row that contains values that met the criteria is 4.

AND Function returns #VALUE error

Finally, let us see how the function works if the formula does not contain logical values. Type the formula below:

=AND(C4, D4)

Result:

and return value error

Wrapping things up

The AND function:

  • has two outputs: TRUE or FALSE
  • not case-sensitive
  • can handle up to 255 arguments
  • does not support wildcards
  • text values or empty cells are ignored
  • return the #VALUE error if no logical values are found or created during evaluation

Additional resources:

Istvan Vozar

Istvan is the co-founder of Excelkid. He writes blog posts and helps people to reach the top in Excel.