Lessons Lesson 28

Nested IF, and When to Stop

About this lesson

A nested IF puts a second IF in the first one's no-value. So several bands can be tested in order. Once the bands pass three, a band table with an approximate-match lookup does the same job.

By the end you can

  • nest one IF inside another for three or four outcomes.
  • say why the tests must run highest first, and what happens if they do not.
  • replace a long nest with a band table and an approximate-match lookup.

Practises IF IFS VLOOKUP

By Ali, Founder, Excel Lessons

The idea

One IF gives two outcomes. An IF inside the no-value of another gives three, and a third gives four. The tests run in order and stop at the first TRUE. That is why they must be written from the most demanding down: 90 first, then 85, then 75.

Written the other way round, the chain is wrong and silent. The first, loosest test catches everything. And past three or four bands, stop nesting. A small table of minimums and an approximate-match VLOOKUP reads the same rule in one call. It grows by a row instead of by a bracket.

The mistake to watch for

Testing in the wrong order. Nested IFs must run from the most demanding test to the least, because the first test that passes wins. Written loosest first, every score above the lowest band gets the lowest label, and no error says so. Four bands is where a nest should stop. After that, a band table with an approximate lookup is clearer to read and easier to change.

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

An IF can sit inside another IF's no-value, and runs only when the first fails.

In D2, type:

=IF(B2>=90,"Gold",IF(B2>=85,"Silver","No medal"))

Bands: 90+ Gold, 85+ Silver, 75+ Bronze, else No medal.

D2
Row A B C D E F G
1 Entry Score Region Min score Medal
2 Foxglove Bacchus 92 Kent 0 No medal
3 Nine Acre Rose 84 Sussex 75 Bronze
4 Cold Harbour Fizz 75 Kent 85 Silver
5 Larkhill Pinot 61 Hampshire 90 Gold
6 Stone Cross Red 88 Sussex
7
8

Every step

  1. 1

    An IF can sit inside another IF's no-value, and runs only when the first fails. In D2, type: =IF(B2>=90,"Gold",IF(B2>=85,"Silver","No medal")). Bands: 90+ Gold, 85+ Silver, 75+ Bronze, else No medal.

    Hint. An IF inside the third argument.

  2. 2

    Read =IF(B6>=90,"Gold",IF(B6>=85,"Silver","No medal")) for Stone Cross, which scored 88, and say what it returns.

    Hint. Second test wins.

  3. 3

    Nine Acre scored 84, a Bronze. In D3, add a third IF for the Bronze band. Shape: =IF(test, if_true, IF(test, if_true, if_false)). Test for 75.

    Hint. One more IF at the end.

  4. 4

    75 exactly still earns a Bronze. In D4, write the same three-band chain for Cold Harbour. Keep the tests highest first.

    Hint. Same chain, B4.

  5. 5

    Four bands is where a nest should stop. TRUE as VLOOKUP's last argument matches approximately. In E6, look up Stone Cross's medal in F2:G5. Shape: =VLOOKUP(lookup, table, col, TRUE).

    Hint. The lookup from module 2, with TRUE.

  6. 6

    Order matters: check the highest band first. Read =IF(B2>=75,"Bronze",IF(B2>=85,"Silver","Gold")) for Foxglove and say what it returns. Foxglove scored 92.

    Hint. The first TRUE stops everything.