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


Return the corresponding first match.


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.


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


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:

Returns TRUE if all its arguments are correct in Excel


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


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


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 »