SWITCH Function in Excel

🔹 Part 1: Introduce

Definition: The SWITCH function is a powerful and flexible function in Microsoft Excel that allows you to evaluate an expression against multiple cases and return a corresponding value based on the first matching case. It is a compact alternative to using various nested IF functions when dealing with numerous conditions.

Purpose: The SWITCH function is used to simplify complex decision-making tasks by providing a more readable and structured way to handle multiple scenarios.

Syntax & Arguments:

SWITCH(expression, value1, result1, [value2, result2], ... , [default_value])
  • expression: The value or expression you want to evaluate.
  • value1, value2, etc.: The values that are compared against the expression.
  • result1, result2, etc.: The corresponding values are returned if the expression matches the corresponding value.
  • default_value (optional): The value returned if the expression does not match the specified values. If omitted and no match is found, the function returns #N/A.

The return value corresponds to the first matching case in the SWITCH function.

Remarks: The SWITCH function is case-sensitive, and it evaluates the expression from left to right, returning the value of the first matching case. If no match is found and a default_value is provided, it will be returned. If no match is found and no default_value is provided, the function returns #N/A.

🔹 Part 2: Examples

🔹 Example 1: Student Grades

Student IDScoreGradeFormula
210185A=SWITCH(B2, 90, “A”, 80, “B”, 70, “C”, “D”)
310278C=SWITCH(B3, 90, “A”, 80, “B”, 70, “C”, “D”)
410395A=SWITCH(B4, 90, “A”, 80, “B”, 70, “C”, “D”)

Explanation: In this example, we use the SWITCH function to assign grades based on students’ scores. Column B represents the scores of the students, and column D uses the SWITCH function to evaluate the score against different cases. If the score is 90 or above, the grade is “A”; if it is between 80 and 89, the grade is “B”; if it is between 70 and 79, the grade is “C”; otherwise, the grade is “D”.

🔹 Example 2: Sales Commission

Salesperson IDSalesCommissionTierFormula
2SP-001$10,000$500High=SWITCH(B2, 8000, “Low”, 12000, “Medium”, 10000, “High”, “N/A”)
3SP-002$6,500$325Low=SWITCH(B3, 8000, “Low”, 12000, “Medium”, 10000, “High”, “N/A”)
4SP-003$11,500$575High=SWITCH(B4, 8000, “Low”, 12000, “Medium”, 10000, “High”, “N/A”)

Explanation: In this example, we use the SWITCH function to determine the commission tier for salespeople based on their sales performance. Column B represents the sales amount, and column E uses the SWITCH function to evaluate the sales amount against different cases. If the sales amount is less than $8,000, the tier is “Low”; if it is between $8,000 and $11,999, the story is “Medium”; if it is $12,000 or above, the tier is “High”; otherwise, it is “N/A.”

🔹 Example 3: Project Status

Project IDProgressStatusDecisionFormula
2PROJ-00170%On Track“No Action”=SWITCH(B2, “Not Started”, “No Action”, “In Progress”, “No Action”, “On Track”, “No Action”, “Delayed”)
3PROJ-00295%On Track“No Action”=SWITCH(B3, “Not Started”, “No Action”, “In Progress”, “No Action”, “On Track”, “No Action”, “Delayed”)
4PROJ-00350%In Progress“No Action”=SWITCH(B4, “Not Started”, “No Action”, “In Progress”, “No Action”, “On Track”, “No Action”, “Delayed”)

Explanation: In this example, we use the SWITCH function to provide a decision on the project status based on its progress. Column B represents the project progress, and column E uses the SWITCH function to evaluate the progress against different cases. If the gain is “Not Started,” “In Progress,” or “On Track,” the decision is “No Action”; if it is “Delayed,” the decision is “Delayed.”

🔹 Example 4: Nested SWITCH with IF Function

Product NameQuantityPrice Per UnitTotal Price
2Apples10$1.00=IF(B2>5, SWITCH(C2, 1, 5, 0.9, 0.8), SWITCH(C2, 1, 5, 0.95, 0.85)) * B2 * C2
3Bananas3$0.50=IF(B3>5, SWITCH(C3, 1, 5, 0.9, 0.8), SWITCH(C3, 1, 5, 0.95, 0.85)) * B3 * C3
4Oranges7$0.75=IF(B4>5, SWITCH(C4, 1, 5, 0.9, 0.8), SWITCH(C4, 1, 5, 0.95, 0.85)) * B4 * C4

Explanation: In Example 4, we use the IF function to check if the quantity of a product (column B) is greater than 5. If it is, we use the SWITCH function to apply a discounted price based on the cost per unit (column C). If the quantity is 5 or less, we use another set of discount rates from the SWITCH function. The formula then calculates the total price by multiplying the amount with the adjusted price per unit.

🔹 Example 5: Nested SWITCH with SUM Function

CategorySales Q1Sales Q2Total Sales
2Electronics$500$700=SUM(SWITCH(A2, “Electronics”, B2:C2, “Clothing”, B3:C3, “Accessories”, B4:C4))
3Clothing$300$450=SUM(SWITCH(A3, “Electronics”, B2:C2, “Clothing”, B3:C3, “Accessories”, B4:C4))
4Accessories$200$250=SUM(SWITCH(A4, “Electronics”, B2:C2, “Clothing”, B3:C3, “Accessories”, B4:C4))

Explanation: In Example 5, we use the SWITCH function to select the relevant sales data (Sales Q1 and Q2) based on the category (column A). The SUM function then adds up the selected sales data to calculate the total sales for each category.

🔹 Example 6: Nested SWITCH with VLOOKUP and IF Functions

Customer NamePurchase 1Purchase 2Customer Rating
2John Doe$100$150=IF(SUM(B2:C2) > 200, SWITCH(SUM(B2:C2), 200, “Good”, 300, “Excellent”, “Outstanding”), “”)
3Jane Smith$50$80=IF(SUM(B3:C3) > 200, SWITCH(SUM(B3:C3), 200, “Good”, 300, “Excellent”, “Outstanding”), “”)
4Michael Johnson$300$120=IF(SUM(B4:C4) > 200, SWITCH(SUM(B4:C4), 200, “Good”, 300, “Excellent”, “Outstanding”), “”)

Explanation: In Example 6, we use the IF function to check if the total purchase amount (SUM of Purchase 1 and Purchase 2) exceeds 200. If it is, we use the SWITCH function to assign a customer rating based on specific ranges of the total amount. If the full purchase amount is 200, the rating is “Good,” if 300, the rating is “Excellent,” and for any amount greater than 300, the rating is “Outstanding.”

These examples demonstrate how the SWITCH function can be nested with different functions in Excel to perform dynamic calculations and make decisions based on various conditions.

🔹 Part 3: Tips and Tricks

  • The SWITCH function is handy when dealing with multiple conditions that lead to different outcomes. It simplifies complex logic and makes formulas more concise and readable.
  • Provide a default value in the SWITCH function to handle cases where no match is found. This helps avoid errors and provides a fallback option when no specified matters match.
  • Be cautious when using the SWITCH function with ranges or strings with similar characters. Consider using exact matches or sorting the values to ensure the correct case is matched.

These examples illustrate the versatility of the SWITCH function and its ability to handle different scenarios efficiently. Using the SWITCH function, you can streamline your Excel formulas and improve the clarity of your decision-making processes.