SWITCH function in Excel

Hello All. Let’s explore SWITCH function in this tutorial. SWITCH is a logical function that would compare given value against pre-defined list of values and returns the first match. One can specify an optional default value to be returned if there is no match.


=SWITCH(expression, value1, result1, [default_or_value2, result2],..)

One can recognize IFS which is very similar to Switch function. IFS, too, checks for multiple condition and returns the matching result. However, the key difference between the two functions: The expression is entered once in SWITCH whereas the expression is entered for each condition in IFS. SWITCH gives an option to enter default value which is unavailable in IFS function.

Let’s look at couple of examples. First, in the below data set, we need to auto-fill the fee for the exam the student enrolled.

Here, the function first check the expression i.e., B3 which is the exam column. Then checks the given conditions (here, each exam and the corresponding fee). The function also has a default value of “$0”.

Please note that SWITCH function works only exact values and doesn’t work directly with operators like “>” or “<“. However, we can use TRUE instead of expression within SWITCH function to use > or < operators. Let’s look at the below example:

Here, the function uses >, <, AND operators to check the values / ranges and returns the appropriate rank. If the function finds a match, then the TRUE value would return. Else, the default value would be returned.

I hope this tutorial was helpful. Please provide us your feedback to improve the content. Thanks for reading.

1 thought on “SWITCH function in Excel”

Leave a Reply

%d bloggers like this: