How to use the IF and AND function in Excel (Examples)

The IF function and the AND function belong to the logical functions’ library of Microsoft Excel.

Both these functions are superheroes in their domain – but when put together, the IF AND function makes an unstoppable superhero duo.

In the tutorial below, I will teach you how this function works. With two conditions, multiple conditions, and also when nested together.

So grab your sample practice workbook for this tutorial here and tag along with me 🏍

Table of Contents

IF AND function in Excel

The IF AND function in Microsoft Excel is the combination of two top-notch logical functions of Excel. The IF function and the AND function.

The IF function works to run a given condition and return a certain value if the condition meets or another value if the condition fails.

Like you test a range of values to see if they are greater than 400 using the simple IF function.

The AND function on the other hand is a simple logical function that can test multiple conditions co-exist and return a Boolean value (true or false) ✔

The AND function checks each value against both conditions (greater than 200 but lesser than 600) and returns TRUE only if both conditions are true.

So why do we even need to bring both these functions together?

That’s because the IF function can only test a single condition by itself. It doesn’t allow testing multiple conditions to return a specified value.

Similarly, the AND function can test multiple conditions in one go but is only capable of returning Boolean values – not the values you specify.

How do you use the IF AND function in Excel

The IF AND function makes a deadly combo in Excel and I am going to illustrate that here through an example.

Here I have a list of students and their grades in academics, as well as in extra-curricular activities 📋

Per the rules, only those students pass who:

• Scored an A+ in academics
• And A+ in extra-curriculars

Low-key, I am just so grateful to have completed my school 🙏

To have the pass/fail results populated in Column D, let’s write the IF AND function as follows:

Step 1) Begin by writing the AND function.

Pro Tip!

The syntax of this formula says that:

• First Condition: Cell B2 should be equal to the text value “A+” (note the quotation marks I have used to specify a text value to Excel)
• Second Condition: Cell value for C2 should be equal to the text value “A+”

AND function checks for the first condition and the second condition, both to be true. Only then if returns TRUE. If either of the conditions fails, it returns FALSE.

I dragged and dropped it to the whole list to learn only Benjamin and Elijah yielded TRUE.

Step 2) Nest the above function into the IF formula as below:

The first argument of the IF function is the logical test. We have set the AND function in its place. The IF function will now run the AND function as the logical test.

Step 3) Specify the value_if_true (if both the conditions are true) as “Pass”.

Step 4) Specify the value_if_false (if any of the conditions or both the conditions are false) as “Fail”.

Step 5) All set – hit enter for results.

Step 6) Drag and drop the above formula to the entire result.

The IF AND function evaluates both the given conditions and returns the pass/fail result for each student.

Although the results of students are not very encouraging (only 2 passed), the results of the IF AND function are amazing!

IF AND function with multiple conditions

The above section was about running kind of a basic IF AND function. As we move forward, why run only two conditions? Why not more?

To your surprise, the IF AND function is very well capable of running 3, 4, 5 and so on up to 255 conditions.

Want to see it happen? Let’s go.

With the same example as above, passing now gets even more difficult 🤔

For any student to pass now, he must:

• Score A+ in academics
• Score A+ in extra-curricular
• Have 90% or higher Attendance; and
• Have his behavior graded as Excellent

Time to see if anyone passes the above criteria.

Step 1) Write the AND function like below.

This time the AND function translates to:

• First Condition: Cell B2 should be equal to the text value “A+”
• Second Condition: Cell C2 should be equal to the text value “A+”
• Third Condition: Cell D2 should be equal to or greater than 90%
• Fourth Condition: Cell E2 should be equal to the text value “A+”

Step 2) Hit enter to see what we get.

Step 3) Nest the above function into the IF function as below:

So, we have our logical test ready.

Step 4) Add the next two arguments for value_if_true and value_if_false as “Pass” and “Fail”, respectively.

Step 5) Fingers crossed. It’s time we see the results in our worksheet.

One more down – with the criteria so strict and four simultaneously running conditions, only Benjamin makes it to the students who passed. Better luck next time to the rest of them!

But yes, you must not stop here – make your own set of formulas with dozens of conditions and see how powerful the IF AND statement gets.

Nested IF AND function

The IF AND combination can be tweaked in another way, too – and the results will bowl you over.

This is the situation where you want Excel to run a logical test and return results based on how the logical test performed.

However, the results are not just limited to two values (the value_if_true and value_if_false), but more.

The following example will explain to you how the nested IF AND function works.

So, here’s a restaurant with multiple branches that are assessed based on two of their KPIs i.e., Food and Ambience 🍽

The KPI sheet is simple:

• Food and Ambience both graded above 4 > Excellent
• Food and Ambience both graded above 3 > Good
• Food and Ambience both graded equal to or above 2 > Need Improvement
• Food and Ambience are both graded below 2 > Shut Down

But the point is, how do we translate the aforementioned KPIs into an Excel formula?

Here’s how:

Step 1) Begin writing from the first KPI using the IF AND function:

We’re just telling Excel to return the performance score as Excellent if both the Food and Ambience grades exceed 4.

But – in case both the conditions don’t turn true (any of the grades is equal to or less than 4), in place of the value_if_false, we will nest another IF AND function.

Step 2) As the value_if_false for this function write a new IF AND function.

With this, we are telling Excel that if the first condition isn’t met, run another IF AND function.

The nested IF AND function checks if both the Food and Ambience grades exceed 3. If they do, it returns the value_if_true i.e., Good.

Step 3) Repeating the same science as above, we will nest another IF AND function in the following formula (in place of the Value_if_false) :

Once again, the formula above says that:

If none of the branches exceed grade 4 or 3 for both KPIs, check if any branch achieves a score of 2 or above for both KPIs.

If any branch does, return the performance score “Need Improvement”.

Step 4) Next, specify the final value_if_false as “Shut Down”.

With that, Excel knows that if none of the branches exceeds grade 4 or 3 or at least meets grade 2 for both the KPIs, it needs to be “Shut Down”.

Step 5) Hit Enter to have your results populated.

Main Boulevard–I stays on top with other branches next in line 🥇

And we’re probably bidding goodbye to the branch in Orchard Street since it runs Grade 2 in food and Grade 1 in Ambience – rightly so.

That’s how the nested IF AND function works.

I get it looks clumsy (as the formula keeps getting longer and longer) but the results it produces are phenomenal.

Must-knows about using the IF AND function of Excel

By now, I assume you’re a pro at playing around with IF AND statements. Keep up with that but here are some pointers to keep in mind about the IF AND function:

• The IF AND function is not case-sensitive. So, it will not differentiate between texts that are only different in terms of case sensitivity.
• There’s a certain limit to the arguments you can give to the IF AND function. A maximum of 255 arguments and total formula length of 8192 characters is all it offers.
• To nest the IF AND function appropriately, follow a logical sequence to structure your conditions.
• As the formula continues to grow, make sure you use the right number of parentheses in it.

Conclusion

The IF function is one of the most powerful and useful functions of Excel 💪

To make the most out of the IF function, make sure you know all the ins and outs of how this function works. My step-by-step blog on how to use the IF function will help you with that.

Additionally, there are so many more functions that you will see clubbed with the IF Function in Excel like the SUMIF function, MEDIANIF function, COUNTIF function, AVERAGEIF function, and others.

Check out our articles that cover all the smart IF-related functions to become a pro at using them.