CONTENTS

Excel IF Function Multiple Conditions: Nested IF, AND, OR, IFS, and SWITCH

When creating reports, you often need to evaluate multiple conditions rather than just one. For example, you can use the Excel IF function with multiple conditions to automatically calculate letter grades by score, incentive rates by sales criteria, or allowances by working hours. This guide covers nested IF → AND/OR → IFS → SWITCH with practical examples. Also see these essential Excel shortcuts for work to work faster.

Quick Fix (3-Minute Summary)

Nested IF: =IF(A2>=90,"A",IF(A2>=80,"B","C"))
AND/OR combination: =IF(AND(B2>=60,C2="Y"),"Pass","Fail")
IFS function: =IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",TRUE,"F")
SWITCH function (single-value comparison): =SWITCH(D2,"Gold",0.2,"Silver",0.1,"Bronze",0.05,0)

IF Function Basic Structure

=IF(logical_test, value_if_true, value_if_false)

The condition is a logical expression that evaluates to TRUE or FALSE. The true and false arguments can contain numbers, text, or formulas.

Handle Multiple Conditions With Nested IF

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

90 or above: A; 80 or above: B; 70 or above: C; otherwise: F
As the number of conditions increases, parentheses become more complex, so consider replacing nested IF statements with IFS.

Combine AND and OR With IF (Compound Conditions)

AND returns TRUE when all conditions are met, while OR returns TRUE when at least one condition is met.

=IF(AND(B2>=60,C2="Y"),"Pass","Fail")
=IF(OR(D2="Excellent",E2="Recommended"),"Benefit","Standard")

IFS Function — Better Readability Than Nested IF

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

Conditions are evaluated from top to bottom.
To handle cases where all conditions are FALSE, add TRUE,"default value" at the end.

SWITCH Function — Best for Single-Value Comparisons

=SWITCH(D2,
  "Gold",0.2,
  "Silver",0.1,
  "Bronze",0.05,
  0)

It is concise when you need to compare one value (D2) against multiple options and return mapped results.

Practical Examples (Reproducible)

1) Letter Grades by Score

Name	Score	Grade (Formula)
Kim Cheolsu	95	`=IF(A2>=90,"A",IF(A2>=80,"B","C"))` → A
Lee Younghee	82	B
Park Minsu	67	C

2) Incentive Rates by Sales Criteria

=IFS(B2>=1000000,0.20,
     B2>=700000,0.15,
     B2>=500000,0.10,
     TRUE,0)

3) Allowances by Working Hours

=IF(AND(C2>8,C2<=12),C2*5000,
   IF(C2>12,C2*7000,0))

Readability and Maintenance Tips

Use defined names to make formulas more meaningful (for example, TargetScore and GradeTable).
Switch to a lookup approach with a reference table + XLOOKUP:
```
=XLOOKUP(A2,GradeCriteriaRange,GradeResultRange,"F",-1)
```
Handle exceptions with IFERROR:
```
=IFERROR(IFS(...),"Review required")
```

Troubleshooting

Issue	Cause	Solution
Formula is too long	Too many nested IF statements	Simplify with IFS/SWITCH or a reference table
Results are reversed	Incorrect condition order	Reorder conditions from largest value to smallest value
#N/A or blank result	All conditions are FALSE	Add `TRUE,"default value"` at the end
Text and numbers cause an error	Inconsistent column formats	Standardize formats and use `NUMBERVALUE`/`TRIM`

{ “@context”: “https://schema.org”, “@type”: “FAQPage”, “mainEntity”: [ { “@type”: “Question”, “name”: “How do I create multiple conditions with the Excel IF function?”, “acceptedAnswer”: { “@type”: “Answer”, “text”: “Use nested IF statements or combine AND/OR within IF. If you have many conditions, use IFS for improved readability. For single-value comparisons, SWITCH is recommended.” } }, { “@type”: “Question”, “name”: “What is the difference between nested IF and IFS?”, “acceptedAnswer”: { “@type”: “Answer”, “text”: “Nested IF statements can become difficult to read as parentheses become deeply nested, while IFS lists conditions and results sequentially for better readability. Add a TRUE default value at the end to handle cases where all conditions are FALSE.” } }, { “@type”: “Question”, “name”: “When should I use AND or OR?”, “acceptedAnswer”: { “@type”: “Answer”, “text”: “Use AND in the IF condition when all multiple conditions must be met. Use OR when meeting either condition is sufficient.” } }, { “@type”: “Question”, “name”: “How can I reduce errors?”, “acceptedAnswer”: { “@type”: “Answer”, “text”: “Order conditions from largest value to smallest value, add a default value for cases where all conditions are FALSE, and use IFERROR to handle exceptions.” } } ] }

엑셀 조건부 서식 완벽 가이드: 수식 규칙·아이콘 집합·데이터 막대·관리 팁까지

Excel IF Function Multiple Conditions: Complete Guide (AND, OR, IFS, SWITCH)