Mar 11, 2016

If you use Excel through an Office 365 subscription or you use Excel Online or Excel Mobile, then you can take advantage of two new worksheet functions: IFS() and SWITCH()

IFS()

IFS() can make creating nested IF() functions unnecessary as IFS() lets you test for multiple conditions. The first one to return TRUE wins.

The syntax for the function is:

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],…)

Note: Up to 128 logical tests can be incorporated into an IFS() function

Here is an example of its use:
Grades converted from score out of 100 to A, B, C, D or F
=IFS(A1>89,"A",A1>79,"B",A1>69,"C",A1>59,"D",TRUE,"F")
If A1 contains 92 then IFS() will return “A”
If A1 contains 60 then IFS() will return “D”

SWITCH()

The SWITCH() function compares a value against a list of values and returns the first matching value. If there is no match, an optional default value may be returned.

The syntax for the function is:

=SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])

Note: Up to 126 values and results can be incorporated into a SWITCH() function

Here is an example of its use:

=SWITCH(A2,1,"Monday",2,"Tuesday",3,"Wednesday",4,"Thursday",5,”Friday”,"Weekend")
If A2 contains 2 then SWITCH() will return “Tuesday”
If A2 contains 6 then SWITCH() will return “Weekend”

For more of Excel’s worksheet functions, take a look at New Horizons’ Excel courses.