The article will show you how to use **IFS **and **AND** function together in Excel. The** IFS **function is an alternative to using the nested **IF **functions. The advantage of using the** IFS** function is that we don’t need to use the function repeatedly in the formula. The** AND **function will do the logical test here. Please stay tuned to this article to see the application of the combined **IFS **and **AND **functions.

**Table of Contents**Expand

**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**

I’ve used two types of datasets in this article to show how to use the **IFS **and **AND **functions together in Excel. Please go through the following sections to understand the process.

**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**. The following procedure will guide you on this matter.

**Steps:**

- First, make a column to store the
**Grade Points**and type the following formula in cell**D5**and press the**ENTER**It will return the corresponding**Grade Point**for the obtained marks of**Lula**.

`=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, you can find it easy if you go through the arguments one by one. The arguments are given below. 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**.

- After that, use the
**Fill Handle**to**AutoFill**the lower cells.

Thus you can determine the **CGPAs **of the students using the **IFS **and **AND **functions together.

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

**2. Conditional Formatting Using Excel IFS and AND Functions Together**

We can also do **Conditional Formatting **using **IFS **and **AND **functions. Say we want the **CGPAs 4.00 **and **3.75 **to be filled with **Green **background color. Let’s go through the discussion below to see how we can achieve this. We will use the dataset that we got in **Method 1**.

**Steps:**

- First, select the range
**D5:D14**and then go to**Home**>>**Conditional Formatting**>>**New Rule**.

- Then, select the
**Use a formula to determine which cells to format**option in the**New Formatting Rule**dialog box. - Next, type the following formula in the ‘
**Format values where this formula is true**’ section and click on**Format**.

`=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**.

- Thereafter, select the fill color from the
**Fill**tab of the**Format Cells**Here we chose**Green**. - In addition, you may format the
**Font**too if you want. - Later, click
**OK**.

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

- After this operation, you will see the
**Grade Points 4.00**and**75**filled with**Green**color.

Thus you can perform **Conditional Formatting **using the **IFS **and **AND **functions together in Excel.

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

**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. So you made a criteria: 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**. Let’s go through the description below to see how we can do this.

**Steps:**

- First, make a column to store the
**Bonus Eligibility**of your employees and type the following formula in cell**D5**and press**ENTER**. The formula will show you the**Bonus Eligibility**of**Lula**.

`=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 he worked not more than **44 hours**, he won’t get a bonus. And so, his **Bonus Eligibility **is “**No**”.

- After that, use the
**Fill Handle**to**AutoFill**the lower cells.

Thus you can use the **IFS **and **AND **functions for official purposes too.

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

**Practice Section**

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.

**Download Practice Workbook**

**Conclusion**

In the end, you will learn the basic idea of how to use **IFS **and **AND **function together in Excel after reading this article. If you have any better suggestions, questions, or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.

## 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**