**Syntax of IFS and AND Functions**

The syntax for **the IFS function **is given below.

`=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2],...)`

**The IFS function **can test several arguments at a time whether it is true or not. If the argument meets the criteria, the function returns the ‘**value_if_true**’ value. Otherwise, it returns the **Value not Available Error (#N/A)**. To get rid of this problem, we state the **logical_test **that does not meet any arguments of the system as **TRUE **and then put the ‘**value_if_true**’ value for that **logical_test**.

Let’s have a simple example to illustrate this issue. Suppose you want **Apple **as an output in cell **B5 **if you type **A **in **B4**. For any other character, it will return nothing. So, the first **logical_test **will be if the cell value of **B4 **matches with **A**. We can express this **logical_test** as **B4=“A”**. The next thing will be the output (**Apple**) we want if the cell value of **B4 **matches the condition. We write this as **“Apple” **after the **logical_test**. The next **logical_test **will be if the value in **B4 **does not match with the letter **A**. Following the description above, we can express this as **TRUE **and put a space (**“ ”**) in the **value_if_true **argument.

The formula will look like this

`=IFS(B4="A","Apple",TRUE,"")`

The syntax for **the AND function** is given below.

`=AND(logical1, [logical2])`

The function checks whether all the arguments are **TRUE**, and returns **TRUE** if all arguments are **TRUE**. Otherwise, it returns **FALSE**.

**How to Use IFS and AND Functions Together in Excel: 3 Examples**

**Method 1 – Applying Excel IFS and AND Functions Together to Determine CGPA**

Suppose you are a teacher and you want to determine the **Grade Point **of the students based on their **Obtained Marks**.

**Steps:**

- Make a column to store the
**Grade Points,**copy the following formula in cell**D5,**then press**Enter:**

`=IFS(AND(C5>=80,C5<=100)=TRUE,4,AND(C5<80,C5>75)=TRUE,3.75,AND(C5>70,C5<=75)=TRUE,3.5,AND(C5>65,C5<=70)=TRUE,3.25,AND(C5>60,C5<=65)=TRUE,3,AND(C5>55,C5<=60)=TRUE,2.75,AND(C5>50,C5<=55)=TRUE,2.5,AND(C5>45,C5<=50)=TRUE,2.25,AND(C5>40,C5<=45)=TRUE,2,TRUE,0)`

**Formula Breakdown**

Although the formula looks horrible, let’s go through the arguments one by one. The formula returns the **CGPA **of the matched argument based on the **Mark **in **C5**.** **

**AND(C5>=80,C5<=100)=TRUE, 4 —->**returns the**Grade Point 4.00**if the mark is in the range**[80, 100]**.**AND(C5<80,C5>75)=TRUE, 3.75 —->**returns the**Grade Point 3.75**if the mark is in the range**(75, 80)**.**AND(C5>70,C5<=75)=TRUE, 3.5 —->**returns the**Grade Point 3.5**if the mark is in the range**(70, 75]**.**AND(C5>65,C5<=70)=TRUE, 3.25 —->**returns the**Grade Point 3.25**if the mark is in the range**(65, 70]**.**AND(C5>60,C5<=65)=TRUE, 3 —->**returns the**Grade Point 3.00**if the mark is in the range**(60, 65]**.**AND(C5>55,C5<=60)=TRUE, 2.75 —->**returns the**Grade Point 2.75**if the mark is in the range**(55, 60]**.**AND(C5>50,C5<=55)=TRUE, 2.5 —->**returns the**Grade Point 2.50**if the mark is in the range**(50, 55]**.**AND(C5>45,C5<=50)=TRUE, 2.25 —->**returns the**Grade Point 2.25**if the mark is in the range**(45, 50]**.**AND(C5>40,C5<=45)=TRUE, 2 —->**returns the**Grade Point 2.00**if the mark is in the range**(40, 45]**.**TRUE, 0 —->**returns the**Grade Point 0.00**if the mark is below**40**.

- Use the
**Fill Handle**to**AutoFill**the lower cells.

**Read More:** How to Use IF with AND Function in Excel

**Method 2 – Conditional Formatting Using Excel IFS and AND Functions Together**

Say we want the **CGPAs 4.00 **and **3.75 **to be filled with a **green **background color. We’ll use the dataset from the previous method.

**Steps:**

- Select the range
**D5:D14.** - Go to
**Home,**select**Conditional Formatting,**and choose**New Rule**.

- Select the
**Use a formula to determine which cells to format**option in the**New Formatting Rule**dialog box. - Copy the following formula in the ‘
**Format values where this formula is true**’ section:

`=IFS(AND(D5>3.75,D5<=4)=TRUE,TRUE,AND(D5>3.5,D5<=3.75)=TRUE,TRUE)`

The formula fills the cells with a color that satisfies the condition, which is the cells containing the **CGPAs 4.00 **and **3.75**.

- Click on
**Format.** - Select the fill color from the
**Fill**tab of the**Format Cells.**We chose**Green**. - You may format the
**Font**too if you want. - Click
**OK**.

- The
**New Formatting Rule**window will again pop up with a preview of how the formatted cells will look like. - Click
**OK**.

- You will see the
**Grade Points 4.00**and**3.75**filled with**Green**.

**Read More:** How to Use Conditional Formatting with AND Function in Excel

**Method 3 – Using Excel IFS and AND Functions Together to Consider Bonus for Employee**

Suppose you want to motivate your employees by giving them **Bonuses **to get their best effort. If someone works more than **47 hours **a week, you will certainly give him a **bonus**. If someone works between **44 **to **47 **hours, you will decide later whether you give him a bonus or not. The other employees won’t receive any **bonus**.

**Steps:**

- Make a column to store the
**Bonus Eligibility**of your employees. - Copy the following formula in cell
**D5**and press**Enter**.

`=IFS(AND(C5>47,C5<=53)=TRUE,"Yes",AND(C5<=47,C5>44)=TRUE,"Under Consideration",TRUE,"No")`

The formula will return the **Bonus Eligibility **for **Lula**. As they worked not more than **44 hours**, they won’t get a bonus. And so, the **Bonus Eligibility **is “**No**”.

- Use the
**Fill Handle**to**AutoFill**the lower cells.

**Read More:** How to Return TRUE or FALSE Using Excel AND Function

**Practice Section**

Here’s the dataset of this article so that you can practice these methods on your own.

**Download Practice Workbook**

## Related Articles

- How to Use AND Function with Text in Excel
- How to Use SUMIF and AND Function in Excel
- Nested IF and AND Functions in Excel

**<< Go Back to Excel AND Function | Excel Functions | Learn Excel**