The SWITCH function compares one value against a list of values, and returns a result that corresponds to the first match. You can use the SWITCH function when you want to perform an exact match with several possible results, and return a default value when no match is found.
The first argument is called "expression" and can be a hard-coded constant, a cell reference, or another formula that returns a specific value you want to match against. Matching values and corresponding results are entered in pairs. SWITCH can handle up to 126 pairs, after which the last argument is an (optional) default value to return in cases where there is no match.
SWITCH only performs an exact match, so you can't include logical operators like greater than (>) or less than (<) in the logic used to determine a match. You can workaround this limitation by constructing a formula to match against TRUE like this:
However, in a case like this, you may want to use the IFS function instead.
SWITCH versus IFS
Like the IFS function, the SWITCH function allows you to test more than one condition without nesting multiple IF statements, making formulas with many conditions easier to read and write. 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 is not possible to use operators like greater than (>) or less than (<) with the standard syntax. In contrast, the IFS function actually requires expressions for each condition, so you can use logical operators as needed.
Note: The SWITCH function and IFS function are both new in Excel 2019 and Excel 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 runs a logical test and returns 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 IF...
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 look up data in a table organized vertically. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. Lookup values must appear in the first...