The SWITCH function in Excel lets you easily test a certain value against a list of possible matches, and returns a result corresponding to the matching value. It's a quick and easy way to test which potential value from a list, if any, the value in a certain cell matches.
In many situations the SWITCH function, or a "switch statement," can replace the IFS function or a nested IF statement and provide a method to test against multiple potential matches, but is much easier to implement and edit.
The SWITCH function in Excel starts with the switch value, followed by pairs of match and result values, with an optional default result at the end.
=SWITCH(switch_value, match_1, result_1, [match_2, result_2, ...], [default])
This is the value that you are comparing against a list of potential matches. If this value matches one of the match arguments, the formula will return the corresponding result argument.
The match and result arguments must come in pairs, and there can be up to 126 pairs of match and result arguments, though only one pair is required. If the switch_value matches match_1, the function returns result_1. If the switch_value matches match_2, the function returns result_2, and so on.
The default argument is optional and will return if the switch_value argument doesn't match any of the supplied match arguments. If there are no match arguments that match the switch_value argument, and there is no default result, the SWITCH function will return the #N/A error.
Look at the example sales data in this example Excel spreadsheet. It shows the number of deals made by each person. Let's say you wanted to add a column that spells out the number of deals made, such as "one" rather than 1, "two" rather than 2, and so on.
You could do this with a nested IF statement, but this would get confusing after the first few numbers (think of all the parentheses!). Instead, you can do this with the SWITCH function!
Copy the following formula, then double-click into cell D2 (column D, row 2) and paste the formula into the cell.
This formula looks at the value in cell C2 (column C, row 2) and, if that value is 1, returns "One" and if the value is 2, returns "Two" and so on all the way up to 5.
Note that the numbers are not in quotes, but the spelled-out numbers are in quotes. Recall that in Excel words, or text values, go in quotes and numbers do not.
The formula you pasted into cell D2 should return a value of "Four." This is because it looks at the value in cell C2, which is 4, and returns the corresponding result argument that comes immediately after the match argument 4.
Try dragging the formula down to cell D3. The first argument should update to reference cell C3, and the formula should return a value of "Two." Again, this is because the formula looks at the value in cell C3, sees that it is 2, and returns the corresponding result argument of "Two."
You can repeat this exercise for all rows until row 6, in which case you get a #N/A error.
In Excel, the SWITCH function returns the #N/A error when there is no match argument which matches the switch value, and there is no default value supplied.
In the example of row 6 in our example spreadsheet above, we have the following formula:
The formula is referencing the value in cell C6 and looking for a match argument of 6. Since our formula does not have a match argument of 6, and does not have a default value, Excel is returning the #N/A error.
We have two options to fix our formula:
The first option is to add a match and result argument for 6. We would change our formula to this:
This option is fine, but doesn't account for more options. What if someone else comes along and gets seven deals, or eight, or a hundred? We will just keep making our formula longer and longer, until we eventually hit Excel's limit of 126 pairs of match and result arguments.
The second option would be to add a default value. This would handle any case in our current formula where the switch value is greater than five. It might look like this:
In this case, we have set the default value to the text "Big number!" This default value will appear if the value in column C is anything other than 1, 2, 3, 4, or 5. So in the case of row 6, where the formula references cell C6, it will simply return the text "Big number!" because we do not have a match argument of 6.
Our SWITCH formula would return this default value for any number of deals greater than five. It would also return the default value for a negative number or a decimal value, for example 2.5 or -3. But because we know that deals can only be measured in whole numbers and cannot be negative, this solution works for us.
It is up to you, the Excel user, to determine which solution works best with your data and your goals. For example, if your data can contain decimal values, it may be too difficult to use the SWITCH function to specify a result for each possible number; imagine having to write a condition for 1.01, 1.02, 1.03, etc. In a case like this it might make more sense to use the IFS function, where you can test whether a value falls into a given set of ranges that you define.
