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

By Ali, Founder, Excel Lessons

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.

Read more about this

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 pairs a test with a value; the first TRUE pair wins.

In D5, type: =IFS(B5>=48,"Critical",B5>=24,"High",B5>=8,"Medium"). 48+ is Critical, 24+ High, 8+ Medium.

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 pairs a test with a value; the first TRUE pair wins. In D5, type: =IFS(B5>=48,"Critical",B5>=24,"High",B5>=8,"Medium"). 48+ is Critical, 24+ High, 8+ Medium.

    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. In D2, write the IFS for T-1041 with a Low default. Shape: =IFS(test, value, TRUE, value). T-1041 passes none of the three tests.

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

  4. 4

    SWITCH compares one value against a list of matches. In E2, type: =SWITCH(C2,"H","Hardware","S","Software","Unassigned"). H means Hardware, S Software; anything else is Unassigned.

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

  5. 5

    In E4, write the same SWITCH for T-1043's code. Its code, N, is not on the list.

    Hint. Same SWITCH, C4.