The first argument is called "expression" and can be a hardcoded constant, a cell reference, or another formula that returns a specific value you want to match against. Matching values and results are entered in value/result pairs. SWITCH can handle up to 126 matches. A optional final argument specifies a default result to return in cases where there is no match.
SWITCH versus IFS
Like the IFS function, the SWITCH function allows you to test more than one condition without nesting, making formulas with many conditions easier to read and create. One advantage of SWITCH over IFS is that the expression appears just once in the function and does not need to be repeated. However, SWITCH is limited to exact matching. It's not possible to use operators like greater than (>) or less than (<) in the logic used to determine a match in cases where there likely will not be an exact match, for example when assigning grades with a formula.
Note: the SWITCH function is new in Excel 2016 via Office 365.
Expression can be another formula that returns a specific value.
SWITCH can handle up to 126 value/result pairs.
Enter a final argument to set a default result when no match is found.
The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions....
The Excel IFS function runs multiple tests and returns a value corresponding to the first TRUE result. Use the IFS function to evaluate multiple conditions without multiple nested IF statements. IFS allows shorter, easier to read formulas.
The Excel CHOOSE function returns a value from a list using a given position or index. For example, CHOOSE(2,"red","blue","green") returns "blue", since blue is the 2nd value listed after the index number. The values provided to CHOOSE can...
VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. The "V" stands for "vertical". Lookup values must appear in the...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.