SWITCH function

Use of SWITCH Function in Excel

The SWITCH function in Excel evaluates an expression against a list of values and returns the corresponding result of the first match. It is useful for replacing nested IF statements when checking multiple conditions.

Syntax of SWITCH Function:

SWITCH(expression, value1, result1, [value2, result2], ..., [default])

- expression: The value or cell reference to evaluate.
- value1, result1: The first comparison value and the result to return if matched.
- [value2, result2]: Optional additional comparisons and results.
- [default]: Optional value returned if none of the comparisons match.

Example Based on Table:

Given the following data:

SL No	City Code	Switch
1	100	UK
2	200	UP
6	200	UP
11	200	UP
14	200	UP

The SWITCH formula used in the “Switch” column could be:

=SWITCH(B2, 100, "UK", 200, "UP")

To include a default value for unmatched cases:

=SWITCH(B2, 100, "UK", 200, "UP", "Unknown")

Process Flow:

1. Evaluate the City Code (e.g., cell B2).
2. Compare with first value (100):
   - If matched → Return “UK”
   - If not matched → Continue
3. Compare with next value (200):
   - If matched → Return “UP”
   - If not matched → Continue
4. If no matches and default is provided → Return “Unknown”
5. If no default and no match → Excel returns #N/A

Benefits of SWITCH:

- Easier to read and manage than nested IF statements.
- More efficient for handling multiple exact match conditions.
- Ideal for short lists of defined options.