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.

Practice

1. What does the SWITCH function do in Google Sheets?

easy

A. It selects a value based on matching a given expression to cases.

B. It adds up all numbers in a range.

C. It finds the average of numbers in a list.

D. It sorts data alphabetically.

Solution

1. Step 1: Understand the purpose of SWITCH

   The SWITCH function compares a value to multiple cases and returns the matching result.

2. Step 2: Compare with other options

   Options A, B, and D describe different functions (AVERAGE, SUM, SORT), not SWITCH.

3. Final Answer:

   It selects a value based on matching a given expression to cases. -> Option A

4. Quick Check:

   SWITCH matches value to cases = It selects a value based on matching a given expression to cases. [OK]

Hint: Remember SWITCH picks result by matching value [OK]

Common Mistakes:

• Confusing SWITCH with SUM or AVERAGE
• Thinking SWITCH sorts data
• Missing that SWITCH needs a matching value

2. Which of these is the correct syntax for the SWITCH function in Google Sheets?

easy

A. SWITCH(expression; case1; result1; case2; result2; default)

B. SWITCH(expression, case1, result1, case2, result2, ..., default)

C. SWITCH(expression, case1:result1, case2:result2, default)

D. SWITCH(expression, {case1, result1}, {case2, result2}, default)

Solution

1. Step 1: Recall SWITCH syntax

   The correct syntax uses commas to separate expression, cases, results, and default value.

2. Step 2: Check each option

   SWITCH(expression, case1, result1, case2, result2, ..., default) uses commas correctly. SWITCH(expression; case1; result1; case2; result2; default) uses semicolons (wrong in Google Sheets). Options C and D use incorrect separators or braces.

3. Final Answer:

   SWITCH(expression, case1, result1, case2, result2, ..., default) -> Option B

4. Quick Check:

   Correct syntax uses commas = SWITCH(expression, case1, result1, case2, result2, ..., default) [OK]

Hint: Use commas, not semicolons, to separate SWITCH arguments [OK]

Common Mistakes:

• Using semicolons instead of commas
• Using colons or braces incorrectly
• Omitting the default value

3. What is the result of this formula in Google Sheets?
=SWITCH(A1, "Red", 1, "Blue", 2, "Green", 3, 0)
If cell A1 contains the text Blue?

medium

A. 2

B. 1

C. 3

D. 0

Solution

1. Step 1: Identify the value in A1

   A1 contains "Blue".

2. Step 2: Match A1 value with SWITCH cases

   "Blue" matches the second case, which returns 2.

3. Final Answer:

   2 -> Option A

4. Quick Check:

   Value "Blue" returns 2 [OK]

Hint: Match the cell value to cases to find result fast [OK]

Common Mistakes:

• Choosing the default value instead of matching case
• Confusing case order
• Ignoring exact text match

4. This formula returns an error. What is wrong?
=SWITCH(B2, "Yes", 10, "No", 0)

medium

A. Too many arguments provided

B. Wrong use of quotation marks

C. Missing default value argument at the end

D. Incorrect cell reference syntax

Solution

1. Step 1: Check SWITCH argument count

   The formula has expression, two cases with results, but no default value.

2. Step 2: Understand default value necessity

   SWITCH requires a default value to return if no case matches; missing it causes an error.

3. Final Answer:

   Missing default value argument at the end -> Option C

4. Quick Check:

   Always include default value in SWITCH [OK]

Hint: Always add a default value to avoid errors [OK]

Common Mistakes:

• Forgetting the default value
• Thinking quotes cause error
• Assuming cell reference is wrong

5. You want to assign grades based on scores in cell C3 using SWITCH: 90+ = "A", 80-89 = "B", 70-79 = "C", below 70 = "F". Which formula correctly uses SWITCH to do this?

hard

A. =SWITCH(C3, TRUE, "A", FALSE, "F")

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

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

D. =SWITCH(TRUE, C3>=90, "A", C3>=80, "B", C3>=70, "C", TRUE, "F")

Solution

1. Step 1: Understand SWITCH with logical TRUE

   Using SWITCH(TRUE, ...) lets us test conditions in order, returning the first TRUE match.

2. Step 2: Check each condition and result pair

   =SWITCH(TRUE, C3>=90, "A", C3>=80, "B", C3>=70, "C", TRUE, "F") correctly tests if C3>=90, then C3>=80, then C3>=70, else default TRUE returns "F".

3. Step 3: Review other options

   =SWITCH(C3, 90, "A", 80, "B", 70, "C", "F") matches exact numbers, not ranges. =SWITCH(C3>=90, "A", C3>=80, "B", C3>=70, "C", "F") misses default value. =SWITCH(C3, TRUE, "A", FALSE, "F") is incorrect logic.

4. Final Answer:

   =SWITCH(TRUE, C3>=90, "A", C3>=80, "B", C3>=70, "C", TRUE, "F") -> Option D

5. Quick Check:

   Use SWITCH(TRUE, conditions...) for range checks [OK]

Hint: Use SWITCH(TRUE, condition, result, ...) for ranges [OK]

Common Mistakes:

• Trying to match exact numbers instead of ranges
• Omitting default value
• Not using TRUE as first argument for conditions