SWITCH function in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of SWITCH with matching case

What is the output of this formula in Google Sheets?

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

Google Sheets

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

A "Three"

B "Other"

C "Two"

D 3

💡 Hint

Look for the value that matches the first argument exactly.

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

📊 Formula Result

intermediate

Output when no match and default provided

What does this formula return?

=SWITCH("apple", "banana", "Yellow", "orange", "Orange", "Unknown")

Google Sheets

=SWITCH("apple", "banana", "Yellow", "orange", "Orange", "Unknown")

A apple

B "Orange"

C "Yellow"

D "Unknown"

💡 Hint

Check if the first argument matches any case. If not, the default is returned.

Since "apple" does not match "banana" or "orange", the default value "Unknown" is returned.

❓ Function Choice

advanced

Choosing the right function for multiple conditions

You want to assign a grade based on a score:
- 90 or above: "A"
- 80 to 89: "B"
- 70 to 79: "C"
- Below 70: "F"

Which formula using SWITCH will correctly assign the grade for a score in cell A1?

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

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

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

D =SWITCH(A1, TRUE, "A", FALSE, "F")

💡 Hint

Use SWITCH with TRUE as the first argument to evaluate conditions.

Using SWITCH(TRUE, ...) lets you test multiple conditions in order. The first TRUE condition returns its value.

🎯 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")

B =SWITCH(B2, "Saturday", "Weekend", "Sunday", "Weekend", "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", "Saturday", "Weekday", "Sunday", "Weekday")

💡 Hint

Make sure all days are covered with correct labels and a default value.

Option C correctly maps weekdays and weekends and provides a default "Unknown" for any other input.

❓ data_analysis

expert

Count how many times SWITCH returns a specific value

You have a list of fruit names in column A (A2:A10). You use this formula in column B to categorize fruits:

=SWITCH(A2, "apple", "Red", "banana", "Yellow", "grape", "Purple", "Other")

Which formula will correctly count how many fruits are categorized as "Yellow" in column B?

A =COUNTIF(B2:B10, "Yellow")

B =COUNTIF(B2:B10, "banana")

C =COUNTIF(A2:A10, "Yellow")

D =COUNTIF(A2:A10, "banana")

💡 Hint

Count the occurrences of the category in the column where SWITCH results are stored.

Column B contains the categories (like "Yellow"), so counting "Yellow" in B2:B10 gives the correct count.