Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

SWITCH

Check if one of the multiple criteria match in Excel
Check if one of the multiple criteria match

Rational

Return the corresponding first match.

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.

Syntax

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

Parameters

expression – Lookup value against multiple arguments (val1,[val2]….)
value – A list of value 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 first argument (val1,[val2]….) the return value is the corresponding result (result1,[result2]….).

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

The formula evaluate value in C5 cell (expression) and If it matches with one item in the list “Platinum, Gold, Silver” then it return corresponding result “10%, 6%, 2%” respectively, otherwise return optional default value “0%”.

The SWITCH function can test multiple conditions like IFS function that check various criteria at once and return the first TRUE criteria match.

SWITCH function can manage 126 pairs of value and result in arguments.
The SWITCH function was introduced in Excel 2016.

Keep Reading Similar Functions:

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 »
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 »