FormulaGym
XP 0
Streak 0

Nested IF in Excel

A nested IF puts one IF inside another so a formula can choose between three or more results.

What Nested IF does

Put the second IF in the place where the first would show its false result. Excel runs the tests from the left and stops at the first one that is true, so the order of the tests matters. Test the highest threshold first.

Syntax

=IF(test1, result1, IF(test2, result2, result3))
test1
The first comparison. If true, the formula stops and returns result1.
test2
Only checked when test1 is false.
result3
What to show when neither test is true.

Worked example

ABC
1StudentScoreGrade
2Ana72B
3Ben48C
4Cy64B
5Di91A
6Eli55C
C2 =IF(B2>=80,"A",IF(B2>=60,"B","C")) (copied down 5 rows)

A score of 80 or more is an A. Otherwise 60 or more is a B. Everything else is a C.

Now you try

A fresh exercise every time. Press Another exercise as often as you like.

Where you will use it

Finance

Apply commission tiers or tax bands.

HR

Turn scores into rating labels.

Sales & Ops

Group orders into small, medium and large.

Common mistakes

  • Putting the tests in the wrong order. If you test >=60 first, an 85 stops there and never reaches the A test.
  • Brackets that do not balance. You need one closing bracket for each IF.
  • Nesting too deep. Beyond three or four levels, a lookup table is easier to read and change.

The Nested IF course

  1. 1
    Grade each row three waysFree practice below
    Practise
  2. 2
    Commission tiersComing soon
  3. 3
    Tax bandsComing soon
  4. 4
    Order of tests mattersComing soon
  5. 5
    When to switch to IFSComing soon

Common questions

How many IFs can I nest?

Excel allows 64 levels, but more than three or four is hard to read. A lookup table is usually a better choice.

Is there a simpler way?

Newer versions of Excel have the IFS function, which lists the tests one after another without nesting.

Related functions