SWITCH function

  • 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.

Technical Details

Syntax

SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])

ArgumentDescription
expression
(required)
Expression is the value (such as a number, date or some text) that will be evaluated against value1…value126.
value1…value126ValueN is a value that will be compared against expression.
result1…result126ResultN is the value to be obtained when the associated valueN argument matches expression. ResultN and must be provided for each matching valueN argument.
default
(optional)
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.

Overview

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:

Breakdown of the SWITCH function's arguments
  1. Value to switch? In this example, WEEKDAY(A2) equals 2.
  2. What value do you want to match? In this scenario, it’s 1, 2 and 3.
  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.
  4. 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.

Examples

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.

Example

ValueFormulaResult
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
2=SWITCH(A5,1,”Sunday”,7,”Saturday”,”weekday”)weekday
3=SWITCH(A6,1,”Sunday”,2,”Monday”,3,”Tuesday”,”No match”)Tuesday

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

VLOOKUP function

CHOOSE function

IF function

IFS function

Summary of formulas in Excel

Ways to avoid broken formulas

Find and correct errors in formulas

Excel keyboard shortcuts and function keys

Logical functions (reference)

Excel functions (alphabetical)

Excel functions (by category)

Leave a Reply

%d bloggers like this: