Skip to content

Stockertim Dictionary · Technology

Nested IF

Also called nested IFs, nested IF function, IF inside IF, IFS

In short

An IF function placed inside another IF to choose between more than two outcomes, where the tests are checked in order and the first true one decides the result.

  • Intermediate
  • Technology
  • Checked

In detail

A single IF chooses between two results. To choose between three or more, the result of one IF can itself be another IF. A tiered commission rule of 5% on sales of 400 or more, 3% on sales from 200, and nothing below 200 becomes =IF(B2>=400,B2*5%,IF(B2>=200,B2*3%,0)). The outer test runs first; only when it is FALSE does the formula move on to the inner one.

Because the first true test wins, the order of the tests is part of the logic. Tiers that rise with the amount must be tested from the highest threshold downwards when using >=. Testing B2>=200 first would give every sale of 400 or more the 3% rate, since 450 is also at least 200, and the 5% branch would never be reached.

Excel allows up to 64 levels of nesting, but formulas with more than three or four become hard to read and harder to check. Newer spreadsheet versions offer IFS, which lists test and result pairs in sequence without the nesting, and SWITCH for exact matches against a list of values. For many tiers, a small lookup table with an approximate match is clearer still, and the rates can then be changed without editing the formula.

Errors in nested IFs usually sit at the boundaries or in a missing final result. Every closing bracket must match an opening one, and the last IF needs a value for the case where no test is true.

Worked example

A wholesaler in Ibadan applies the tiered rule above to three sales.

  1. Sale of 500: first test true, 500 × 5% = 25
  2. Sale of 250: first test false, second true, 250 × 3% = 7.5
  3. Sale of 150: both tests false, so the result is 0

Each sale falls into exactly one tier, decided by the first test that is true.

In practice

Nested IFs appear in grading schemes, discount tiers, banding customers by size and labelling variances. Reviewers test them with one value inside each band and one exactly on each threshold.

Watch out

  • Testing the lowest threshold first lets it capture every higher amount.
  • Long nests are fragile; a lookup table of thresholds is easier to audit and update.

Learn it properly

Official sources

Facts last checked on 9 November 2026.

Education, not advice: the Stockertim Dictionary explains what terms mean. It never recommends buying or selling anything.

English