SWITCH

Formulas / SWITCH
Match multiple values with the SWITCH function.
=SWITCH(expression,val1/result1,[val2/result2],...,[default])
  • expression - required, the value to compare against value1…value126
  • value1…value126 - required, a value to compare against expression
  • result1…result126 - required, the value to return if matched
  • default - [OPTIONAL] the value to return if no matches are found

Examples

  • =SWITCH(C5,1,"Poor",2,"OK",3,"Good","?")

    This example shows how the SWITCH function can be used to compare a value in a specific cell. This function will compare the value in cell C5 and if the value is 1, it will return Poor, if it is 2, it will return OK, and if it is 3, it will return Good. If the value does not match any of the given cases, it will return a question mark.

  • =SWITCH(TRUE,A1>=1000,"Gold",A1>=500,"Silver","Bronze")

    This function will compare the value in cell A1 to the range of values given and if the value is equal to or greater than 1000, it will return Gold, if it is equal to or greater than 500, it will return Silver, and if it is less than 500, it will return Bronze. If the value does not match any of the given cases, it will return the last value given, which in this case is Bronze.

  • =SWITCH(TRUE,A1>=1000,"Gold",A1>=500 AND A1<1000,"Silver","Bronze")

    This function will compare the value in cell A1 to the range of values given and if the value is equal to or greater than 1000, it will return Gold, if it is equal to or greater than 500 and less than 1000, it will return Silver, and if it is less than 500, it will return Bronze. If the value does not match any of the given cases, it will return the last value given, which in this case is Bronze.

Summary

The SWITCH function is a useful tool for comparing a single value to a list of values and returning the first matching value. It is available on both Windows and Mac computers with Office 2019 or Office 365.

  • The SWITCH function performs an exact lookup based on a given criteria and can handle up to 126 value/result pairs.
  • The optional default argument can return when no match is found, allowing for greater control over the results.
  • The SWITCH function is new in Excel 2019 and Excel 365, providing a more efficient way to perform exact lookups.


Frequently Asked Questions

What is the SWITCH function?
The SWITCH function is a feature available on Windows and Mac that compares one value to a list of values and returns the value of the first matching column in a table.
How do I use the SWITCH function?
To use the SWITCH function, you need to provide the value that you want to compare against the list of values. Then you will provide the list of values and the value that you want to be returned if the list of values matches the value that you provided. The SWITCH function will then return the value for the first matching column.
What are some possible applications for the SWITCH function?
  • The SWITCH function can be used to find the value of a particular element in a list of values.
  • The SWITCH function can be used to lookup a value in a table.
  • The SWITCH function can be used to match a value to a list of predetermined values.
  • The SWITCH function can be used to quickly find the value of an item in a list of values.
Is the SWITCH function available for both Windows and Mac?
Yes, the SWITCH function is available for both Windows and Mac.

Related Functions

Make Better Decisions
With Data

Analyze data, automate reports and create live dashboards
for all your business applications, without code. Get unlimited access free for 14 days.