Skip to main content

Excel SWITCH function

Author: Xiaoyang Last Modified: 2019-07-17

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.

doc switch function 1


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:

=SWITCH(C2,1,"Monday",2,"Tuesday",7,"Sunday","No match")

doc switch function 2

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")

doc switch function 3

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...


Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • 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, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations