SWITCH

Evaluates each case and returns the associated variable that matches first.
Return the associated variable that matches the first or the final default clause.

Rational

Return the corresponding first match case or default. 

You can also watch the quick Video.

Description

The Excel SWITCH function lookup a value (expression) in a list of multiple data points (val1) and return the corresponding first match (result1). In case of no value matches an optional default value or #N/A return.

The SWITCH statement evaluates each case and returns the associated variable that matches first. If there is no match, the default clause is enforced.

Syntax

= SWITCH (expression, val1/result1, [val2/result2], …, [default])

Parameters

expression – Lookup value against multiple arguments (val1,[val2]….)
value – A list of values to match against expression.
result – A list of corresponding ‘value to switch’ (result1,[ result2]….)
default [optional] – Value in case of no match

Return Value

Corresponding result of match value or default value or #N/A return

Key Notes

  • The Excel SWITCH function lookup value, and once it matches with the first argument (val1,[val2]….), the return value is the corresponding result (result1,[result2]….).
  • The SWITCH function can test multiple conditions like the IFS function that checks various criteria and returns the first TRUE criteria match.
  • Manage 126 pairs of values and result in arguments.
  • The SWITCH function was introduced in Excel 2016.

Let’s check out two examples:

Return the Switch Case or Default value (one)

In the first example, The formula evaluates the value in the D5 cell (expression). If it matches with one item in the list “Platinum, Gold, Silver,” then it returns the respective result “10%, 6%, 2%”; otherwise, return optional default value “0%”.

=SWITCH(C5,”Platinum”,10%,”Gold”,6%,”Silver”,2%,”No Discount”)

Match for multiple case labels (Headings) to return associated value:

In the second example, we will evaluate multiple conditions and labels before returning the corresponding statements for more than one case.

Adding to the first example, let’s think If we have Level (1&2) on top of Membership. And we want to find the discount rate based on the two labels (Level & Membership).

 =SWITCH(D5&C5,”Gold1″,5%,”Gold2″,6%,”Silver1″,2%,”Silver2″,3%,”Platinum1″,8%,”Platinum2″,10%,”No Discount”)

In this case, we can combine Level and Membership simply by using “&” operators or the Excel CONCAT or TEXTJOIN function. And then, we can use different arguments in the ‘value and result’ parameters of the function.

You can even link the formula through an Excel Table that is robust to handle a more extensive data set that you can easily change as per the requirement. Don’t forget to mark relative and absolute cell references in the formula.

=SWITCH(C5,”Platinum”,10%,”Gold”,6%,”Silver”,2%,”No Discount”)

There could be more than one way to do this, but my focus is to utilize the Excel SWITCH function. Different lookup functions can also be handy for returning the value.

Keep Reading Similar Functions:

Returns TRUE if all its arguments are correct in Excel

AND

The AND function is a logical operator that join two or more criteria i.e., if A2 is 75, =AND(A2>1,A2<50), return “FALSE” as none of the condition matches.

Read More »
Check if one of the multiple criteria match in Excel

OR

The OR function is a logical operator that returns TRUE if the condition matches i.e. =OR(A1=10, A1=100) check cell A1 contains either 10 or 100.

Read More »
Returns TRUE when only one condition is true in Excel

XOR

The XOR function returns TRUE when only one of the logic or condition is true. It returns TRUE if only one is right, not ‘at least one’ like OR function.

Read More »