ExcelFormulas logoExcelFormulas.
Ask AI
Logical & ConditionalVerified QA Tested

SWITCH.

Evaluates an expression against a list of values and returns the matching result.

SyntaxArgumentsExampleInteractive SandboxGeneratorCompatibilityFAQRelatedCheat Sheet
Category: Logical & Conditional
ExcelExcel 365Excel 2021Excel 2019Google Sheets

How it works.

SWITCH compares an expression against a sequence of values and returns the corresponding matching outcome. Cleaner than deeply nested IF statements.

Formula syntax.

Standard Excel Notation
=SWITCH(expression, val1, result1, [default])
Tip:Arguments wrapped in square brackets [like_this] are optional; all other arguments are required.

Formula arguments.

Every parameter accepted by the SWITCH function is detailed below.

expressionRequired

The value to evaluate.

Example value: A2
val1Required

First value to test for equality.

Example value: 1
result1Required

Output returned if expression matches val1.

Example value: "Sunday"

Simple example.

Step-by-Step Scenario

Day Number to Name Mapping

Convert numeric day codes into weekday names.

Day CodeWeekday Name
2—
1—
3—
Applied Formula
=SWITCH(A2, 1, "Sunday", 2, "Monday", 3, "Tuesday", "Other")
Expected Result:Monday

Evaluates SWITCH over the sample table cells, placing the verified output in cell B2.

Formula generator.

Full Generator Wizard

Customize the argument fields below to generate a tailored SWITCH formula ready to paste directly into your worksheet.

Generated FormulaReady to copy
=SWITCH()

Expected result behavior.

Returns "Monday" based on sample evaluation criteria and active cell references.

Sample Output:Monday
Output Type:Text String

Best Practices

  • Always specify an optional default value at the end to catch unanticipated inputs.

Common Mistakes

  • If no value matches and no default argument is provided, SWITCH returns #N/A.
Compatibility Database

Platform & version compatibility.

Verified platform compatibility matrix based on actual spreadsheet calculation engine specifications.

Excel✓
Supported
Excel 365✓
Supported
Excel 2021✓
Supported
Excel 2019✓
Supported
Google Sheets✓
Supported
Recommended Alternative for Unsupported Platforms

In Excel 2016 or older, use nested IFs or the CHOOSE function.

Microsoft Excel History & Version Availability

Introduced in Excel 2019 and Excel 365. Supported in Excel 2019, 2021, and 365.

Google Sheets Compatibility Notes

100% compatible with Google Sheets with identical expression evaluation rules.

Frequently asked questions.

Technical Q&A
Can I use SWITCH across different worksheets in Excel?↓

Yes. You can reference cells located on other sheets by including the sheet name followed by an exclamation mark, for example: =SWITCH(Sheet2!A2:A10). If the sheet name contains spaces, enclose it in single quotes: ='Q1 Report'!A2:A10.

What is the most common reason for errors in SWITCH?↓

If no value matches and no default argument is provided, SWITCH returns #N/A.

Does SWITCH work identically in Google Sheets?↓

Yes, SWITCH is supported in both Microsoft Excel and Google Sheets with standard evaluation behavior.