Excel IF Formula With Multiple Conditions

If you have been using Excel for any amount of time, you have almost certainly used the IF formula. It is one of the most essential and widely used functions in all of Excel.

The basic IF formula is straightforward:

If this condition is true, do this. Otherwise, do that.

But real-world data is rarely that simple. Most of the time, you need to check multiple conditions at once and that is where many Excel users get stuck.

In this guide, we will break down every method for using the Excel IF formula with multiple conditions including IF AND, IF OR, nested IF, IFS, and SWITCH with clear step-by-step examples, a full comparison table, real-world use cases, and practical tips for getting it right every time.

The Basic IF Formula

Before adding multiple conditions, let us make sure the foundation is solid.

Basic IF Syntax

=IF(logical_test, value_if_true, value_if_false)

• logical_test — The condition you want to check
• value_if_true — What to return if the condition is TRUE
• value_if_false — What to return if the condition is FALSE

Basic IF Example

	A	B
1	Score	Result
2	75	=IF(A2>=60, “Pass”, “Fail”)

Result: Pass

This works perfectly when you have one condition. But what if you need to check two, three, or more conditions at the same time? That is where the real power of IF begins.

Method 1: IF with AND (All Conditions Must Be True)

The IF AND combination checks whether all conditions are true simultaneously. If every condition is met, it returns one result. If even one condition fails, it returns the other.

Syntax

=IF(AND(condition1, condition2, ...), value_if_true, value_if_false)

Example 1: Bonus Eligibility

An employee qualifies for a bonus only if they have been with the company for more than 2 years AND their performance score is above 80.

	A	B	C
1	Name	Years	Score
2	Alice	3	85
3	Bob	1	90
4	Charlie	4	72
5	Diana	2	88

=IF(AND(B2>2, C2>80), "Bonus", "No Bonus")

Results:

Name	Result
Alice	Bonus (3 years AND score 85 — both conditions met)
Bob	No Bonus (only 1 year — fails first condition)
Charlie	No Bonus (score 72 — fails second condition)
Diana	No Bonus (exactly 2 years — fails first condition, needs MORE than 2)

Example 2: Three Conditions with AND

A loan application is approved only if the applicant has a credit score above 700, an income above $50,000, AND no existing defaults.

=IF(AND(B2>700, C2>50000, D2="No"), "Approved", "Rejected")

All three conditions must be TRUE simultaneously for the loan to be approved.

When to Use IF AND

• When all conditions must be satisfied at the same time
• Bonus eligibility, loan approval, access control
• Any situation where multiple requirements must all be met together

Method 2: IF with OR (At Least One Condition Must Be True)

The IF OR combination returns the true result if at least one of the conditions is met. Only if every single condition fails does it return the false result.

Syntax

=IF(OR(condition1, condition2, ...), value_if_true, value_if_false)

Example 1: Discount Eligibility

A customer qualifies for a discount if they are a member OR if their order total exceeds $500.

	A	B	C
1	Customer	Member	Order Total
2	Alice	Yes	250
3	Bob	No	620
4	Charlie	No	150
5	Diana	Yes	800

=IF(OR(B2="Yes", C2>500), "Discount", "No Discount")

Results:

Customer	Result
Alice	Discount (is a member)
Bob	Discount (order over $500)
Charlie	No Discount (neither condition met)
Diana	Discount (both conditions met — OR only needs one)

Example 2: Flagging Data Quality Issues

Flag a record if the name is blank OR the salary is zero OR the department is missing.

=IF(OR(A2="", B2=0, C2=""), "Review Needed", "OK")

This is extremely useful in data cleaning workflows where any single issue should trigger a review.

When to Use IF OR

• When any one condition being true is enough
• Discount eligibility, alert systems, data validation flags
• Any situation where multiple possible triggers should produce the same result

Method 3: Combining IF, AND, and OR Together

Real-world scenarios often require combining AND and OR logic in the same formula. This is where Excel’s logical functions become very powerful.

Example: Complex Promotion Criteria

An employee is eligible for promotion if:

• They have been with the company for more than 3 years AND their score is above 85
• OR they have been with the company for more than 5 years (regardless of score)

=IF(OR(AND(B2>3, C2>85), B2>5), "Eligible", "Not Eligible")

How It Works

1. AND(B2>3, C2>85) — Checks if years > 3 AND score > 85
2. B2>5 — Checks if years > 5 alone
3. OR(...) — Returns TRUE if either of those conditions is met
4. The outer IF returns “Eligible” or “Not Eligible” based on the OR result

Example: Loan Approval With Complex Logic

Approve a loan if the applicant has a high credit score (above 750) AND low debt ratio (below 30%), OR if they have a guarantor AND income above $80,000.

=IF(OR(AND(B2>750, C2<30), AND(D2="Yes", E2>80000)), "Approved", "Rejected")

This kind of layered logic would be extremely difficult to handle without combining AND and OR inside a single IF formula.

Method 4: Nested IF (Multiple Outcomes)

The IF AND and IF OR methods return one of two outcomes — true or false. But what if you need three, four, or more different outcomes? That is where nested IF comes in.

A nested IF puts one IF formula inside another, creating a chain of conditions that leads to multiple possible results.

Syntax

=IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, default_result)))

Example 1: Grade Classification

Assign a letter grade based on a numeric score.

Score	Grade
90 and above	A
80 to 89	B
70 to 79	C
60 to 69	D
Below 60	F

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))

How It Works Step by Step:

1. Is score >= 90? If YES → return “A”. If NO → go to next IF
2. Is score >= 80? If YES → return “B”. If NO → go to next IF
3. Is score >= 70? If YES → return “C”. If NO → go to next IF
4. Is score >= 60? If YES → return “D”. If NO → return “F”

Example 2: Sales Commission Tiers

Calculate commission rate based on total sales amount.

Sales Amount	Commission Rate
Above $100,000	15%
$75,000 to $100,000	12%
$50,000 to $74,999	10%
Below $50,000	7%

=IF(B2>100000, 15%, IF(B2>=75000, 12%, IF(B2>=50000, 10%, 7%)))

Example 3: Combining Nested IF with AND

Classify employees by seniority level based on both years of experience and job title.

=IF(AND(B2>10, C2="Manager"), "Senior Manager",
    IF(AND(B2>5, C2="Manager"), "Manager",
        IF(B2>5, "Senior Staff", "Junior Staff")))

Limitations of Nested IF

• Excel allows up to 64 levels of nesting but readability breaks down long before that
• More than 3 to 4 levels becomes very hard to read, write, and debug
• For many outcomes, use IFS or SWITCH instead (covered below)

Method 5: IFS Function (Cleaner Alternative to Nested IF)

The IFS function was introduced in Excel 2019 and Excel 365. It evaluates multiple conditions in order and returns the result for the first condition that is TRUE without the messy nesting of multiple IF statements.

Syntax

=IFS(condition1, result1, condition2, result2, condition3, result3, ...)

Example: Grade Classification with IFS

The same grade classification from Method 4 but much cleaner with IFS.

=IFS(A2>=90, "A", A2>=80, "B", A2>=70, "C", A2>=60, "D", A2<60, "F")

Compare this to the nested IF version:

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))

The IFS version is significantly easier to read, write, and maintain especially as the number of conditions grows.

Adding a Default Value to IFS

IFS does not have a built-in “else” but you can add a catch-all condition using TRUE as the final condition.

=IFS(A2>=90, "A", A2>=80, "B", A2>=70, "C", A2>=60, "D", TRUE, "F")

TRUE always evaluates to true, so it acts as the default fallback when no earlier condition is met.

Example: Performance Rating with IFS

=IFS(
    C2>=95, "Outstanding",
    C2>=85, "Exceeds Expectations",
    C2>=75, "Meets Expectations",
    C2>=60, "Needs Improvement",
    TRUE, "Unsatisfactory"
)

When to Use IFS

• You have three or more conditions leading to different outcomes
• You want cleaner, more readable formulas than nested IF
• You are using Excel 2019 or Excel 365

Method 6: SWITCH Function (Value-Based Matching)

The SWITCH function is perfect when you want to match a cell to a specific list of values rather than evaluating conditions. Think of it as a cleaner alternative to nested IF when you are checking equality.

Syntax

=SWITCH(expression, value1, result1, value2, result2, ..., default)

Example: Department Code Lookup

Convert department codes to full department names.

=SWITCH(A2,
    "ENG", "Engineering",
    "MKT", "Marketing",
    "HR", "Human Resources",
    "FIN", "Finance",
    "Unknown Department")

How It Works:

• If A2 = “ENG” → return “Engineering”
• If A2 = “MKT” → return “Marketing”
• If A2 = “HR” → return “Human Resources”
• If A2 = “FIN” → return “Finance”
• If A2 matches none of the above → return “Unknown Department”

Example: Day of Week Label

=SWITCH(WEEKDAY(A2),
    1, "Sunday",
    2, "Monday",
    3, "Tuesday",
    4, "Wednesday",
    5, "Thursday",
    6, "Friday",
    7, "Saturday",
    "Invalid Date")

When to Use SWITCH

• You are matching a value against a fixed list of specific values
• Your conditions are all equality checks and not ranges or comparisons
• You want cleaner syntax than a long nested IF for value matching

Method	Best For	Number of Outcomes	Excel Version	Readability
IF AND	All conditions must be true	2	All versions	Easy
IF OR	At least one condition must be true	2	All versions	Easy
IF AND + OR combined	Complex mixed logic	2	All versions	Moderate
Nested IF	Multiple different outcomes	2 to 64	All versions	Gets messy
IFS	Multiple conditions, multiple outcomes	Unlimited	Excel 2019+	Clean
SWITCH	Matching specific values	Unlimited	Excel 2019+	Very clean

Real-World Use Cases

HR and Payroll

Determine salary bands, bonus eligibility, or leave entitlement based on years of service, department, and performance rating using IF AND with multiple conditions.

=IF(AND(B2>5, C2="Full-Time", D2="Active"), "Senior Band", "Standard Band")

Sales and Commission Tracking

Calculate tiered commission rates, qualify leads based on deal size and stage, or flag accounts that need follow-up based on multiple criteria.

=IFS(B2>100000, "Tier 1 - 15%", B2>50000, "Tier 2 - 10%", B2>20000, "Tier 3 - 7%", TRUE, "Tier 4 - 5%")

Student Grade Management

Assign letter grades, determine pass or fail status based on multiple assessments, or flag students who need academic support.

=IF(AND(B2>=60, C2>=60, D2>=60), "Pass", "Fail — Review Required")

Inventory Management

Flag items that need restocking based on current stock level AND reorder threshold AND supplier lead time.

=IF(AND(B2<C2, D2>7), "Urgent Reorder", IF(B2<C2, "Reorder", "OK"))

Financial Reporting

Classify expenses by category and amount, flag budget overruns, or determine approval levels based on multiple financial criteria.

=IF(OR(B2>10000, C2="Capital Expense"), "Director Approval Required", "Manager Approval")

Data Validation and Quality Control

Flag records that have missing data, invalid values, or inconsistencies across multiple columns.

=IF(OR(A2="", B2=0, C2<0, D2=""), "Data Error — Review", "Valid")

Advantages and Disadvantages

IF AND

Advantages: Simple logic, easy to understand, works in all Excel versions Disadvantages: Only two outcomes, becomes complex when combined with OR

IF OR

Advantages: Flexible trigger logic, great for flag and alert systems Disadvantages: Only two outcomes, easy to confuse with AND logic

Nested IF

Advantages: Works in all Excel versions, handles multiple outcomes Disadvantages: Becomes unreadable quickly, hard to debug, error-prone with many levels

IFS

Advantages: Clean syntax, easy to read with many conditions, no nesting required Disadvantages: Only available in Excel 2019 and Excel 365, no built-in else (need TRUE workaround)

SWITCH

Advantages: Very clean for value matching, easy to read and extend Disadvantages: Only works for exact value matching not range comparisons or complex conditions

Common Mistakes to Avoid

• Confusing AND and OR logic — AND requires ALL conditions to be true. OR requires only ONE. Getting these mixed up is the most common mistake and produces silently wrong results
• Putting conditions in the wrong order in nested IF — Always start with the most restrictive condition. For grades, check >=90 before >=80, not the other way around
• Forgetting the FALSE result in IF — If you omit the value_if_false argument, Excel returns FALSE as text, which is rarely what you want
• Using text without quotes — Text values in IF formulas must always be wrapped in double quotes: “Pass” not Pass
• Not using absolute references when copying formulas — If your conditions reference lookup tables or fixed values, use $ to lock references so they do not shift when copying down
• Too many nested IF levels — More than three or four levels of nesting is a strong signal to switch to IFS or restructure your approach
• Using IFS or SWITCH in older Excel versions — IFS and SWITCH are not available in Excel 2016 or earlier. Always check your Excel version before using these functions
• Forgetting the TRUE catch-all in IFS — Without a default condition, IFS throws an error if no condition is matched. Always add TRUE as the final condition

Quick Reference Cheat Sheet

Goal	Formula Pattern
Both conditions true	=IF(AND(cond1, cond2), "Yes", "No")
Either condition true	=IF(OR(cond1, cond2), "Yes", "No")
Mixed AND OR logic	=IF(OR(AND(cond1, cond2), cond3), "Yes", "No")
Multiple outcomes	=IFS(cond1, result1, cond2, result2, TRUE, default)
Value matching	=SWITCH(cell, val1, res1, val2, res2, default)
Grade from score	=IFS(A2>=90,"A", A2>=80,"B", A2>=70,"C", TRUE,"F")
Nested IF (3 levels)	=IF(c1, r1, IF(c2, r2, IF(c3, r3, default)))

The Excel IF formula with multiple conditions is one of the most powerful tools in your spreadsheet toolkit. Once you understand how to combine IF with AND, OR, and nesting and when to upgrade to IFS or SWITCH — you can handle virtually any conditional logic that real-world data throws at you.

Here is a simple decision framework to choose the right approach:

• Need to check if all conditions are met → Use IF AND
• Need to check if any condition is met → Use IF OR
• Need complex mixed logic → Combine AND and OR inside IF
• Need multiple different outcomes → Use nested IF or IFS
• Need to match specific values → Use SWITCH
• Using Excel 2019 or 365 with many conditions → Always prefer IFS over nested IF

Start with the simple IF AND and IF OR examples, practice on your own data, and gradually work your way up to combining them and using IFS for multi-outcome scenarios.

Once these formulas feel natural, you will find yourself solving complex business logic problems in Excel that used to seem impossibly difficult.

FAQs