IF with AND and OR in Excel
Learn how to use IF with AND and OR in Excel. This beginner-friendly guide explains how to test multiple conditions with real examples and formulas.
If you’re new to Excel, mastering logical functions like IF, AND, and OR will open up endless possibilities for analyzing data, making decisions, and automating your spreadsheets.
Table of Contents
🔍 Introduction
Microsoft Excel is more than just a number-crunching tool — it’s a decision-making powerhouse. And at the core of that power is the IF function, especially when combined with AND and OR.
Whether you’re building a grading system, filtering data, or applying conditional formatting, understanding how to use IF with AND & OR can save hours of manual work.
🧠 What is the IF Function?
The IF function allows you to return different results depending on whether a condition is TRUE or FALSE.
Syntax:
=IF(logical_test, value_if_true, value_if_false)
✅ Example:
=IF(A1>50, “Pass”, “Fail”)
🔗 What is the AND Function?
The AND function checks if all conditions are TRUE.
Syntax:
=AND(condition1, condition2, …)
✅ Example:
=AND(A1>50, B1>50)
Returns TRUE only if both A1 and B1 are greater than 50.
🔗 What is the OR Function?
The OR function checks if any of the conditions are TRUE.
Syntax:
=OR(condition1, condition2, …)
✅ Example:
=OR(A1>50, B1>50)
Returns TRUE if either A1 or B1 is greater than 50.
🚀 How to Use IF with AND in Excel
🔧 Example:
You want to give a “Bonus” only if both sales are over 100.
=IF(AND(A2>100, B2>100), “Bonus”, “No Bonus”)
📌 Explanation:
- If both A2 and B2 are above 100, it shows “Bonus”.
- Otherwise, it returns “No Bonus”.
🚀 How to Use IF with OR in Excel
Use this when any one of the conditions should be TRUE.
🔧 Example:
You want to flag “Review” if either test score is below 40.
=IF(OR(A2<40, B2<40), “Review”, “OK”)
📌 Explanation:
- If A2 OR B2 is less than 40, it shows “Review”.
- Otherwise, it shows “OK”.
🤹♂️ Combine IF, AND & OR (Advanced Level)
Yes, you can even nest AND and OR inside a single IF function!
🧪 Example:
You want to mark as “Eligible” only if:
- Age > 18 AND
- Either Score1 > 70 OR Score2 > 70
=IF(AND(A2>18, OR(B2>70, C2>70)), “Eligible”, “Not Eligible”)
📌 This evaluates:
- Age (A2) must be above 18
- One of the two scores (B2 or C2) must be above 70
✅ Real-World Use Cases
| Situation | Formula Example |
|---|---|
| Student Grading | =IF(AND(A2>50, B2>50), "Pass", "Fail") |
| Product Discount Logic | =IF(OR(A2="Gold", A2="Platinum"), "Discount", "No Discount") |
| Employee Eligibility | =IF(AND(A2>1, OR(B2="Full-time", C2="Remote")), "Eligible", "Not") |
| Sales Commission | =IF(AND(A2>5000, B2="Target Met"), "Bonus", "No Bonus") |
🧰 Pro Tips
✅ Use Named Ranges to make your formulas easier to read.
✅ Use Data Validation to ensure logical entries.
✅ Combine with Conditional Formatting for visual clarity.
✅ Press Ctrl + ~ to show formulas on the entire sheet.
🧪 Common Errors to Avoid
❌ Don’t forget to close all parentheses )
❌ Don’t mix text and numbers in logical tests
❌ Avoid deeply nested logic – break complex formulas into steps
🏁 Final Thoughts
Combining IF with AND and OR in Excel is the key to unlocking dynamic decision-making. Whether you’re a student, office worker, or data analyst, mastering these functions can supercharge your spreadsheets.
🔁 Bookmark this guide, share with friends, and don’t forget:
The smarter your logic, the smarter your Excel!
