Excel SWITCH Function – How To Use

The SWITCH function in MS Excel is a logical function used to compare one value (or the result of an expression) against a list of values and returns the first exact match found as a result.

If there is no matching value found, the SWITCH function can return an optional default value. SWITCH function can only be found in Excel 2019 or later versions.

Syntax

The syntax of the Excel SWITCH function is as follows:

Arguments:

expression – The expression that has to be compared with a list of values is given here. This expression can be a constant, a reference to a cell, result of another formula, or a plain Boolean value like TRUE or FALSE.
value1 – The first value against which the expression has to be compared is given here.
result1 – The result that has to be returned when the expression matches the first value.
value2 – The second value against which the expression has to be compared when the first match turns out to be false. This is an optional parameter.
result2 – The result that has to be returned when the expression matches the second value.
default – This is an optional parameter. The default value is returned when no exact match is found during the comparison.

Important Characteristics of SWITCH Function in Excel

• The SWITCH function can compare an expression against 126 values/result pairs.
• SWITCH function can accept a default argument, which is returned when no matching value found
• When there are no matching values found, and the default argument is not specified, the SWITCH function returns a #N/A! error.
• SWITCH function only performs an exact match. We cannot use logical operators like greater than (>) or less than (<) in the logic used to determine a match.

Examples of SWITCH Function

Let's have a look at some of the examples of the SWITCH function.

Example 1 – Plain Vanilla Version of SWITCH Function

In this example, we have Student Data that includes 'Student Names' and 'Student Codes'. Our objective is to populate the values in the 'Student Type' column.

The logic for this is that all students with 'Student Code' equal to 1 are categorized as 'International' whereas the students with 'Student code' 2 are categorized as 'Domestic'.

To populate the 'Student Type' values, we can make use of the formula –

Let's try to dissect the above formula –

Here, the 'expression' parameter passed to the SWITCH function is B2. B2 is the cell reference that contains the value' 1′.

'1' is passed to the function as 'value1' parameter, and 'International' is passed as 'result1' parameter. This essentially means that when the 'expression' passed equals 1, then the result should be 'International.'

'2' is passed to the function as 'value2' parameter, and 'Domestic' is passed as 'result2' parameter. This implies that when the 'expression' passed equals 2, then the result should be 'Domestic'.

Thus, for the first student, the 'expression' is 1, and hence the result is 'International'. While for the second student, the 'expression' is 2, and hence the result is 'Domestic'. In the same way, student type is generated for all the students.

Example 2 – SWITCH Function with a default value

Now let's take the previous example one step ahead and see what happens when the 'expression' does not match any of the specified values.

As you can see, in this case, the 'expression' in B7 is compared against the values 1 and 2 to determine a match. Since the expression does not match any of the specified values and there is no default argument specified, the SWITCH function returns a #N/A! error.

To prevent this error, we assign a default value within the function for the expressions that don't match the specified values. In this case, we will add "Invalid Type" as the 'default' argument.

So, the formula will become:

After specifying the default value for the SWITCH function, it will return the default value, i.e., "Invalid Type" in the cases where an exact match is not found. It should also be noted that in the absence of a default value, the SWITCH function throws a #N/A! error when no matching values are found.

Example 3 – SWITCH Function with Logical Operators

We know that the SWITCH function can only be used for exact matching.

What if I told you that there is a workaround for this?

Yes, with clever use of logical operators (like greater than (>) or less than (<)) in values parameter, we can perform logical matches as well. Let's quickly jump to an example and try to see this in action.

In this example, we have a student table with 'student name' and their corresponding 'scores' out of 100. We aim to populate a grade against each student according to the scores.

Students with scores higher than 90 should be assigned 'Grade A', scores between 90 to 81 correspond to 'Grade B', scores between 80 to 71 correspond to 'Grade C', while scores of less than 71 correspond to 'Grade D'.

The SWITCH function based formula used in this case is:

Now, let's try to dissect this formula and try to understand what is happening inside.

In the above formula, the expression has a value TRUE. After that, for the values parameter, the expression in B2 is compared using logical operators. Since each value parameter can evaluate into a TRUE or a FALSE and the SWITCH function will return the result corresponding to the first value that comes out to be TRUE.

For instance, in the first case, the expression value 'TRUE' is matched against the following.

• B2 >= 91. The value at B2 is 75, so the expression further simplifies to 75 >= 91, which evaluates to FALSE. Hence the match won't happen in this case.
• Next, B2 is compared with 81, like B2 >= 81 (or 75 >= 81). This again comes out to be FALSE. Hence the match won't happen in this case either.
• After this, B2 is compared with 71, like B2 >= 71 (or 75 >= 71). This condition evaluates to TRUE, and hence a match is determined, and its corresponding value, i.e., "Grade C" is returned as a result.

The formula that we have used above is correct, but there is a small mistake.

If you closely look at Row 7, you will be able to see that even though the value in B7 is 124 and we have specified the default value as "Invalid", the SWITCH function returns the result "Grade A" which seems to be incorrect.

Why does this happen? 

We very well know that – SWITCH function returns the value of the first exact match found and does not check any further matches. This is because 124 is greater than 91. We'll explain this by running through the function's brain.

The function computes the value in B7, i.e., 124, against the first logical expression – B2 >= 91. It finds 124 to be greater than 91 and so returns "Grade A" as a result. It didn't let 124 be processed until the end of the function because it had already found the first exact match.

Whoops! Now, let us check if this issue is resolved when we specify a condition – B2 >100, "Invalid" as the first test value in the formula.

The formula becomes:

It worked! How though?

Remember, the SWITCH function always returns the result of the FIRST exact match found. By placing the default value first in the formula, we allow the invalid values to be caught first.

SWITCH vs IFS Function

Both SWITCH and IFS functions help check a set of conditions and return a result based on the given expression. They evaluate multiple conditions and serve as an easy and quick alternative to the nested IF statements.

One minor edge of SWITCH over IFS is that the expression or the condition to be tested appears just once in the function and does not need to be repeated.

However, the critical difference between IFS and SWITCH functions is that with standard syntax, the SWITCH function can only compute the exact match of the expression specified in the formula.

With SWITCH, you cannot use the logical operators like greater than (>), or less than (<) in the expression used to decide a match. For that reason, when an exact match is to be returned as a result, SWITCH could be a better option.

But the IFS function can compute different logical expressions and return a value based on the logical computation. Thus, when the expressions have to be logically evaluated before returning a result, IFS serves as a great option.

That was all about the SWITCH function in Excel. We hope, we gave you a good grip on how it works. We will see you soon with another fantastic Excel function. Until then, keep crunching those numbers.