## What Is IF AND Function In Excel?

The

IF AND in Excelincludes two logical functions,ANDandIF,that checks for multiple criteria simultaneously. TheAND()output decides whether theIF()output should be theTRUEorFALSEvalue.Users can use the

IF ANDfunction when purchasing a stock or updating a status based on more than one condition.

For example, the below table contains six teams’ scores in two rounds of a quiz competition. We must decide the teams qualifying for the third round based on their previous two rounds’ scores using the **IF AND function**. Assume the qualifying score for the two rounds is 45.

Select cell **D2**, enter the formula **=IF(AND(B2>=45,C2>=45),”Qualified”,”Not Qualified”),** press “**Enter**”, and drag the formula from cell **D2 **to** D7** using the fill handle.

**[Output Observation:** In the above example, the **AND excel function** checks if the scores in the two rounds equal or exceed 45 for each team. And if both criteria hold, the **AND()** output is **TRUE**. If the **IF** condition is **TRUE**, it returns “**Qualified**” as the status for the corresponding team. Otherwise, the output is “**Not Qualified**”. Column E is for our reference.]

##### Table of contents

###### Key Takeaways

- The
is a formula that uses the**IF AND**in Excel**AND()**to check multiple criteria in one go. And based on whether the**AND**function returns**TRUE**or**FALSE**, the**IF**function output will be the**TRUE**or**FALSE**value. - Users can apply the
**IF AND**formula in Excel to check multiple conditions. For instance, the function helps decide whether to buy a commodity based on its price and discount percentage. - We can use the formula as it is or in different forms, such as “
**nested IF AND**”and**“IF-AND**–**OR**”, and with more than two conditions.

### IF AND() Excel Formula

The syntax for ** IF ANDExcel Formula** is,

And we can enter it in an Excel cell as:

The arguments for ** IF AND Excel Formula** are,

: It is the*logical_test***AND()**, which returns**TRUE**if all the specified conditions hold.: The*value_if_true***TRUE**value the**IF()**should return if thecondition is*logical_test***TRUE**.: The*value_if_false***FALSE**value the**IF()**should return if thecondition is*logical_test***FALSE**.

[**Note:** The above formula of the **IF()** shows the last two arguments as optional. The ** logical_test** (

**AND()**) and the

**arguments are mandatory, and only the third argument is optional in the**

*value_if_true***If And Excel statement**.]

### How To Use **IF AND** Excel Function?

We can use **If And in Excel** in 2 ways, namely,

**Access from the Excel ribbon.****Enter in the worksheet manually.**

#### Method #1 – Access from the Excel ribbon

Choose a target cell for output → select the “**Formulas”** tab → go to the “**Function Library**” group → click the “**Logical”** option drop-down → select the “**IF”** or “**AND**” function, as shown below.

The **Function Arguments** window appears. Enter the arguments in the “** Logical_test**,

**, and**

*Value_if_true***fields → click**

*Value_if_false”***OK**, as shown below.

#### Method #2 – Enter in the worksheet manually

- Choose a target cell for the output.
- Type
**=IF(AND()**in the cell. [Alternatively, type**=I**, double-click the**IF**function from the Excel suggestions, type**A**, and double-click the**AND**function from the Excel suggestions.] - Enter the arguments as cell values or references and close the brackets.
- Press
**Enter**to execute and get the required outcome.

The below table shows the order delivery details for a list of items. We must check if the total units delivered meet the minimum required number of units, and if the order delivery is before April for each item. And if the two conditions hold, the order status is complete. Otherwise, the status is pending.

The steps to use** IF AND in Excel** function in order to check delivery status are,

**Select cell E2, and enter the formula***=IF(AND(C2>=B2,MONTH(D2)<4),”Complete”,”Pending”)***Press Enter to view the order status.**

[Alternatively, select the target cell**E2**and go to**Formulas**→**Logical**→**IF**to apply the function in the chosen cell.

The above step will open the**Function Arguments**window. Enter the required conditions within the**AND()**and the**IF() TRUE**and**FALSE**values in the respective fields.

Finally, clicking**OK**will close the window, and we can see the**IF AND**formula output.]**Using the fill handle, drag the formula in cells E3:E7.****[Output Observation:**Let us consider the cell E7 expression to see how the formula works.

First, the**AND()**checks the two given conditions.

The first one is whether the number of units delivered,**900**, exceeds the required minimum units of**950**. And the second condition checks if the**MONTH()**output, the month of the specified date,**5**(a value indicating May), is less than**4**(a value indicating April).

But, as both criteria are**FALSE**, the**AND()**output is**FALSE**. Thus, the**IF**condition does not hold, and the**IF()**returns the**FALSE**value,**Pending**, as the**Item_6**order status.]

### Examples

We will consider some advanced scenarios using the **IF AND****in Excel** examples.

#### Example #1

The following table shows a list of branded items and their costs. Suppose, the store decides to give a 20% discount on items of the Gap brand that are less than $30. Otherwise, the discount percentage is 5%.

The steps to calculate using the ** IF AND in Excel** are,

**Step 1:**Select the cell range**C2:C7**, go to**Home**→**Number Format**, and set the data type as**Currency**.

**Step 2:**Select cell**C2**, enter the formulaand press*=IF(AND(A2=”Gap”,B2<30),B2*20%,B2*5%),***Enter**. The result is**$2.50**,

**Step 3:**Using the excel fill handle, drag the formula in the remaining target cells**C3:C7**.

[**Output Observation:** Let us check the cell **C7** expression to learn how the formula executes.

The **AND()** checks if the brand specified in cell **A7** is **Gap** and the item cost is less than **30**. And as both conditions are **TRUE**, the **AND()** output is **TRUE**. Therefore, the **IF** condition holds, and the **IF **function calculates and returns **20% **of the cell B7 value, **$5**.]

#### Example #2

The following illustration explains how to apply the **IF and AND function in Excel with multiple conditions**.

The below table shows a list of students, their overall aggregates, attendance data, and project submission status. We must update their scholarship eligibility, and the eligibility criteria are that the aggregate should be **450 marks** or above, and attendance must be **90** **days** or above. The project submission status should be **Yes**.

The steps to apply the **IF and AND function in Excel with multiple conditions** are,

**Step 1:**Select cell**E2**, enter the formula, and press*=IF(AND(B2>=450,C2>=90,D2=”Yes”),”Eligible”,”Not Eligible”)***Enter**. The result is “**Eligible**”, as shown below.

**Step 2:**Drag the formula from cell**E2**to**E11**using the fill handle.

[**Output Observation:** Let us take the cell **E11** expression to check how the formula works.

The **AND()** checks if **Jane Francis’s **aggregate,** 458**, is greater or equal to **450**, her attendance, **101 days**, is greater or equal to **90 days**, and she has submitted the project.

And as all the criteria hold, the **AND() **output is **TRUE**, implying that the **IF **condition is **TRUE**. So, the **IF()** returns the **TRUE** value, **Eligible**, as **Jane Francis’s **scholarship edibility.]

#### Example #3

We shall see an example of **nested IF AND**

**in Excel**.

The below table contains a list of employees, their designations, and utilization data.

The requirement is to update the performance rating for each employee in column D based on their designation and utilization. The conditions are:

- Performance Rating will be “
**Exceeds Expectations**” for Designation:**Senior Engineer**, Utilization:**>=95%**. - Performance Rating will be “
**Meets Expectations**” for Designation:**Senior Engineer**, Utilization:**>=80%**and**<95%**. - Performance Rating will be “
**Under Performer**” for Designation:**Senior Engineer**, Utilization:**<80%**.

The steps to apply the **nested IF AND in Excel** are,

**Step 1:**Select the target cell**D2**, enter the below formula, and press**Enter**.

*=IF(AND(B2=”Senior Engineer”,C2>=95%),”Exceeds Expectations”,IF(AND(B2=”Senior Engineer”,C2>=80%,C2<95%),”Meets Expectations”,IF(AND(B2=”Senior Engineer”,C2<80%),”Under Performer”,”NA”)))*

**Step 2:**Using the fill handle, drag the formula to cells**D3:D11**.

[**Output Observation:** Let us see the cell **D11** expression to know how the formula executes.

First, the **AND()** in the outer **IF()** checks if **Tracy Bridges **is a **Senior Engineer** and whether her utilization isgreater or equal to **95%**. But as the utilization percentage condition does not hold, the **AND() **output is **FALSE**, implying the outer** IF** condition does not hold.

So, the first inner **IF()**, the outer **IF() FALSE** value, executes. The **AND()** checks if the employee is a **Senior Engineer** and their utilization is between **80%** to **95%**. In this case, the **AND()** returns **TRUE** as both conditions hold. So, the **IF() **returns the **TRUE **value, **Meets Expectations**, as the performance rating of **Tracy Bridges**.]

### Important Things To Note

- In the
, supplying the**IF AND**in Excel**AND()**as theargument and the*logical_test*argument to the*value_if_true***IF excel function**is mandatory. - The Excel
**IF AND**formula is case-insensitive. - We can evaluate 255 conditions using the
**AND()**in the**IF AND**formula. - Suppose the
**AND()**in the**IF AND**function cannot return a logical value. Then the formula output will be the**#VALUE!**error.

### Frequently Asked Questions (FAQs)

**1. Is there an**

**IF AND**function in Excel?There is no inbuilt **If And function in Excel**. However, we can apply the below **IF AND** formula, which works using two logical functions, **IF** and **AND**.

The condition arguments inside the **AND()** (the **IF** condition) are the multiple criteria that the function must check. The two arguments in the **IF()** are the **TRUE** and **FALSE** values that the function returns based on the **AND()** output.

**2. When to use the**

**IF AND**function in Excel?For example, the function is useful when deciding whether a venture is pursuable based on multiple criteria such as its yearly turnover, profit margin, and scope of expansion.

**3. Can you use the IF AND formula with the OR function in Excel?**

We can use the** IF AND** formula with the **OR** function in Excel.

Let us see the steps with an example.

The table below lists laptop models, their prices, and their colors. We must update the availability status for each laptop in column D, but the conditions are that the status should show as “**Available**” only if the laptop price is equal to or greater than **$500**. And the laptop’s color is **Black**, **Platinum Blue**, or **White**.

The steps to apply the **IF AND** formula with the **OR** function are,**• Step 1:** Select the target cell **D2**, enter the below formula, and press **Enter**.*=IF(AND(B2>=500,OR(C2=”Black”,C2=”Platinum Grey”,C2=”White”)),”Available”,”Not Available”)***• Step 2:** Drag the formula from cell **D2 **to** D11** using the fill handle.

[**Output Observation:** Let us see the cell **D11** expression to understand how the formula works.

First, the **OR()** checks if the cell **C11** value is any of the specified colors. And as the second criterion in the **OR()** holds, the **OR()** returns **TRUE**. Next, the **AND()** checks if the laptop price equals or exceeds **$500**. But as the given rate is **$450**, the condition does not hold.

So, as not all the conditions in the **AND() **hold, the **AND()** output is **FALSE**. Thus, the **IF **condition does not hold, leading to the **IF() **returning the **FALSE** value, “**Not Available**”, as the **Laptop Model_10** availability status.]

### Download Template

This article must help understand the **If And in Excel**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to IF AND in Excel. Here we use the combination of IF AND functions to check multiple criteria along with examples & downloadable excel template. You can learn more from the following articles –

## Leave a Reply