How to use the SWITCH function in Excel 2016
If you've spent too much time creating a nested IF formula (IFS function), you'll love to use the new SWITCH function in Excel. This function saves you time in situations where IFS is needed. Earlier this function was only available in VBA, but recently SWITCH was added in Excel 2016, Excel Online and Mobile, Excel for computers and Android phones. This article will show you how to use the SWITCH function in Excel 2016.
- MS Excel - Lesson 15: Harness the power of the Excel function in Excel
SWITCH function syntax
The SWITCH function compares an expression with a list of values and returns the result in the first matching value. If no match is found, it can return the default value.
The structure of SWITCH function is as follows:
SWITCH (expression, value1, result1, [default or value2, result2], . [default or value3, result3])
It has 4 arguments, one of which is optional:
- Expression (expression) is a required argument used to compare value1 . value 126.
- ValueN is a value used to compare expressions.
- ResultN is the value returned when the corresponding valueN argument matches the expression. It must be specified for each valueN argument.
- Default is the value returned if no match is found in the valueN expressions. This argument has no corresponding resultN expression and must be the last argument in the function.
Because functions are limited to 254 arguments, you can use up to 126 pairs of value arguments and results.
Compare SWITCH function with IFS function
The SWITCH function, as well as the IFS function, helps define a range of conditions. However, with the SWITCH function you can define an expression and a sequence of values and results, not some conditional statements. With the SWITCH function, you do not need to repeat the expressions again as in the IFS function.
For example, the IFS function below finds the exact states.
Explanation: cell A2 contains the 85-UT string. RIGHT function extracts the 2 rightmost characters from this string (UT). As a result, the IFS function returns to the right state (Utah). If the rightmost two characters are not equal to UT, TX or OH, the IFS function returns a question mark. Instead of TRUE, you can also use 1 = 1 or something else that is always TRUE.
The SWITCH function below produces identical results but is much easier to read.
Explanation: if the first argument (RIGHT (A2.2) in this example) is in UT, the SWITCH function returns Uta; if TX returns to Texas; If OH returns to Ohio. The final argument (question mark in this example) is always the default value (if no match is found).
Similarly, you can see the following example with the rating system, the SWITCH function looks more compact.
See how the SWITCH function works when combined with other functions. Suppose, we have a few days and want to see it quickly is today, yesterday or tomorrow. To do this, we will use the TODAY function to return the serial number of the current day and the DAYS function returns the number of days between two dates.
You can see the SWITCH function works perfectly for this task.
With the IF function, conversion needs some integration and complexity. So you will be more likely to make mistakes.
However, it is not always possible to use the SWITCH function in Excel. There are many examples where you cannot use the SWITCH function instead of the IFS function.
Explain: because here the "<=" and ">" symbols are used in this IFS function, you cannot use the SWITCH function.
The SWITCH function is really a useful function, which saves time and makes fewer mistakes.
I wish you all success!
See more:
- These are the most basic functions in Excel that you need to understand
- How to combine Sumif and Vlookup functions in Excel
- How to use the SUM function to calculate totals in Excel
You should read it
May be interested
- How to use the WRAPROWS function in Excelhaving trouble with handling large data sets in excel? then learn how to use the wraprows function to split them into more manageable rows.
- How to use the IFS function in Excel 2016the ifs function in excel is a logical function introduced in excel 2016. this function is a function that replaces the nested if function and is much easier to use. the ifs function checks one or more conditions and returns a value that meets the first true condition.
- How to use the NPER function in Excel to plan loans and savingsdo you want to effectively manage and control your personal finances? then we invite you to learn how to use excel's nper function.
- How to use the function to delete spaces in Exceldeleting white space with functions in excel makes it easier for users to handle content, instead of traditional editing.
- How to fix the SUM function doesn't add up in Excelin the process of summing with sum in excel, you will encounter some errors such as not jumping the number, not adding the sum. so how to handle this problem?
- How to use the SUBTOTAL function in Excelthe subtotal function in excel is used in many different cases, helping you to sum subtotals in a list or database, unlike the sum function in excel such as counting cells, calculating average, finding the largest / smallest value. or sum the filtered list values in excel
- Save time with these text formatting functions in Microsoft Excelmicrosoft excel is a main application for anyone who has to work with numbers, from students to accountants. but its usefulness extends beyond a large database, it can do a lot of great things with text. the functions listed below will help you analyze, edit, convert, change text and save many hours of boring and repetitive tasks.
- SUMPRODUCT function in Excel: Calculates the sum of corresponding valuesthe sumproduct function is an extremely useful function when you have to deal with a lot of data numbers in microsoft excel. here are the things you need to know about the sumproduct function in excel.
- How to use the SUMIF function in Excelthe sumif function in excel is a function used to compute values in a specified range. the sumif function can be used for summing cells based on the date, data and text that are connected to the specified area.
- How to use the DAVERAGE function in Exceldaverage in excel is a function that calculates the average value of data with given conditions in a spreadsheet. so, how to use this function?