How to use Excel AND Function (With Examples)

The Excel AND function lets you test two or more conditions simultaneously. It returns TRUE or FALSE as the output.

TRUE only if all the conditions are met
FALSE when any of the given condition is not met

For example, you can check whether the number in cell A1 is greater than 10 and less than 100. Here’s how the formula will look:

=AND(A1>10,A1<100)

Where,

A1>0 is the first condition to identify if the number is greater than 10
A1<100 is the second condition to identify if the number is less than 100

Syntax

The AND is a simple function with the following syntax. It is combined with other functions like IF, NOT, and OR to create complex formulas in Excel.

=AND(logical1,[logical2],...)

Where logical1 and logical2 represent conditions. You can use comparison operators to define conditions or criteria. They are also known as calculation or logical operators in Excel.

“=” – Equal To
“<“- Less Than
“<=” – Less Than or Equal To
“>” – Greater Than
“>=” – Greater Than or Equal To
“<>” – Not Equal To

Important Notes:

You can input up to 255 logical conditions
The function is not case-sensitive
It does not support wildcards
You may get a #VALUE error if no logical values are found or created

How to use the AND Function in Excel

Download the example spreadsheet used to demonstrate the AND function in the upcoming section. Practice is the key to success!

Download Now

Example 1: Check if the number is between two numbers

Suppose that we are hiring candidates based on their years of work experience. The experience is available in column C, as shown in the following image. Our task is to find candidates with 3 to 5 years of experience.

Excel AND Function Example - Syntax, Examples, and More

Here are the steps,

Select the cell D1
Type =AND
Select the first option from the popup
Specify the first condition as C2>=3
Type , on your keyboard
Specify the second condition as C2<=5
Complete the bracket using , on your keyword
Press the Enter key

Our final formula is as follows,

=AND(C2>=3, C2<=5)

Where,

C2>=3 condition is to find the candidates with experience greater than or equal to 3 years.
C2<=5 condition is to find the candidates with experience less than or equal to 5 years.

The results are displayed in the following image,

Example 2: Combining IF with AND function in Excel

Suppose you plan to offer a bonus to employees with an 8+ performance rating out of 10. The bonus is applicable only to those who have worked for a minimum of two years for your company.

Refer to the following data. Here we have,

Employee names in the first column
Performance ratings in the second column
Years of Service in the third column

But before we jump into the actual steps, let us first understand the IF function.

Explained: The IF Function in Excel

The IF is a logical function in Excel. It can test a condition and return values based on whether the condition is satisfied or not.

The IF function syntax is as follows:

=IF(logical_test, [value_if_true], [value_if_false])

Where,

logical_test is used to define the condition to be tested.
value_if_true is an optional argument where you can define the value to return if the condition is satisfied.
value_if_false argument lets you define the value to return if the condition is not satisfied.

Next, let us find the employees as discussed earlier. Here are the steps,

Click on the cell D2
Type =IF
Select the first option from the popup
Type AND
(As AND is a function in Excel, you will see the following popup)
Double-click on the AND command
Specify the first condition as B2>=8
Type ,
Specify the next condition as C2>=2
Complete the bracket for the AND function using )
Type ,
Specify the value “Yes” if the logical test is TRUE
Type ,
Specify the value “No” if the logical test is FALSE
Complete the bracket for the IF function using )
Press Enter key

Our final formula is as follows,

=IF(AND(B2>=8,C2>=2),"YES","No")

Where,

B2=>8 is used to find the employees with performance ratings equal to greater than 8
C2=>2 is used to find the employees who have been associated with the company for more than 2 years
Eligible is the desired value to be returned if the employee satisfies the given conditions
Not Eligible is the desired value to be returned if the employee doesn’t satisfy the given condition

The results are displayed in the following image,

Takeaway

Learning the Excel AND function is a crucial step. It helps you create complex formulas. You can combine it with many other built-in functions such as IF, OR, LAMBDA, NOT, etc. The function comes with a simple syntax. Anyone can learn it without any prior spreadsheet experience.

I hope this article taught you all the bells and whistles of the Excel AND function. Please comment below if you are stuck or encounter any particular error while using the Excel AND function. I will answer your questions as soon as possible.

Additional Resources: