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

Get FREE Advanced Excel Exercises with Solutions!

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 functions in Excel effectively with appropriate illustrations.


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

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


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


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.


Download Practice Workbook

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


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo