Course / Logic & Decisions

Nested IF and IFS

Sort values into three or more tiers with nested IFs or the tidier IFS function. Intermediate · 5 min · Nested IF, IFS, TRUE catch-all

One IF chooses between two results. When you need three or more, such as tiers or grades, you have two options:

• Nested IF: put a second IF in the value_if_false slot, like =IF(B2>=30, "Hot", IF(B2>=15, "Mild", "Cold"))
• IFS: list test/result pairs, like =IFS(B2>=30, "Hot", B2>=15, "Mild", TRUE, "Cold")

Both check the tests in order and stop at the first one that is TRUE, so put the strictest test first.

Steps

1. Coffee club members who spend $1,500 or more are Gold, $800 or more are Silver, and everyone else is Bronze. In D2:D9, work out each tier with a nested IF.

2. Rewards depend on orders: 50+ earns a Free grinder, 25+ a Free 1kg bag, 10+ a Free tote, and everyone else gets Not yet. Fill in E2:E9 with IFS, using TRUE as the final test.

3. The tier sets the discount: Gold gets 15%, Silver 10% and Bronze 5%. In F2:F9, use IFS to test the tier in column D.

Remember

• Nested IF: =IF(test1, r1, IF(test2, r2, r3)).
• IFS: =IFS(test1, r1, test2, r2, TRUE, r3), flatter and easier to read.
• Tests run in order, so start with the strictest.
• End IFS with TRUE so nothing falls through to #N/A.

Previous: The IF function · Next: AND, OR and NOT