Formulas and functions > Functions from A to Z > SWITCH (categorize initial value)

Syntax:

SWITCH(Calculation, Result1, Value1 [, Result2, Value2 ...] [, DefaultValue])

Description:

This function checks an initial value and compares it to a series of defined values for the first match.

Calculation is the initial value and can be any expression.

Result1 is the first element to be compared with the initial value for Calculation.

Value1 is to be returned if Result1 matches the initial value for Calculation.

Result2; Value2 (optional) is the second compared element, etc.

Once the first match is found, its assigned value is returned.

Note:

If no match is found, the #N/A error is returned.

However, you can set a DefaultValue instead at the end of the formula, which will be returned if no match is found (see example 2 below).

Compatibility notes:

Microsoft Excel supports this function only in version 2019 or later. The function is unknown in older versions.

Example 1:

If a specific number is found in cell A1, Calculate should output a name defined for this purpose. The following key applies: 1 stands for Lisa, 2 stands for Peter, 3 stands for Anna.

The formula SWITCH(A1, 1, "Lisa", 2, "Peter", 3, "Anna"), for example, then returns the following:

If cell A1 contains the number 1 = Lisa

If cell A1 contains the number 2 = Peter

If cell A1 contains the number 3 = Anna

If cell A1 contains anything other than 1, 2, or 3, the formula returns the result #N/A because no match was found. Alternatively, you can determine which default value the formula generally returns if no match is found (see example 2).

Example 2:

You have the same initial situation as in example 1. At the end of the formula, set a default value that appears if no match is found:

SWITCH(A1, 1, "Lisa", 2, "Peter", 3, "Anna", "no match")

If, for example, cell A1 contains 4, the result "no match" is returned.