Google Sheets SWITCH Function | Allows Multiple Cases | Tutorial | Google Sheets Formulas
The Google Sheets SWITCH function checks an expression against a list
of cases and, if found, returns the value corresponding to the first matching
case. If a case is not found, the SWITCH function returns a default value, if
it is specified.
The expression attribute of the SWITCH function can be, for example, a cell reference or a range. A case attribute is an element of the expression. A value attribute can be either text or a number.
----------------------------------------------
Here are the links to the FILTER, COUNT, and MAX function video tutorials:
FILTER
• Google Sheets Filter Function | Extra...
COUNT
• Google Sheets Count | How to Use COUN...
MAX
• MAX & MIN Function | MAX, MATCH, OFFS...
----------------------------------------------
Let's look at the format of the SWITCH function formula:
=SWITCH(expression, case1, value1, [case2_or_default], [value2], [...])
Start the formula with an equal-to symbol.
SWITCH is the name of the function.
expression is a cell or range.
case1 is the first text or number, which is checked for its presence in the expression.
value1 is the first value, which is returned, corresponding to case1.
case2 is optional, and is the second text or number, which is checked against the expression.
default is optional. If it is specified, the SWITCH function returns it, if none
of the cases, can be matched against the expression.
value2 is the second value, which is returned, corresponding to case2.
Let's look at a scenario where you would want to use the SWITCH function:
Consider this spreadsheet data for the SWITCH function. Assume Item is
in the range A3:A11, Code is in the range B3:B11, and Type is in the range C3:C11 (excluding the column labels):
Item Code Type
Barley 1WG
Split pigeon pea 3LE
Oats 1WG
Sorghum 1WG
Brown rice 1WG
Horse gram 5PU
Quinoa 1WG
White rice 4RG
Clove 2SP
For each item in the Item column, say you want to assign its type, based on the item's Code. For example, say for the codes "1WG", "3LE", "5PU", and "4RG" and "2SP", you want to assign the text "Whole grain", "Lentil", "Pulse", and "Other" respectively.
Here's the SWITCH function formula for the above task:
=SWITCH(expression, case1, value1, [case2_or_default], [value2], [...])
=SWITCH(B3:B11,"1wg", "Whole grain", "3le", "Lentil", "5pu", "Pulse", "Other")
The SWITCH function formula will return:
Item Code Type
Barley 1WG Whole grain
Split pigeon pea 3LE Lentil
Oats 1WG Whole grain
Sorghum 1WG Whole grain
Brown rice 1WG Whole grain
Horse gram 5PU Pulse
Quinoa 1WG Whole grain
White rice 4RG Other
Clove 2SP Other
Do take a look at this video tutorial, which gives the steps to use the Google Sheets SWITCH function with an example.