Mastering IF Statements, AND & OR in Excel: Comprehensive Guide

IF statements in Excel return different results depending on whether a condition is true, and combining them with AND and OR lets you test several conditions at once. They are among the most powerful tools in Excel, and together they open up a world of possibilities for making complex decisions in your spreadsheets.

In this guide, we explore how to use IF on its own, IF with AND, IF with OR, and nested combinations of all three, with every example worked on one small table so you can check the results. It also covers IFS and NOT, common mistakes, and a free tester that shows exactly how each formula evaluates row by row.

The Sample Data Used in This Guide

Every example below uses this list of six students, with exam scores in column B, attendance in column C, and assignment status in column D. The formulas go in column E, starting in row 2.

1. Understanding the Basics: The Excel IF Function

Before diving into AND and OR, here is a quick refresher on the IF function, as described in Microsoft's IF function reference.

=IF(logical_test,value_if_true,value_if_false)

Example: =IF(B2>=70,"Pass","Fail") checks whether the exam score in B2 is 70 or higher. If it is, the formula returns Pass; otherwise it returns Fail. For Ana, with 82, the result is Pass. For Ben, with 65, it is Fail.

Comparison operators do the testing: = equals, <> not equal, > greater than, < less than, >= at least, and <= at most. Text comparisons go in quotes, such as D2="Complete", and they are not case-sensitive.

2. Using IF With AND: Every Condition Must Be True

The AND function tests several conditions at once and returns TRUE only if all of them are TRUE.

=AND(condition1,condition2,...)

When you put AND inside IF, you can make a decision based on several criteria that all need to be met.

Example: =IF(AND(B2>=70,C2>=80),"Pass","Fail") returns Pass only when the exam score is at least 70 and attendance is at least 80%. Ana (82 and 90%) passes. Cara, with a strong exam but 70% attendance, fails, because one condition is not met.

You can include as many conditions as you need in AND, up to 255, but every one must be true for IF to return the value_if_true.

3. Using IF With OR: At Least One Condition Must Be True

The OR function also tests several conditions, but it returns TRUE if at least one of them is TRUE.

=OR(condition1,condition2,...)

Combined with IF, the formula returns the value_if_true when any of the conditions is met.

Example: =IF(OR(B2>=70,C2>=80),"Pass","Fail") returns Pass if the exam score is at least 70 or attendance is at least 80%. Ben, with a 65 exam but 95% attendance, now passes. Only Fay, who misses both, fails.

Use OR when meeting any one criterion is enough to pass the test.

4. Advanced Example: Nesting IF With AND and OR

You can build more complex logic by putting OR inside AND, or the other way around. Suppose a student must score at least 70 on the exam, and must either have at least 80% attendance or have completed all assignments.

=IF(AND(B2>=70,OR(C2>=80,D2="Complete")),"Pass","Fail")

Here B2 is the exam score, C2 is attendance, and D2 is the assignment status. The formula first checks the exam, which must be true, then checks whether at least one of the other two conditions holds. Cara passes on her completed assignments despite 70% attendance. Dev fails despite an 88 exam, because his attendance is 60% and his assignments are incomplete.

The table below compares all four patterns on the same six students, so you can see how the logic changes the result.

5. Other Useful Logical Functions

Nested IF for more than two outcomes. To return a grade instead of Pass or Fail, nest IFs from the highest threshold down: =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F"))).

IFS for cleaner multiple outcomes. In Microsoft 365 and newer versions of Excel, IFS does the same without nesting: =IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F"). The final TRUE acts as the catch-all.

NOT to reverse a test. =IF(NOT(D2="Complete"),"Follow up","") flags every student whose assignments are not complete.

Free IF, AND, and OR Tester

Use the tester below to see how each pattern behaves. Choose IF, IF with AND, IF with OR, or the nested pattern, set the minimum exam score and attendance, and type the results to show. The tester writes the formula and evaluates it for every student, with check marks showing which conditions each row passed.

Change any score or assignment status to see the result update. Copy to Excel pastes the data with the live formula in every row and a COUNTIF that totals the passing results, or print the tester as a reference.

6. Common Pitfalls and Best Practices

Watch your parentheses. When you combine functions, it is easy to misplace a parenthesis. Excel colors each matching pair as you type, so check that every function closes where you expect.

Quote your text. Text results and text comparisons need quotation marks. =IF(B2>=70,Pass,Fail) returns a #NAME? error; =IF(B2>=70,"Pass","Fail") works.

Avoid overly complex formulas. Excel can handle deep logic, but a formula nobody can read is a formula nobody can fix. Break complex logic into helper columns, or switch to IFS.

Use descriptive names. Naming a cell or range, such as PassMark for the 70 threshold, makes formulas easier to read and lets you change the rule in one place.

Debug with Evaluate Formula. On the Formulas tab, Evaluate Formula steps through a complex formula one part at a time, showing exactly which condition returned TRUE or FALSE.

Final Thoughts

Mastering IF statements with AND and OR gives your spreadsheets a new level of flexibility and precision. Whether you manage projects, analyze sales, or make complex decisions, these functions let you build logic that fits your exact needs. Practice combining them in your own spreadsheets, and soon you will handle even complex scenarios with ease.

When those rules start running your team's approvals, schedules, and follow-ups, Updoot is business management software that automates them for $5 per user per month. To build your Excel skills further, try the free Excel courses.

Sources

IF function (Microsoft Support)

AND function (Microsoft Support)

OR function (Microsoft Support)