CONTENTS

Excel IF Function Multiple Conditions: Complete Guide to AND/OR, IFS, and FILTER

If you want to handle multiple conditions with the IF function cleanly, follow this guide. It starts with a Quick Fix you can apply in 3 minutes, then covers logic principles, practical table examples, Microsoft 365-only optimizations (LET/FILTER), precautions, and Troubleshooting.

Quick Fix: Multiple Conditions in 3 Minutes

Use AND when all conditions must be met.

=IF(AND(A2="Sales", B2>=80, C2="New"), "Bonus", "Not Eligible")

Use OR when meeting at least one condition is enough to pass.

=IF(OR(A2="VIP", B2>=100, C2="Strategic"), "Priority", "Standard")

Use IFS when conditions are tiered (such as score to grade); it is the easiest to read (recommended for Microsoft 365).
```
=IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", TRUE, "D")
```
For policies where priorities may change often, use LET to improve readability and maintainability (Microsoft 365).
```
=LET(Score,B2, New,(C2="New"), IF(AND(Score>=85, New),"Bonus","Standard"))
```

Tip: To check intermediate formula results, use Formulas tab > Formula Auditing > Evaluate Formula, or select part of a formula and press F9.

Why Is It Confusing? Logic Principles and Priorities

AND / OR Truth Table (Simple)

AND: Returns TRUE only when all conditions are TRUE.
OR: Returns TRUE when at least one condition is TRUE.

Excel often treats TRUE=1 and FALSE=0. When numbers, text, and blanks are mixed, you may get unexpected results, so specify comparison operators (=, >, <=) and handle spaces and errors with IFERROR and ISBLANK.

Nested IF vs. IFS vs. SWITCH

Nested IF: Versatile, but readability decreases and maintenance costs increase as formulas get longer.
IFS (Microsoft 365): Makes multiple conditions easy to read from top to bottom.
SWITCH (Microsoft 365): Clean for branching multiple cases based on one “key value.”

Practical Example 1: Bonus Eligibility (AND + OR Combination)

Scenario: In the table below, pay a “Bonus” if an employee is in the Sales department, has a score ≥ 85, and is either New or Strategic.

Department (A)	Score (B)	Status (C)
Sales	90	New
Sales	88	Standard
Sales	82	Strategic
Support	95	New
Sales	87	Strategic

Enter the following formula in D2 and copy it down:

=IF( AND(A2="Sales", B2>=85, OR(C2="New", C2="Strategic")), "Bonus", "Not Eligible")

Expected Results

Row 1: Bonus (Sales ∧ 90≥85 ∧ (New∨Strategic))
Row 2: Not Eligible (status is Standard)
Row 3: Not Eligible (score of 82 is below the threshold)
Row 4: Not Eligible (department is Support)
Row 5: Bonus (Sales ∧ 87≥85 ∧ Strategic)

Cleaner Formulas with Structured References (Tables)

Convert the range to a table: select the data range → Ctrl+T → select “My table has headers.”

=IF( AND([@Department]="Sales", [@Score]>=85, OR([@Status]="New", [@Status]="Strategic") ), "Bonus","Not Eligible")

Improve Maintainability with LET (Microsoft 365)

=LET(
  Department,[@Department],
  Score,[@Score],
  Status,[@Status],
  IF(AND(Department="Sales", Score>=85, OR(Status="New", Status="Strategic")), "Bonus","Not Eligible")
)

Practical Example 2: Score to Grade (IFS vs. Nested IF vs. SWITCH)

Policy: 90 or higher = A, 80–89 = B, 70–79 = C, and all other scores = D.

IFS (Most Readable, Microsoft 365)

=IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", TRUE,"D")

Nested IF (Versatile; Improve Readability with Comments/Indentation)

=IF(B2>=90,"A",
   IF(B2>=80,"B",
    IF(B2>=70,"C","D")))

SWITCH (For Exact-Value Branching, Not Ranges)

=SWITCH(C2, "New","N", "Strategic","S", "Standard","G", "Other")

Microsoft 365 Advanced: Easier Combinations Without IF

Extract Only Matching Rows with FILTER

Extract Sales & score ≥85 & (New∨Strategic) to a separate sheet for monitoring:

=FILTER(A2:C100, (A2:A100="Sales") * (B2:B100>=85) * ((C2:C100="New")+(C2:C100="Strategic")) )

LET + FILTER: Readability, Performance, and Maintainability

=LET(
  Department,A2:A100, Score,B2:B100, Status,C2:C100,
  IsSales,(Department="Sales"),
  MeetsScore,(Score>=85),
  MeetsStatus,(Status="New")+(Status="Strategic"),
  FILTER(A2:C100, IsSales*MeetsScore*MeetsStatus)
)

Alternatives / Precautions / Checklist

Blanks/typos: Standardize values with TRIM, UPPER, and similar functions before comparison.
Error handling: Wrap the final IF in IFERROR( … , "" ) to keep the output clean.
Tables (structured references): Essential for reports with expanding ranges.
Evaluate Formula (F9): Select a partial range or logical expression and press F9 to verify intermediate values.
Frequent policy changes: Use LET, IFS, and SWITCH to make rules easier to update.
Performance: For large data sets, minimize repeated calculations by assigning shared logic to LET variables; set calculation options to manual if needed.

Troubleshooting

Symptom	Cause	Solution
“Not Eligible” despite matching conditions	Spaces, typos, or leading/trailing spaces	Apply `TRIM` or `CLEAN`, or enforce Data Validation (List)
#VALUE! / #N/A errors	Mixed data types or mismatched reference ranges	Use ranges of the same length and handle exceptions with `IFERROR`
Formula is too long and difficult to manage	Too many nested IF statements	Rewrite it with `IFS`, `SWITCH`, or `LET`
References break when copied	Mixed absolute and relative references	Lock references with `$` or use table structured references
Filtering report is slow	Many repeated calculations	Cache shared logic with `LET`; in Microsoft 365, use `FILTER` for a single extraction

Wrap-Up & Recommended Next Steps

The key to using multiple conditions with the IF function is to “define logic with AND/OR and improve maintainability and readability with IFS/LET/FILTER.” For your next practice topics, we recommend these articles:

Excel XLOOKUP 다중 조건 활용법 (5 Shots)