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

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.

Where this comes up again

Putting It Together Conditional Math
Errors: Putting It Together Errors and Robust Formulas
VLOOKUP Lookups
HLOOKUP Lookups

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. Then the second test runs only when the first one fails. The bands: 90 and above is Gold, 85 and above Silver, 75 and above Bronze, anything less No medal. Start with two bands.

In `D2`, type `=IF(B2>=90,"Gold",IF(B2>=85,"Silver","No medal"))`.

Row	A	B	C	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

An IF can sit inside another IF's no-value. Then the second test runs only when the first one fails. The bands: 90 and above is Gold, 85 and above Silver, 75 and above Bronze, anything less No medal. Start with two bands. In D2, type =IF(B2>=90,"Gold",IF(B2>=85,"Silver","No medal")).

Hint. An IF inside the third argument.
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

Nine Acre scored 84, which is a Bronze. In D3, add the third band: another IF inside the Silver one's no-value, testing for 75. Then the chain covers all four outcomes.

Hint. One more IF at the end.
4

Cold Harbour scored exactly 75, and the rules say 75 earns a Bronze. In D4, write the same three-band chain for it, keeping the tests in the same order, highest first.

Hint. Same chain, B4.
5

Four bands is where a nest should stop. The same bands are written down in F2:G5, smallest first. An approximate-match VLOOKUP reads a band table in one call. TRUE as its last argument finds the largest minimum the score has reached. In E6, get Stone Cross's medal from the table that way.

Hint. The lookup from module 2, with TRUE.
6

The order trap. Read =IF(B2>=75,"Bronze",IF(B2>=85,"Silver","Gold")) for Foxglove, which scored 92, and say what it returns.

Hint. The first TRUE stops everything.