Excel SWITCH function
The SWITCH function in Excel is used to compare one value against a list of values, and returns a result corresponding to the first match, if no match is found, an optional default value is displayed.
The syntax for the SWITCH function in Excel is:
= SWITCH( expression, value1, result1, [value2, result2],..., [default] )
- expression: Required. The expression or value that is to be compared to a list of given values.
- value1, result1: Required. The first value and result pair.
- value2, result2: Optional. The second value and result pair.
- Default: Optional. The default value to return when no match is found.
|Type of Expression ||Example |
|Cell reference ||A2 |
|Logical test ||A2= “KTE” |
|Number or text ||2 or “KTE” |
|Named range ||Named-Range |
|Boolean values ||TRUE or FALSE |
|Math expression ||A2+100 |
- 1. This SWITCH function can handle up to 126 value/result pairs.
- 2. The SWITCH function is available for Excel 2019 and Office 365. All versions earlier than Excel 2019 don’t support this function.
- 3. If a default value is not defined and no matches are found, the SWITCH function will return #N/A error value.
- 4. The logical operators such as >, < or = can’t be applied to the expression in the SWITCH function. It cannot test if a value is larger or smaller.
Returns a corresponding value to the first match.
Example 1: Simple use of SWITCH function
For example, I want to use Monday to replace the number 1, Tuesday to replace the number 3, and Sunday to replace the number 7, other numbers will be replaced by No Match.
This SWITCH function can solve this job as quickly as you can.
Please enter this formula:
Example 2: Combine SWITCH function with other functions
Supposing, you have a worksheet with a column of dates, now, you want to determine if a date is occurring today, yesterday or tomorrow. In this case, you can use the TODAY function to return the serial number of the current date, and DAYS to return the number of days between two dates.
Please apply the following formula, and all the matched values have been returned into the cell based on the date.
=SWITCH(DAYS(TODAY(),C2),0,"Today",1,"Yesterday",-1,"Tomorrow", "Out of Range")
The Best Office Productivity Tools
Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.
- Designed for 1500 work scenarios, helps you solve 80% Excel problems.
- Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
- Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
- 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.