In Microsoft Excel, combining multiple functions to get output is everyday work. We use various operators to extract a particular result from a formula. The **IF** function and **AND** function are one of them. Incorporating **IF **with **AND **works like magic. In this tutorial, you will learn to combine **IF** with **AND **in an Excel Formula with suitable examples and proper illustrations.

**Table of Contents**hide

## Download Practice Workbook

Download this practice workbook.

## 7 Examples of Combining IF with AND in an Excel Formula

Now, we all know the **IF **function basically operates based on the value that the statement return. The **IF **function can have only one condition in it. To implement multiple criteria, we can take the help of the **AND** function. It is a logical operator. It will also return **TRUE **or **FALSE **based on the statement. This **TRUE **or **FALSE **will pass on the **IF **condition. After that, the **IF** function will evaluate something based on these results.

If you don’t know everything about **IF **and **AND **operators, make sure you check these before:

In the following sections, we will provide you with seven examples of **IF **with **AND **in an Excel formula. We recommend you read and learn all of these. It will surely improve your Excel knowledge.

### 1. Greater than x But Less than y with IF & AND Function

In this example, our **AND** function will contain multiple conditions greater than or less than the operator. We will show you three instances here in this example.

**The first instance is this:**

Your budget is between **$500** to **$1000** to buy a TV. A price less than **$500** won’t be good. Also, you don’t want to cross **$1000**. In this case, you can use the **IF **and **AND **find it.

To get this, we are using this formula:

`=IF(AND(B5>500,B5<1000),"YES","NO")`

As you can see, the formula successfully shows which one to buy based on the statement.

**Second Instance:**

You can also perform this with the “**Greater than or equal** **(>=)” **and **“Less than or equal (<=)**” operators.

Just a simple tweak in the formula:

`=IF(AND(B5>=500,B5<=1000),"YES","NO")`

**Third Instance:**

Now, you can perform the same thing with **absolute cell references. **Take a look at the following screenshot to have a better understanding.

**Formula:**

`=IF(AND(B5>=$F$4,B5<=$F$5),"YES","NO")`

As you can see, we have used the absolute cell references to perform the operation with **IF **and **AND **functions.

**Read More:** **Excel VBA: Combining If with And for Multiple Conditions**

### 2. Formula with IF and AND Function: Compute Something

Now, apart from returning a particular value, the **IF **function can also perform calculations in it. The estimations will depend on the return value **TRUE** or **FALSE**. But, this time **IF** function will also contain the **AND **function.

Take a look at the following dataset:

Here, the dataset demonstrates some employees’ salary, their serving years, and the number of their completed projects. Now, we will increment their salaries. The condition is: “**If someone is serving more than 4 years and completed more than 10 projects, increase their salary by 10%. Otherwise, increase their salary by 6%.**”

📌 **Steps**

- First, type the following formula in
**Cell F5**:

`=IF(AND(D5>4,E5>10),C5*10%,C5*6%)`

- After that, press
**Enter**and drag the**Fill handle**icon over the range of cells**F6:F10.**

As you can see from the result, our **IF-AND **formula successfully returns the desired value based on the conditions.

### 3. Formula Using IF Function with Multiple Conditions in AND

From the previous examples, you noticed that we used only two conditions in the **AND** function. But, you can use more than two statements in an** IF AND** formula. But there are some limitations to this:

- You can use up to 255 arguments in a formula in Excel 2019 – 2007. The length should not cross more than 8192 characters.
- In previous versions (Excel 2003 and lower), you can’t use more than 30 statements. The total length can not exceed 1,024 characters.

Take a look at the following dataset:

Here, we have a dataset of some orders from the customers. Now, we want to give them some discount based on some conditions.

The condition is: **“If any customer is in VIP category, No. of purchase more than 5 and buying with a price more than $400, give them a discount”.**

📌 **Steps**

- Now, type the following formula in
**Cell F5**:

`=IF(AND(C5>400,D5="Yes",E5>5),"Yes","")`

- Then, press
**Enter**and drag the**Fill handle**icon over the range of cells**F6:F10.**

As you can see, our formula returns the exact value. So, we successfully used the **IF **and **AND **functions for an Excel formula.

**Read More:** **VBA IF Statement with Multiple Conditions in Excel (8 Methods)**

### 4. Using Nested IF-AND in an Excel Formula

In this example, we will combine multiple** IF AND** functions in the same formula. You can call it the nested **IF** **AND **formula.

Take a look at the following screenshot:

Here, we have some students’ attendance days and their marks. Now, the conditions are:

**If any student attended more than 20 days and scored more than 70, the Remarks is Outstanding.****If any student attended more than 20 days and scored less than 70, the Remarks is Average.****Set the Remarks to Poor if any student attended less than 20 days.**

📌 **Steps**

- First, type the following formula in
**Cell E5**:

`=IF(AND(C5>20,D5>70),"Outstanding",IF(AND(C5>20,D5<70),"Average","Poor"))`

- Next, press
**Enter**and drag the**Fill handle**icon over the range of cells**E6:E10.**

As you can see, we successfully used multiple conditions in our IF-AND formula. The formula returns the exact results that we wanted.

**Read More:** **Excel If Function with Multiple Conditions (Nested IF)**

**Similar Readings**

**Excel VBA: If Then Else Statement with Multiple Conditions (5 Examples)****Example of VLOOKUP with Multiple IF Condition in Excel (9 Criteria)****How to Use Multiple If Conditions in Excel for Aging (5 Methods)****How to Use Multiple IF Condition in Excel (3 Examples)**

### 5. Using IF AND Formula for Case-Sensitive Data

Now, the** IF AND** formula do not differentiate between uppercase and lowercase letters. It happens because the **AND **function is case-insensitive by default.

To solve this, we can use **the EXACT function** to make this formula a case-sensitive one. We will nest it with the **AND **function. If you are working on case-sensitive data, you should use this according to my opinion.

Take a look at the following dataset:

Here you can see some persons’ names and their serving years in a company. Also, you can see there are multiple persons named Robert. Our condition is: “ **Find the match for name Robert with serving years of more than 15 years**”.

📌 **Steps**

- Now, type the following formula in
**Cell D5**:

`=IF(AND(EXACT(B5,$G$4),C5>15),"Yes","")`

- Then, press
**Enter**and drag the**Fill handle**icon over the range of cells**D6:D10.**

As you can see from above, our **IF AND **formula find the exact match based on the condition.

### 6. IF AND Formula with a Date in Excel

If you have followed the previous methods, this example won’t be difficult for you. You can compare dates in the formula. In this example, we will use a date to demonstrate this problem.

Here, we have a dataset of some employees’ joining dates and the number of projects they have completed. Our condition is: “**If someone joined before 1 January 2022 and completed projects more than 10, give them a promotion**”.

📌 **Steps**

- First, type the following formula in
**Cell E5**:

`=IF(AND(C5<$H$4,D5>$H$5),"Promotion","")`

- Then, press
**Enter**and drag the**Fill handle**icon over the range of cells**E6:E10.**

As you can see, both Stacy and Mike joined before 1 January 2022 and completed more than 10 projects. So, our formula successfully returns a value based on the conditions.

### 7. Combining IF AND with OR in an Excel Formula

We are not limited to using one logical operator in a formula. In fact, you can use multiple logical operators in an **IF AND **formula. It basically checks various combinations of your given conditions. These logical tests allow you to analyze complex criteria in a dataset.

To demonstrate this, we are using **the OR function** in our **IF AND **formula in Excel.

Here, we have some salespersons’ names, their selling products, and the selling prices. Now, our condition is: “**Find a match where a salesperson sold TV or Laptop for more than $1500**”.

📌 **Steps**

- Now, type the following formula in
**Cell E5**:

`=IF(AND(OR(C5=$H$4,C5=$H$5),D5>$H$6),"Yes","")`

- Next, press
**Enter**and drag the**Fill handle**icon over the range of cells**E6:E10.**

Here you can see, our **IF AND** formula found two matches for the particular condition. In this way, you can use multiple logical operators in Microsoft Excel.

**Read More:** **Excel VBA: Combined If and Or (3 Examples)**

## 💬 Things to Remember

**✎ **The formula will show **#VALUE!** Error if it can not evaluate it with **TRUE **or **FALSE**.

**✎ **If you provide an array as an argument, the IF function will evaluate every element of that array.

✎ To count conditions, you can use **the COUNTIF function** or **the COUNTIFS function**.

## Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge about the **IF AND **formula in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website **Exceldemy.com** for various Excel-related problems and solutions.

Keep learning new methods and keep growing!