Nested IF and AND Functions in Excel (4 Suitable Examples)

While working with Microsoft Excel, sometimes we need to nest formulas so that we can get our desired output with less effort or without effort. Nesting formulas in Excel is an easy task. This is a time-saving task also. Today, in this article, we’ll learn two quick and suitable ways to use nested IF and AND function in Excel effectively with appropriate illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


4 Suitable Examples to Use Nested IF and AND Functions in Excel

Let’s get introduced to our dataset first. I have the Name of the students, CGPA, and the number of credits earned by the students on my sheet and I’ll use the nested IF and AND functions in Excel with multiple conditions using this dataset.

nested IF and AND function in excel


1. Apply AND Function Nested in IF in Excel

We will nest the AND function inside the IF function. This is an easy and time-saving task also. From our dataset, we will identify if a student Pass or Fail based on his/her securing marks in Physics. To do that, we will apply AND and IF functions in Excel. Let’s follow the instructions below to learn!

Step 1:

  • First of all, select cell D5.
  • After selecting cell D5, type the below IF and AND functions in that cell. The functions are,
=IF(AND(C5>50),"Pass","Fail")

Formula Breakdown:

  • If the value of cell C5 is greater than 50, the AND function will return TRUE otherwise FALSE.
  • If the securing mark by John Wilkins is greater than 50 then the IF function will return Pass otherwise it returns Fail.
  • Hence, simply press Enter on your keyboard. As a result, you will get the output of the AND function that is nested in the IF The return is “Pass”.

.AND Function Nested in IF Function

Step 2:

  • Hence, autoFill the AND function that is nested in the IF function to the rest of the cells in column D.

Read More: How to Use IF with AND Function in Excel (Formula + VBA)


2. Use IF Function Nested in AND Function in Excel

In this method, we will nest the IF function inside the AND function. This is an easy and time-saving task also. From our dataset, if the securing mark in Chemistry by John Wilkins is greater than 50, the IF function will return True. The AND function will return TRUE otherwise FALSE based on the output of the IF function. To do that, we will apply AND and IF functions in Excel. Let’s follow the instructions below to learn!

Step 1:

  • First of all, select cell D5.
  • After selecting cell D5, type the below IF and AND functions in that cell. The functions are,
=AND(IF(C5>50,"True","False"))

Formula Breakdown:

  • If the securing mark by John Wilkins is greater than 50 then the IF function will return True otherwise it returns False.
  • If the return of the IF function is True, the AND function will return TRUE otherwise FALSE.
  • Hence, simply press Enter on your keyboard. As a result, you will get the output of the AND function that is nested in the IF The return is “TRUE”.

IF Function Nested in AND Function

Step 2:

  • Hence, autoFill the AND function that is nested in the IF function to the rest of the cells in column D.

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


3. Nested Multiple IF and AND Functions in Excel

Multiple IF and AND functions can also be used to create a nested formula. It will be clearer if we can concentrate on the following descriptions. Here, we are going to use a Dataset to evaluate a student’s performance with his obtained marks in the examination.

Steps:

  • First of all, type the below multiple IF and AND functions in cell D5. The functions are,
=IF(AND(C5>=80),"Excellent",IF(AND(C5>=40),"Average","Poor"))

Formula Breakdown:

  • The AND function verifies whether the value in cell C5 meets the conditions inside the parenthesis. If the value in cell C5 is greater or equal to 80, it will show the output Excellent. Again, If the value in cell C5 is greater or equal to 40, it will show the output Average. Otherwise, it will show Poor.
  • Hence, simply press Enter on your keyboard. As a result, you will get the output of the multiple IF and AND The return is “Pass”.

Nested Multiple IF and AND Functions

  • Hence, autoFill the nested multiple IF and AND functions to the rest of the cells in column D.

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


4. Nested IF and AND Functions with OR Function

Suppose you want to allocate some number of students in the thesis/project program. The conditions are,

Condition 1: The student has to obtain a CGPA of more than 2.50 (must be fulfilled)

And Condition 2: He has to earn a total number of credits more than or equal to 110,

Or Condition 3: He has to complete at least 1 elective course

Here, A student must fulfill the 1st condition. After fulfilling the 1st condition, either the 2nd or 3rd condition needs to be fulfilled to be eligible to take the thesis/project.

Nested IF and AND Functions with OR Function

Now. write the below formula in cell F5 and copy this formulated cell to the rest of column F where you want to find your result.

=IF(AND(C5>=3.2,OR(D5>=110,E5>=1)), "Yes","No")

Formula Breakdown:

  • If all conditions are matched then AND Function returns TRUE otherwise FALSE.
  • If any condition is matched then OR Function returns TRUE otherwise FALSE.
  • AND(C5>=3.2,OR(D5>=110,E5>=1)) satisfied by the IF function then it returns YES otherwise NO.

By doing this you will get the desired result that you were looking for. The result is shown below.

Read More: How to Use IFS and AND Functions Together in Excel (3 Examples)


Things to Remember

👉 #N/A! error arises when the formula or a function in the formula fails to find the referenced data.

👉 #DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.


Conclusion

I hope all of the suitable methods mentioned above to nest the IF and AND functions will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo