Excel SWITCH function

Summary

The Excel SWITCH function evaluates a given expression against a list of values and returns a corresponding result for the first exact match found. If no match is encountered, SWITCH has the capability to return an optional default value. This function is particularly useful for simplifying complex nested IF statements and providing a clear, readable alternative for handling multiple potential scenarios.
Syntax
				
					=SWITCH(expression, val1/result1, [val2/result2], ..., [default])
				
			
  • expression: The value or expression to match against.
  • val1/result1: The first value and result pair.
  • val2/result2: [Optional] Additional value and result pairs.
  • default: [Optional] The default value to use when no match is found.
Return value
The result corresponding to the first match found or the default value if no match is found.

How to use

Utilize the SWITCH function to compare a single expression against a series of values and return corresponding results without the complexity of nested IF statements. Provide the expression to evaluate, followed by pairs of values and their associated results. Include an optional default value to be returned if no matches are found. SWITCH is most effective for scenarios requiring exact match comparisons.

Examples

Simple SWITCH

Basic Category Assignment: To assign categories based on specific numeric codes:

				
					=SWITCH(A1, 1, "Category A", 2, "Category B", 3, "Category C", "Unknown")
				
			
This formula assigns a category based on the numeric code in A1. If the code doesn’t match 1, 2, or 3, it defaults to “Unknown”.
SWITCH with Expression
Dynamic Status Update: To provide status updates based on project completion percentages:
				
					=SWITCH(ROUNDUP(B1/25, 0), 1, "Starting", 2, "Underway", 3, "Nearly Complete", 4, "Finished", "Not Started")
				
			

Here, the formula evaluates the completion percentage in B1 and provides a status update based on the quarter of completion, with a default status of “Not Started”.

SWITCH for Error Handling
Custom Error Messages: To provide user-friendly error messages based on error types:
				
					=SWITCH(ERROR.TYPE(A1), 2, "Empty Cell", 3, "Invalid Calculation", 7, "Divide by Zero", "Unknown Error")
				
			
This formula uses the ERROR.TYPE function to identify the type of error in A1 and returns a custom message for common errors, with a default message for other types.
SWITCH in Arrays
Applying SWITCH to Arrays: To categorize an array of numbers based on ranges:
				
					=SWITCH(ROUNDUP(A1:A10/10, 0), 1, "0-10", 2, "11-20", 3, "21-30", "31+")
				
			
This array formula categorizes each number in the range A1:A10 based on its value, providing a corresponding range label for each.
SWITCH and IF
Nested Conditions: To manage complex, nested conditions with a fallback option:
				
					=SWITCH(IF(A1>0, "Positive", "Non-Positive"), "Positive", "It's Positive!", "Non-Positive", "It's Zero or Negative!", "Check your input")
				
			
This formula first uses an IF statement to categorize A1 as “Positive” or “Non-Positive” and then uses SWITCH to provide a more detailed message, with a default message for unexpected inputs.

Additional Notes

  • SWITCH is ideal for situations requiring exact matches. For scenarios involving ranges or greater/lesser comparisons, consider using the IFS function.
  • Ensure that the expression and value/result pairs are correctly paired and that the final default argument is provided for cases where no match is found.
  • The SWITCH function is available in Excel 2019 and Excel 365. For earlier versions, you may need to rely on nested IF statements or look into other logical functions.

Related Functions

Excel IF function

The Excel IF function checks a condition to return values for TRUE or FALSE outcomes, ideal for dynamic decision-making.

Excel CHOOSE function

The Excel CHOOSE function returns a value from a list based on the index number, simplifying decisions from multiple options.

Excel VLOOKUP function

The Excel VLOOKUP function searches a table's first column for a value, returning a specified column's value from the same row.

Excel IFS function

The Excel IFS function evaluates multiple conditions, returning the result for the first TRUE test, simplifying complex logical checks.

Excel MATCH function

The Excel MATCH function finds the position of a value within a range, essential for dynamic lookups and complex searches.

Content Navigation