Is there Something More Flexible than CHOOSE?

May 09, 2022 - by Bill Jelen

Problem: CHOOSE is strange in that it requires values such as 1, 2, 3. What if I need to check for values like 1, 7, 64?

Strategy: The February 2016 release of Office 365 adds a new function called SWITCH. Say that you want to return “Weekend” for values of 1 or 7, but “Weekday” for all other values.

The new SWITCH function. Dates in column D. Weekday name in E. The weekday number in F where 1=Sun, 2=Mon, up to 7=Weekday. =SWITCH(F1,1,"Weekend",7,"Weekend","Weekday"). This lets you specify the condition for 1 and 7 as Weekend and let everything else become the Value_If_Other of Weekday. — Figure 458. The last argument in SWITCH is used for all other values.

SWITCH can also work with text. =SWITCH(J1,”Andy”,5,”Bill”,7,”Fred”,1,”Ralph”,3,99) can be used to assign 5 to Andy, 7 to Bill, 1 to Fred, 3 to Ralph and 99 to any other value.

This article is an excerpt from Power Excel With MrExcel

Title photo by Tolga Ulkan on Unsplash