Let’s say you have a list of values and you want to change those values to a different value in another column. You may want to switch a phrase into an acronym or even change a date into a stated phase depending on how far away it is from the current date. Let’s take a look at the syntax a little closer and follow with an example.
The SWITCH Function Syntax
When used, the SWITCH function needs to be inputted like a formula. An example of the syntax is shown below with an explanation of each function argument. =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) Value to switch - The value or expression to match against. This is essentially a value that will be looked up in a group of cells. Value to match1/Value returned1 - The first value and result if that value matches the value to switch. This is mandatory for the function to operate. Value to match2/Value returned2 - (Optional) The second value and result if that value matches the value to switch. More values and results can be added. No match message - (Optional) This part of the need requests that you add a message if there is no match. Keep in mind that this function can be a rather long formula if you need to match lots of data. The SWITCH function can switch up to 126 values in a single function.
SWITCH Practical Example
Let’s look at an example to determine a name that represents a certain date. Study the following function for a moment. =SWITCH(DAYS(TODAY(),B5), 0, “Present date”, 1,”Yesterday”, -1,”Tomorrow”,”Unknown”). Below you see dates followed by formulas and then the results of the formulas. Basically, the date 8/9/2022 is the present date as of this text. The switch function translated today’s date to present, the last date to yesterday, and the next date to tomorrow. The value to switch in the function is represented by a number in this example. That’s where the DAYS function works in this function to translate the date into a number and serve as the value to switch. There are three values to match 0,1, and -1. Notice that each value to match is followed by a value as a result. This very useful function can be used for renaming any values in a worksheet.
Inserting the SWITCH Function
The SWITCH function can be inserted to assist individuals that are unfamiliar with the function’s syntax. This insert tool helps with functions that have complexities. Inserting the function essentially walks you through each step required to return a result. To use this method, a cell is first selected and the formulas tab must be clicked. Next, the “More Functions” button on the Excel ribbon needs to be selected. The logical selection from the list is then chosen followed by selecting the SWITCH function from the list. After the functions arguments window appears, a cell reference can either be typed in or selected from the worksheet by clicking on the up arrow to the right of the Expression and Value1 fields. The expression is the value to be switched and the value1, value2, and so on are the values that the expression will be depending on which value the expression matches.
Functional Arguments
The below fields are blank, but as fields are filled in additional fields will appear to extend the function to multiple values. This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional. © 2022 Joshua Crowder
