- Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2019 for Mac.
The SWITCH function evaluates one value (called the expression) in relation to a list of values, and extracts the result reflecting the first matching value. If no match is found, then an optional default value could be captured.
Note: This feature is available on Windows or Mac if you have Office 2019, or if you have a Microsoft 365 subscription. However, if you are a Microsoft 365 subscriber, make sure you have the latest version of Office.
SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])
|Expression is the value (such as a number, date or some text) that will be evaluated against value1…value126.|
|value1…value126||ValueN is a value that will be compared against expression.|
|result1…result126||ResultN is the value to be obtained when the associated valueN argument matches expression. ResultN and must be provided for each matching valueN argument.|
|Default is the value to capture in case no matches are located in the valueN expressions. The Default argument is established by having no associated resultN expression (see examples). Default is required to be the last argument in the function.|
Since functions are restricted to 254 arguments, you can use up to 126 pairs of value and result arguments.
In its simplest form, the SWITCH function states:
- =SWITCH(Value to switch, Value to match1…[2-126], Value to return if there’s a match1…[2-126], Value to return if there’s no match)
Where you can evaluate up to 126 matching values and results.
See the following formula:
- Value to switch? In this example, WEEKDAY(A2) equals 2.
- What value do you want to match? In this scenario, it’s 1, 2 and 3.
- If there’s a match, what do you want to return as a result? In this situation, it would be Sunday for 1, Monday for 2 and Tuesday for 3.
- Default value to return if there’s no match gathered. In this case, it’s the text “No match”.
Note: If there are no matching values, and no default argument is supplied, the SWITCH function returns the #N/A! error.
You can copy the example data in the following table and paste it in cell A1 of a new Excel worksheet to witness the SWITCH function in all its glory. If the formulas don’t present results, you can pick them, then press F2 > Enter. If you must, you can modify the column widths to view all the data.
|2||=SWITCH(WEEKDAY(A2),1,”Sunday”,2,”Monday”,3,”Tuesday”,”No match”)||Because A2=2, and Monday is the result argument linking to the value 2, SWITCH returns Monday|
|99||=SWITCH(A3,1,”Sunday”,2,”Monday”,3,”Tuesday”)||Because a match and else argument are missing, SWITCH returns #N/A!|
|99||=SWITCH(A4,1,”Sunday”,2,”Monday”,3,”Tuesday”,”No match”)||No match|