Excel Practice
Lessons Lesson 29

IFS and SWITCH

About this lesson

IFS returns the value beside the first of its test-value pairs whose test is TRUE. SWITCH returns the result beside the first value that matches the one it is given. Each can have a default at the end.

By the end you can

  • write IFS for several conditions without nesting, with a TRUE catch-all.
  • use SWITCH to map one value to many results, with a default.

Practises IFS SWITCH IF VLOOKUP

The idea

Two functions that replace a nested IF. IFS is a flat list of tests and values, read from the top. The first TRUE wins, so it follows the same order rule as a nest. SWITCH is for the case where every test is "equals this". The value is named once and the matches follow in pairs.

Both need a way to say "otherwise". In IFS it is a final pair whose test is the word TRUE. In SWITCH it is a final argument on its own. Leave it out and a value that matches nothing shows #N/A. On a helpdesk queue, that is a ticket nobody sees.

The mistake to watch for

Leaving out the catch-all. IFS with no TRUE pair at the end returns #N/A for a value that matches no test. SWITCH with no final default does the same for a value not on its list. End IFS with TRUE and a value. End SWITCH with a default. Then the column never shows an error.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

IFS takes pairs of a test and a value. It returns the value beside the first test that is TRUE, with no nesting. Priority runs by age: 48 hours or more is Critical, 24 or more High, 8 or more Medium.

In D5, type =IFS(B5>=48,"Critical",B5>=24,"High",B5>=8,"Medium") for T-1044.

D5
Row A B C D E F G
1 Ticket Hours open Category
2 T-1041 2 H
3 T-1042 26 S
4 T-1043 9 N
5 T-1044 54 S
6
7
8

Every step

  1. 1

    IFS takes pairs of a test and a value. It returns the value beside the first test that is TRUE, with no nesting. Priority runs by age: 48 hours or more is Critical, 24 or more High, 8 or more Medium. In D5, type =IFS(B5>=48,"Critical",B5>=24,"High",B5>=8,"Medium") for T-1044.

    Hint. Pairs, highest first.

  2. 2

    Read =IFS(B3>=48,"Critical",B3>=24,"High",B3>=8,"Medium") for T-1042, open 26 hours, and say what it returns.

    Hint. Second pair.

  3. 3

    When no test is TRUE, IFS returns an error. So the last pair should be the word TRUE and a catch-all value. T-1041 is two hours old and passes none of the three tests. In D2, write the IFS for it with a final pair of TRUE and Low.

    Hint. A fourth pair: TRUE, "Low".

  4. 4

    SWITCH compares one value against a list of matches. It returns the result beside the first match, with a final default. The category codes are H for Hardware and S for Software. In E2, type =SWITCH(C2,"H","Hardware","S","Software","Unassigned") to turn T-1041's code into its team.

    Hint. Value, then match-result pairs, then a default.

  5. 5

    In E4, write a formula using SWITCH that turns T-1043's code into a team name the same way. Its code is N, which is not on the list.

    Hint. Same SWITCH, C4.