SWITCH function in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of SWITCH with numeric match

What is the result of this formula in Excel?

=SWITCH(3, 1, "One", 2, "Two", 3, "Three", "Other")

Excel

=SWITCH(3, 1, "One", 2, "Two", 3, "Three", "Other")

A "Three"

B "Two"

C "Other"

D 3

💡 Hint

Look for the value that matches the first argument exactly.

The SWITCH function compares the first argument (3) to each value (1, 2, 3). When it finds 3, it returns the corresponding result "Three".

❓ Function Choice

intermediate

Choose the correct SWITCH formula for grading

You want to assign letter grades based on numeric scores:
- 90: "A"
- 80: "B"
- 70: "C"
- Otherwise: "F"

Which formula correctly uses SWITCH to do this for a score in cell A1?

A =SWITCH(TRUE, A1>=90, "A", A1>=80, "B", A1>=70, "C", "F")

B =SWITCH(A1, "A", 90, "B", 80, "C", 70, "F")

C =SWITCH(A1, 90, "A", 80, "B", 70, "C", "F")

D =SWITCH(A1>=90, "A", A1>=80, "B", A1>=70, "C", "F")

💡 Hint

SWITCH compares the first argument to each value exactly.

Option A correctly uses SWITCH with TRUE as the first argument and logical tests as values, returning the correct grade. Option A only matches exact scores, not ranges. Option A has wrong argument order. Option A mixes values and results incorrectly.

🎯 Scenario

advanced

Using SWITCH to categorize weekdays

You have a cell B2 containing a weekday name (e.g., "Monday", "Tuesday", etc.).

Which formula using SWITCH will return "Weekday" for Monday to Friday, and "Weekend" for Saturday and Sunday?

A =SWITCH(B2, "Monday", "Weekday", "Tuesday", "Weekday", "Wednesday", "Weekday", "Thursday", "Weekday", "Friday", "Weekday", "Saturday", "Weekday", "Sunday", "Weekday", "Weekend")

B =SWITCH(B2, "Saturday", "Weekend", "Sunday", "Weekend", "Monday", "Weekday", "Tuesday", "Weekday", "Wednesday", "Weekday", "Thursday", "Weekday", "Friday", "Weekday")

C =SWITCH(B2, "Monday", "Weekday", "Tuesday", "Weekday", "Wednesday", "Weekday", "Thursday", "Weekday", "Friday", "Weekday", "Saturday", "Weekend", "Sunday", "Weekend", "Unknown")

D =SWITCH(B2, "Monday", "Weekday", "Tuesday", "Weekday", "Wednesday", "Weekday", "Thursday", "Weekday", "Friday", "Weekday")

💡 Hint

Include all days and a default value for unknown days.

Option C covers all days with correct results and a default "Unknown" for any other input. Option C wrongly labels weekend days as "Weekday" and default is "Weekend" which is incorrect. Option C has correct mapping but order is different; still works but default missing. Option C misses weekend days and default.

📊 Formula Result

advanced

Result of SWITCH with missing match and no default

What is the result of this formula?

=SWITCH("Blue", "Red", 1, "Green", 2, "Yellow", 3)

Excel

=SWITCH("Blue", "Red", 1, "Green", 2, "Yellow", 3)

A 3

B #N/A

C 0

D "Blue"

💡 Hint

What happens if no match is found and no default is given?

When SWITCH does not find a match and no default value is provided, it returns the #N/A error.

❓ data_analysis

expert

Count how many cells return "Pass" using SWITCH

You have a list of scores in cells A1:A6:
85, 92, 70, 60, 88, 75

You use this formula in B1 and copy down to B6:
=SWITCH(TRUE, A1>=90, "Pass", A1>=70, "Pass", "Fail")

How many cells in B1:B6 will show "Pass"?

Excel

=SWITCH(TRUE, A1>=90, "Pass", A1>=70, "Pass", "Fail")

A 4

B 3

C 6

D 5

💡 Hint

Check each score against the conditions in order.

Scores 92, 85, 70, 88, and 75 meet >=70 condition and get "Pass". Only 60 is "Fail". So 5 cells show "Pass".