Nested IF and IFS: more than two outcomes in Excel
When a test needs more than two possible results, you either put IFs inside each other or use IFS. That is how points turn into a grade, an order value into a discount tier, or budget and cost into a traffic light.
In short: =IFS(B2>=90,"Excellent",B2>=75,"Good",TRUE,"Fail") checks the tests in order and returns the value of the first one that is true. The final TRUE means "anything else". Without IFS, the same logic is =IF(B2>=90,"Excellent",IF(B2>=75,"Good","Fail")).
Try it now
After a training course, every participant gets a result based on their test score, with four possible levels. Write the IFS formula for the first row; it is then filled down.
Task After the Excel training, each participant gets a result from their test points: 90 or more is "Excellent", 75 or more "Good", 50 or more "Pass", anything below "Fail". Write the formula with IFS in C2, it is filled down to C8.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Points | Result |
| 2 | Jana Weber | 88 | |
| 3 | Murat Kaya | 95 | |
| 4 | Lena Hoffmann | 47 | |
| 5 | Tom Schulz | 75 | |
| 6 | Sara Klein | 62 | |
| 7 | Ali Demir | 90 | |
| 8 | Mia Wagner | 50 |
The IFS syntax
IFS takes pairs of a test and a value. Excel works through them from left to right and returns the value that belongs to the first true test. Anything after that is ignored.
| Argument | Meaning | In the example |
|---|---|---|
| logical_test1 | The first and strictest condition | B2>=90 |
| value_if_true1 | The result when it is true | "Excellent" |
| logical_test2, value2 … | More pairs, up to 127 in total | B2>=75,"Good" |
| TRUE, fallback | The last pair, for everything left over | TRUE,"Fail" |
Unlike IF, IFS has no value_if_false. When none of the tests is true, it returns #N/A. That is why the last test is one that is always true: simply TRUE.
Getting the order right
Most tier formulas that go wrong have their tests in the wrong order. With "at least" thresholds, start at the top. If B2>=50 comes first, it is also true for a score of 95, Excel stops right there, and "Excellent" never shows up. With "at most" thresholds it is the other way round: start with the lowest limit, for example C2<=B2 before C2<=B2*1.1.
Test the formula with values that sit exactly on a threshold. A score of 75 must come out as Good, not Pass. Whether the boundary belongs to the tier is up to you: choose >= or >.
Nested IF
Before IFS existed, the only option was to place a second IF inside value_if_false of the first, a third inside the second, and so on. The last value_if_false is your fallback, so no TRUE is needed here.
=IF(B2>=5000,10%,IF(B2>=2000,5%,IF(B2>=500,2%,0)))
The formula ends with one closing bracket for every IF you opened. Excel allows up to 64 levels, but readability suffers long before that. Nested IF runs in every Excel version, which makes it the safe option when you send a workbook to someone on Excel 2016.
One more: discount tiers with nested IF
The result should be the discount amount, not just the percentage.
Task The discount depends on the order value: 10% from 5000, 5% from 2000, 2% from 500, nothing below that. Calculate the discount amount with a nested IF in C2, the formula is filled down to C8.
| A | B | C | |
|---|---|---|---|
| 1 | Customer | Order value $ | Discount $ |
| 2 | C-1042 | 3200 | |
| 3 | C-1017 | 780 | |
| 4 | C-1088 | 6400 | |
| 5 | C-1053 | 2000 | |
| 6 | C-1009 | 350 | |
| 7 | C-1071 | 5000 | |
| 8 | C-1036 | 1450 |
A traffic-light status
Three colors, two tests, one fallback. Order matters a lot here, because every project within budget also passes the second test. Use IFS or nested IF, whichever you prefer.
Task Build a traffic light in column D: "Green" if actual costs are at most the budget, "Amber" if they are no more than 10% over, otherwise "Red". Use IFS or a nested IF. Write the formula in D2, it is filled down to D7.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Project | Budget $ | Actual $ | Status |
| 2 | Trade fair | 12000 | 11450 | |
| 3 | Website relaunch | 25000 | 26800 | |
| 4 | Sales training | 8000 | 9300 | |
| 5 | Customer magazine | 15000 | 16200 | |
| 6 | Summer party | 4000 | 3980 | |
| 7 | CRM rollout | 30000 | 34500 |
If a tier needs two conditions at once, such as "Amber only for projects over 10,000", use AND or OR as the test: =IFS(C2<=B2,"Green",AND(C2<=B2*1.1,B2>10000),"Amber",TRUE,"Red").
When a lookup table beats IFS
From around five tiers, or as soon as the thresholds change now and then, any IF formula becomes a chore. Put the thresholds in a small table instead, sorted in ascending order: 0, 500, 2000, 5000 in the first column and the rates next to them. =VLOOKUP(B2,$F$2:$G$5,2,TRUE) then finds the right tier, and new limits go into the table without touching a formula. The details are on our page about VLOOKUP with approximate match.
Common mistakes
#N/A from IFS
None of the tests was true and the final TRUE pair is missing. Add ,TRUE,"…" as the last pair.
Numbers in quotes
B2>="90" compares a number with text and is never true. Thresholds go in without quotes; only the text results need them.
Gaps between tiers
With B2>90 followed by B2<90, a score of exactly 90 fails both tests. If you work from the top down using only >=, no gap can appear.
IFS in German Excel
In a German Excel IFS is called WENNS, and arguments are separated by semicolons:
| English | German |
|---|---|
| IF, IFS | WENN, WENNS |
| TRUE, FALSE | WAHR, FALSCH |
=IFS(B2>=90,"Excellent",TRUE,"Good") | =WENNS(B2>=90;"sehr gut";WAHR;"gut") |
B2*1.1 | B2*1,1 |
The decimal separator changes too: a point in English Excel, a comma in German Excel. Our exercises accept both spellings.
Which Excel versions have IFS?
According to Microsoft, IFS is available in Excel 2019, Excel 2021, Excel 2024 and Microsoft 365, on Windows and on Mac. A perpetual Excel 2016 licence without a Microsoft 365 subscription does not have it, and a file that uses IFS shows #NAME? there. Nested IF works in every version.
Frequently asked questions
How many IFs can you nest in Excel?
Up to 64. IFS accepts up to 127 test and value pairs. In practice, a lookup table with VLOOKUP is easier to maintain once you have about five tiers.
What does TRUE do at the end of IFS?
TRUE is a test that always passes. Placed last, it only applies when every test before it was false, which makes it the else branch that IFS lacks.
Is IFS available in Excel 2016?
Only with a Microsoft 365 subscription. With a one-time purchase of Excel 2016, use nested IF instead.
How do I turn scores into letter grades A to F?
Use IFS with one pair per grade and TRUE,"F" at the end, or build a small grade table and look it up with VLOOKUP using approximate match. The table must be sorted by score in ascending order.