How do you SWITCH data in Excel?

Jan 21, 2021 | Excel Hints and Tips

Until Excel 2019 the SWITCH function did not exist but since then it has provided a viable alternative to using nested IF functions, IFS or VLOOKUPs.

It evaluates one value (called the expression) against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.  This is probably best understood by looking at the structure below and seeing how it could work in reality with an example.

Structure

=SWITCH(Expression, Value 1, Result 1, Value 2, Result 2,………….,Default)

There can be up to 126 groups of information to switch

Arguments

Expression – the expression to be evaluated

Value 1 – the value to be compared with the expression

Result 1 – the result returned if the corresponding value matches the expression

Default – Value returned if none of the values are matched

NOTE: The value and the result can be duplicated up to 126 times for multiple switches

Example

The following example can be looked at in 2 parts.  The WEEKDAY function takes the date in A2 and changes it to a weekday, numbering Sunday as 1, Monday as 2 and so on.

The SWITCH function takes that number and changes 1 to Sunday, 2 to Monday, 3 to Tuesday and if it is none of these puts in the text Other day

=SWITCH(WEEKDAY(A2),1,”Sunday”,2,”Monday”,3,”Tuesday”,”Other day”)

NOTE: The calculation stops when it hits a match so it will run most quickly if the most likely matches are placed at the start. 

Deciding whether to use SWITCH, Nested IFs, IFS or VLOOKUP will depend on the situation. 

Further Reading 

If you’ve enjoyed reading about how to use the SWITCH function on Excel, there’s some other blogs below that you might find useful:

Want to learn more about other Excel features? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Courses I run.

MEMBERSHIPS

FOLLOW LARA