SWITCH() Function in Excel and 3 Alternatives

Add to Favorites
Author: | Edits: don

How to use the SWITCH() function for Excel.

SWITCH() allows you to match a series of values and output a value based on that match - this replaces basic nested IF statements in Excel.

This tutorial covers the syntax of the switch function and shows you how to use it. As well, 3 robust alternatives to the switch function are also covered and explained.

The benefit to showing you this function and its alternatives is to allow you to have the tools that you need to use the best formula for the job. Sometimes you may want a complex setup that is more robust and versatile and utilizes a VLOOKUP() and a lookup table and sometimes you want an old-school IF statement and sometimes, the new SWITCH() function is the perfect tool for the job.

Syntax

=SWITCH(Value to switch, Value to match1...[2-126], Value to return if there's a match1...[2-126], Value to return if there's no match)

Example

=SWITCH(A4,1,"Good",2,"Ok",3,"Bad","Not Allowed")

A4 is the cell with the value to check.

1 is the first value to check for.

Good is the value to output if cell A4 contains 1

This pattern is followed until the last argument, which is what is output if there is no match found.

Not Allowed is the default value that is output if no match is found.

Resources

15 IF Statement Examples in Excel

5 Bite-Size Vlookup Tricks for Excel

 

Question? Ask it in our Excel Forum


Excel Function: CHOOSE(), IF(), SWITCH(), VLOOKUP()
Excel Version: Excel 2016, Excel 2019, Excel 365
Downloadable Files: Excel File

Our Excel Courses

Professional Input Forms in Excel

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

Send Emails from Excel

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Similar Content on TeachExcel
Quickly See All Cells linked to a Formula or Function in Excel
Tutorial: Ill show you how to see the cells used in a formula/function in Excel and also how to tel...
Understanding Formulas and Functions in Excel
Tutorial: In this tutorial I will cover the basic concepts of Formulas and Functions in Excel. A for...
VLOOKUP() Function in Excel
Tutorial: Full explanation of the Vlookup function in Excel, what it is, how to use it, and when yo...
Easily Input Complex Functions in Excel
Tutorial: In this tutorial I am going to show you how to easily input complex Functions in Excel. To...
OFFSET Function in Excel
Tutorial: The OFFSET function in Excel returns a cell or range reference that is a specified number...
Vlookup Function That Searches The Entire Workbook in Excel - UDF
Macro: With this VLOOKUPWORKBOOK function, you will have to power to more quickly and easily ana...
Tutorial Details
Excel Function: CHOOSE(), IF(), SWITCH(), VLOOKUP()
Excel Version: Excel 2016, Excel 2019, Excel 365
Downloadable Files: Excel File
Similar Content
Quickly See All Cells linked to a Formula or Function in Excel
Tutorial: Ill show you how to see the cells used in a formula/function in Excel and also how to tel...
Understanding Formulas and Functions in Excel
Tutorial: In this tutorial I will cover the basic concepts of Formulas and Functions in Excel. A for...
VLOOKUP() Function in Excel
Tutorial: Full explanation of the Vlookup function in Excel, what it is, how to use it, and when yo...
Excel Forum