SWITCH formula is a logical function in Google Sheets that allows you to evaluate an expression against a list of cases and corresponding values. If the expression matches a case, the formula returns the corresponding value. If there is no match and a default case is specified, the formula returns the value for the default case. This function is commonly used for conditional formatting, data validation, and other tasks that require evaluating multiple conditions.
- How to use
- Examples of using
SWITCHformula not working?
- Similar formulas to
SWITCH formula with the syntax shown below, it has 3 required parameters and 2 optional parameters:
- expression (required):
The expression to evaluate against the list of cases. It can be a cell reference or a literal value.
- case1 (required):
The first case to evaluate. It can be a cell reference or a literal value.
- value1 (required):
The value to return if the expression matches case1. It can be a cell reference or a literal value.
- default or case2 (optional):
An optional default case to use if the expression does not match any of the cases. Alternatively, you can specify another case and value pair to evaluate, and continue adding case-value pairs as needed.
- value2 (optional):
The value to return if the expression matches case2. It can be a cell reference or a literal value.
ExamplesHere are a few example use cases that explain how to use the
SWITCH formula in Google Sheets.
Assigning grades based on scores
You have a list of test scores and you want to assign grades based on the following scale: 90-100=A, 80-89=B, 70-79=C, 60-69=D, and below 60=F. You can use the
SWITCH formula to evaluate each score against the grade boundaries and return the corresponding letter grade.
You have a list of expenses and you want to categorize them as either necessary or discretionary. You can use the
SWITCH formula to evaluate each expense against a set of categories and return the corresponding label.
Highlighting data based on criteria
You have a table of data and you want to highlight certain cells based on specific criteria. You can use the
SWITCH formula within a conditional formatting rule to evaluate each cell against a list of conditions and apply different formatting styles based on the matches.
SWITCH not working? Here are some common mistakes people make when using the
SWITCH Google Sheets Formula:
Incorrect number of arguments
One of the most common mistakes is not providing the correct number of arguments for the SWITCH formula. Make sure to include the expression, at least one case and its corresponding value, and an optional default case and value.
Mismatched data types
Another common mistake is providing case values that are of a different data type than the expression being evaluated. This can cause unexpected results or errors. Make sure the data types match for each case value.
Missing default case
If no default case is provided, the SWITCH formula will return an error if none of the provided cases match the expression being evaluated. Make sure to include a default case or check for all possible cases.
Make sure to follow the syntax for the SWITCH formula exactly. Incorrect syntax can cause errors or unexpected results.
Confusing with IF statements
Users may confuse the SWITCH formula with IF statements, which can lead to using the wrong formula or incorrect results. Make sure to understand the differences between the two formulas and use the correct one for your needs.
The following functions are similar to
SWITCH or are often used with it in a formula:
IFformula is a logical function used to test a condition and return one value if the condition is true and another value if the condition is false. It is commonly used to create conditional statements and perform calculations based on certain conditions.
IFSformula evaluates multiple conditions and returns a corresponding value for the first condition that is true. It is commonly used as a more concise alternative to nested
CHOOSEfunction returns a value from a list of values based on a given index number. The function is commonly used to select a value from a dropdown list or to lookup a value based on its position in a list. The function takes an index number and a list of values as input.
VLOOKUPfunction is a lookup formula used to search for a value in the first column of a range of cells (the search key) and return a value in the same row from a specified column in that range. This function is most commonly used to look up and retrieve data from a table.
You can learn more about the
SWITCH Google Sheets function on Google Support.