- Intro
The SWITCH function checks a value (called an expression) against a list of cases and returns the value that corresponds to the first case that is matched.
To use this function, enter =SWITCH in the cell, or click Menu > Data > Formula > Logical > SWITCH. Enter the arguments and press Enter.
- About the function
- Formula: =SWITCH(expression, case1, value1, [case2], [value2],..., [default value])
- Arguments:
- expression (required): The value to be judged and given the corresponding result
- case1 (required): The first situation to check if it matches the "expression"
- value1 (required): The corresponding value returned when the expression matches case 1
- case2 (optional): The second situation to check if it matches the "expression"
- value2 (optional): The corresponding value returned when the expression matches case 2
- default value (optional): The default value returned when no match is found
- Note: SWITCH supports up to 126 pairs of "case" and "value" comparisons.
- Example:
- =SWITCH(A1, "apple", 1, "banana", 2, "orange", 3,"not a fruit") means, when A1 is apple, return 1, when it is banana, return 2, and so on. If it's none of the above, return "not a fruit".
- Use cases
Use SWITCH for data integration
Suppose there is a user research project, and due to a design flaw, different types of data are collected from different channels:
The website collected numerical scores, while surveyors on the ground collected satisfaction ratings in text form.
Formula used below: =SWITCH(C2,"very dissatisfied",0,"Satisfied",60,"Very satisfied",80,B2)
Description: The first few arguments determine what score to return based on the satisfaction ratings, while the last argument simply says to use the B column (cell B2 specifically for the first result) if no matching value is found.
250px|700px|reset
- FAQs
What is the difference between the SWITCH function and IFS function?
Both of these functions can find matching results based on multiple conditions, following the logic of "when {condition}, display {result}". The difference is that the IFS function supports greater than, less than, equal to, and other criteria, while the SWITCH function only supports the equal to criteria, without using equal signs.
- Example,
When we need to convert candidate rating "A, B, C, D" in the table below to "Good, Fair, Poor, Fail", the two functions are written as:
=SWITCH(B2,"A","Good","B","Fair","C","Poor","D","Fail")
=IFS(B2="A","Good",B2="B","Fair",B2="C","Poor",B2="D","Fail")
250px|700px|reset
Clearly, the SWITCH function is much simpler. When you only need one-to-one matches, use this function.
IF and IFS functions are suitable for more complex judgments. For example, if the rating in the table above is a score instead, and each grade in rating 2 spans a range of scores, then the IFS function should be used.