IFS & SWITCH Function in Excel

Learn how to use Excel IFS and SWITCH Functions with simple language, real-life examples, and best practices for cleaner, smarter formulas.

Excel is more than just numbers and charts—it’s also about making smart decisions using formulas. Two powerful functions that help you make logical decisions in Excel are IFS and SWITCH. These functions are upgrades to the classic IF function and are especially useful when you’re dealing with multiple conditions.

If you’ve ever found yourself writing a formula with multiple nested IF statements, you’ll love the simplicity and clarity that IFS and SWITCH bring to your work.

---

What is the IFS Function in Excel?

The IFS function lets you test multiple conditions without having to nest multiple IF functions.

Syntax:

=IFS(condition1, result1, [condition2, result2], …)

Key Points:

• Tests conditions in order.
• Returns the result for the first TRUE condition.
• If none match and no default is added, Excel returns an error.

Example 1: Grade Assignment

Score	Formula (in B2)	Output
85	=IFS(A2>=90, “A”, A2>=80, “B”, A2>=70, “C”, A2>=60, “D”)	B

Also Read: IF Function in Excel (with AND & OR)

---

What is the SWITCH Function in Excel?

The SWITCH Function is great when you need to evaluate one expression against a list of possible values.

Syntax:

=SWITCH(expression, value1, result1, [value2, result2], …, [default])

Key Points:

• Compares a single value to multiple possible matches.
• Works like a simplified version of IF when checking for exact matches.

Example 2: Day to Full Name

Day Abbreviation	Formula	Output
Tue	=SWITCH(A2, “Mon”, “Monday”, “Tue”, “Tuesday”, “Wed”, “Wednesday”,  “Other”)	Tuesday

---

IFS vs SWITCH: When to Use Which?

Situation	Use
Multiple different conditions	IFS
One value compared to many possible matches	SWITCH
Need a default value if nothing matches	SWITCH (easier)
Numerical ranges (e.g., score grading)	IFS

---

Example 3: Tax Brackets with IFS

Income	Formula	Output
48000	=IFS(A2<=25000, “5%”, A2<=50000, “10%”, A2<=100000, “20%”, A2>100000, “30%”)	10%

---

Example 4: Department Code to Name with SWITCH

Code	Formula	Output
HR	=SWITCH(A2, “HR”, “Human Resources”, “IT”, “Information Tech”, “FIN”, “Finance”, “Unknown”)	Human Resources

---

Example 5: Incentive Calculation Based on Product Targets

Business Rule:

If the targets for Product 1, Product 2, and Product 3 are all ≥ 100%, the employee receives ₹500. Otherwise, ₹0.

---

Sample Table:

📥 Click here to download the sheet as Excel

---

Formula for Incentive (in E2):

Assuming:

• B2 = Product 1
• C2 = Product 2
• D2 = Product 3

=SWITCH(TRUE,
AND(B2>=100, C2>=100, D2>=100), 500, 0
)

👉 Drag this formula down for each row.

---

Explanation:

• SWITCH(TRUE, ...): Allows logical testing (like IFS)
• AND(B2>=100, C2>=100, D2>=100): Checks if all products met or exceeded 100%
• If true → 500, else → fallback result 0

Example 6: Sales Performance Grading (Using IFS/SWITCH)

Use Case (MIS Context):

You are generating a Sales MIS Report where each salesperson’s monthly performance is graded based on their achievement against the target.

---

Sample Table:

Salesperson	Target (₹)	Achieved (₹)	Grade Formula	Grade
Ramesh	100000	125000	=IFS(C2>=B2*1.2,"A+", C2>=B2,"A", C2>=B2*0.8,"B", C2<B2*0.8,"C")	A+
Priya	100000	100000		A
Ankit	100000	85000		B
Neha	100000	60000		C

---

Formula (using IFS):

=IFS(
C2>=B21.2, “A+”, C2>=B2, “A”, C2>=B20.8, “B”,
C2<B2*0.8, “C”
)

---

Explanation:

• If achieved ≥ 120% of target → A+
• If achieved 100%–119% → A
• If achieved 80%–99% → B
• If < 80% → C

This helps Sales Managers quickly evaluate each employee’s performance in a monthly MIS report.

---

Using SWITCH(TRUE, ...):

=SWITCH(TRUE,
C2>=B2*1.2, "A+",
C2>=B2, "A",
C2>=B2*0.8, "B",
TRUE, "C"
)

---

Why It’s Useful in MIS:

• Easy to analyze and visualize sales team performance
• Makes dashboards more readable
• Can be used with conditional formatting to highlight top/low performers

Tips for Using IFS and SWITCH

• IFS processes conditions in order, so always list the most likely matches first for better performance.
• Always consider using a default fallback value (like TRUE, "Unknown") at the end of IFS.
• SWITCH is cleaner when comparing one variable to several known values.
• Use IFS when conditions involve ranges or logical comparisons (e.g., >, <, =).

---

Summary

The IFS and SWITCH functions simplify your formulas by removing the need for multiple nested IF statements.

• Use IFS for multiple condition checks.
• Use SWITCH when checking one value against many options.
• Both improve readability and maintainability of your Excel sheets.

These functions make your Excel models not only smarter—but easier to understand for your team.

FAQs

What’s Next?

In the next post, we’ll learn about the SUMIF & SUMIFS in Excel