The function works similarly to nested IF functions. But its syntax is much simpler, making it easy for anyone to read formulas in a spreadsheet.\u00a0<\/span><\/p>\n It\u2019s difficult to fully grasp this function without seeing some examples. But it\u2019s worth the investment to learn how to use it.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”syntax” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =SWITCH(logical_expression, case1, value1, [case2, value2]\u2026, default)<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The logic behind this function is illustrated in the flowchart below.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Switch-image-1.png” alt=”switch-function-google-sheets-1″ title_text=”switch-function-google-sheets-1″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.23.1″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”how” _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Let\u2019s start with the standard version of the function, and take a look at how it works. <\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =SWITCH(A2, 1, \u201dAlex\u201d, 2, \u201dBob\u201d, 3, \u201dCharlie\u201d, \u201dNone\u201d)<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/06\/SWITCH-FORMULA-GOOGLE-SHEETS-1.png” alt=”SWITCH FORMULA GOOGLE SHEETS 1″ title_text=”SWITCH FORMULA GOOGLE SHEETS 1″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In this example, the expression is just \u20182\u2019. It is compared with all three cases, and it matches the second case so the function returns the text \u2018Bob\u2019.<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Let\u2019s see what happens when none of the cases match the expression.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/06\/SWITCH-FORMULA-GOOGLE-SHEETS-2.png” alt=”SWITCH FORMULA GOOGLE SHEETS 2″ title_text=”SWITCH FORMULA GOOGLE SHEETS 2″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The expression \u20185\u2019 is FALSE for all three cases, and as a result the function returns the default value \u2018None\u2019.<\/span><\/p>\n If you don\u2019t define a default value the SWITCH function <\/span>produces an error, as shown below.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/06\/SWITCH-FORMULA-GOOGLE-SHEETS-3.png” alt=”SWITCH FORMULA GOOGLE SHEETS 3″ title_text=”SWITCH FORMULA GOOGLE SHEETS 3″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Say I\u2019ve planned out my wardrobe for the week, with a different color for every weekday.<\/span><\/p>\n Column D has various weekdays and in column E SWITCH matches the day of the week with the correct color.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =SWITCH(C2, A2, B2, A3, B3, A4, B4, A5, B5, A6, B6, \u201dNot a valid weekday\u201d)<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/06\/SWITCH-FORMULA-GOOGLE-SHEETS-4.png” alt=”SWITCH FORMULA GOOGLE SHEETS 4″ title_text=”SWITCH FORMULA GOOGLE SHEETS 4″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The function here is long, but you can easily break it down into three parts:<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”should” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The following example illustrates the benefits of the SWITCH function when compared to the IF function. It adapts the formula to use IF instead of SWITCH.<\/span><\/p>\n The formula is noticeably more complicated.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =IF(C2 = A2, B2, IF(C2 = A3, B3, IF(C2 = A4, B4, IF(C2 = A5, B5, IF(C2 = A6, B6, “Not a valid weekday”)))))<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/06\/SWITCH-FORMULA-GOOGLE-SHEETS-5.png” alt=”SWITCH FORMULA GOOGLE SHEETS 5″ title_text=”SWITCH FORMULA GOOGLE SHEETS 5″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Use IF to perform logical tests <\/b>on your data. As this function can be used with logical expressions such as \u201csmaller than\u201d or \u201cgreater than\u201d.\u00a0<\/span><\/p>\n Use SWITCH to work with exact matches<\/b> within a dataset, as it will help avoid complex nested IF statements<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”combining” _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text module_id=”month” _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Some date functions like MONTH return numbers that may be hard to interpret. You can use SWITCH to return more meaningful messages.<\/span><\/p>\n To replace the month numbers returned by MONTH you can set a case for each of the 12 potential results.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =SWITCH(MONTH(<\/span>C21<\/span>), <\/span>1<\/span>, <\/span>“JAN”<\/span>, <\/span>2<\/span>, <\/span>“FEB”<\/span>, <\/span>3<\/span>, <\/span>“MAR”<\/span>, <\/span>4<\/span>, <\/span>“APR”<\/span>, <\/span>5<\/span>, <\/span>“MAY”<\/span>, <\/span>6<\/span>, <\/span>“JUN”<\/span>, <\/span>7<\/span>, <\/span>“JUL”<\/span>, <\/span>8<\/span>, <\/span>“AUG”<\/span>, <\/span>9<\/span>, <\/span>“SEP”<\/span>, <\/span>10<\/span>, <\/span>“OCT”<\/span>, <\/span>11<\/span>, <\/span>“NOV”<\/span>, <\/span>12<\/span>, <\/span>“DEC”<\/span>, <\/span>“No match”<\/span>)<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”weekday” _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Similarly to MONTH, the output of WEEKDAY is difficult to read. Not only does it output numbers instead of text, the numbers can be set to correspond to different days.<\/span><\/p>\n This is the basic formula to use if Sunday is considered the first day of the week.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =SWITCH(WEEKDAY(C21, 1), 1, “SUN”, 2, “MON”, 3, “TUE”, 4, “WED”, 5, “THU”, 6, “FRI”, 7, “SAT”, “No match”)<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”errortype” _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Another formula that returns cryptic codes is ERROR.TYPE, and SWITCH can replace them with more meaningful messages.<\/span><\/p>\n The formula below is just a starting point. It would be a good idea to adapt it to provide hints on how to fix these errors on your spreadsheet.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =IF(ISERROR(C21), SWITCH(ERROR.TYPE(C21), 1, “Null error”, 2, “Division by zero error”, 3, “Value error”, 4, “Reference error”, 5, “Name error”, 6, “Number error”, 7, “Not available error”, 8, “Getting data error”, 9, “Spill error”, 10, “Calc error”, “Unknown error”), “No error”)<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”sheetgo” _builder_version=”4.27.2″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n SWITCH is a great function, but it can only be used in a handful of cases. Sheetgo provides an arsenal of data processing tools to automate your workflows.\u00a0<\/span><\/p>\n This platform has the potential to revolutionize your work, automating tedious tasks and sharing insights across your team.<\/span><\/p>\n These are just some of the key Sheetgo features:<\/span><\/p>\n Try out Sheetgo today<\/a> and start saving precious time.<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The SWITCH function in Google Sheets is an alternative to nested IF statements.<\/span><\/p>\n By evaluating an expression against a series of cases and returning the corresponding value for the first true case, it simplifies complex logical tests and makes your formulas easier to understand<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" This Google Sheets SWITCH evaluates an expression against a set of specific cases. The first case to evaluate as TRUE will return a corresponding value. If all cases are FALSE, the formula can return a default value. The function works similarly to nested IF functions. But its syntax is much simpler, making it easy for […]<\/p>\n","protected":false},"author":40,"featured_media":29378,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"Syntax<\/h2>\n
\n
How to use the SWITCH function<\/span><\/h2>\n
Example 1: Basic use<\/span><\/h3>\n
Example 2: Setting a default value<\/span><\/h3>\n
Example 3: Matching weekdays with colors<\/span><\/h4>\n
\n
Should I use SWITCH or IF function?<\/h4>\n
Combining SWITCH with other functions<\/span><\/h2>\n
Using <\/span>SWITCH with MONTH<\/span><\/h3>\n
Using <\/span>SWITCH with WEEKDAY<\/span><\/h3>\n
Combining <\/span>SWITCH with ERROR.TYPE<\/span><\/h3>\n
Handle your data with Sheetgo<\/span><\/h2>\n
\n
SWITCH it up!<\/span><\/h2>\n
![\"SWITCH](\"https:\/\/importsheet.com\/wp-content\/uploads\/2017\/03\/SWITCH-Formula-Illustration-1-1024x1020.png\")
<\/p>\n![\"SWITCH](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/switch.png\")
<\/p>\n![\"SWITCH](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/switch2-1.png\")
<\/p>\n![\"SWITCH](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/switch3-1.png\")
<\/p>\n![\"SWITCH](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/switch4.png\")
<\/p>\n