Return the corresponding first match case or default.
You can also watch the quick Video.
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.
= SWITCH (expression, val1/result1, [val2/result2], …, [default])
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
Corresponding result of match value or default value or #N/A return
- 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%”.
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).
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.