The first argument in SWITCH is called “expression” and can be a hard-coded constant, a cell reference, or a formula that returns a specific value to match against. Matching values and corresponding results are entered in pairs. SWITCH can handle up to 126 pairs of values and results. The last argument, default, is an optional value to return when there is no match. In the example shown, the formula in D5 is: 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 work around this limitation by constructing a formula to match against TRUE like this: However, in a case like this, the IFS function would likely be more straightforward.
SWITCH versus IFS
Like the IFS function, the SWITCH function allows you to test more than one condition without nesting multiple IF statements in a single self-contained formula. SWITCH therefore makes it easier to write (and read) a formula with many conditions. 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.
Notes
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
