How to Use Excel’s SWITCH Function: Simplify Multiple Condition Logic

INDEX

About the SWITCH Function

About the SWITCH Function

Overview of SWITCH

Returns Different Values Based on Multiple Conditions Excel Function/Logic Formula

=SWITCH(Expression, Value1, Result1, …, DefaultResult)

Summary: Compares the given expression against specified values and returns the corresponding result. If no matches are found, the default value is returned.

Supported Versions: 365 2021 2019

IFS evaluates logical conditions, while SWITCH checks for matches.
The final argument is for the “else” condition, equivalent to a default case.
If no default value is provided and no matches are found, an #N/A error will result.

In programming terms, SWITCH can be expressed as follows:

if (expression === value1) {
  return result1;
} else if (expression === value2) {
  return result2;
} else {
  return defaultResult;
}

When to Use SWITCH

To display different outputs based on user inputs.

Alternatives When SWITCH Is Not Available

SWITCH is only available in Excel 365, 2021, and 2019. It is not supported in older versions such as Excel 2016. Although IFS may seem like an alternative, it has the same version limitations. In such cases, nested IF statements can be used instead.
For example, SWITCH(A1,”A”,”1″,”B”,”2″,”Other”) can be rewritten as IF(A1=”A”,”1″,IF(A1=”B”,”2″,”Other”)).

Understanding Nested IF

IF(A1=”A”,”1″,IF(A1=”B”,”2″,”Other”))

Is A1 equal to “A”? Yes Display “1”

No Is A1 equal to “B”? Yes Display “2”

No Display “Other”

Understanding SWITCH

SWITCH(A1,”A”,”1″,”B”,”2″,”Other”)

Evaluate A1 A Display “1”

B Display “2”

Other Display “Other”

Using SWITCH with an Else Condition

The “else” logic is handled by the final argument (e.g., …,”DefaultResult”).

Checking for Blank Values with SWITCH

To check for blank cells, omit the value and provide a result for that case (e.g., SWITCH(Expression, , “Blank”, …).

Is A1 equal to “A”?	Yes			Display “1”
	No	Is A1 equal to “B”?	Yes	Display “2”
			No	Display “Other”

Evaluate A1	A	Display “1”
	B	Display “2”
	Other	Display “Other”