IFS in Excel: how to use it
IFS checks a series of conditions one after another and gives the result of the first one that's true. That way you avoid nested IF formulas.
Try the first exercises for freeIn the lesson IFS
What does IFS do?
IFS checks a series of conditions one after the other and gives the outcome of the first one that's true. Each time, you write a pair: a test, and what you want to see if it's true.
That's clearer than an IF inside an IF inside an IF. Put the strictest test first, because Excel stops at the first one that's true, and finish with TRUE as a safety net for everything that's left.
When do you use IFS?
- Turning a score into a grade: =IFS(B2>=16,"Excellent",B2>=12,"Good",B2>=10,"Pass",TRUE,"Fail").
- Sorting customers into A, B and C by their revenue: =IFS(C2>=10000,"A",C2>=2500,"B",TRUE,"C").
- A discount percentage per band: =IFS(B2>=1000,10%,B2>=500,5%,TRUE,0).
- A status based on a due date: =IFS(B2<TODAY(),"Overdue",B2<TODAY()+7,"This week",TRUE,"Later").
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
The outcome of the first test that's true.
Filled in: =IFS(C3>=1000,"High",C3>=300,"Medium",TRUE,"Low")
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
IFS | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | IFS |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
test1 | The first condition, such as C2>=1000. Put the strictest one first. | C3>=1000 |
value1 | What you see if test1 is true. | "High" |
test2 | The next condition. Excel only looks at it if test1 isn't true. | C3>=300 |
value2 | What you see if test2 is true. | "Medium" |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. | |
... | You can give more, with a comma between each one. |
IFS step by step
E1 contains =IFS(C3>=1000,"High",C3>=300,"Medium",TRUE,"Low"). 340 doesn't pass the first test, but it does pass the second. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- In E1, type the function with a bracket.
=IFS( - The first pair: the strictest test, C3>=1000, a comma and "High".
=IFS(C3>=1000,"High" - The second pair: C3>=300 and "Medium".
=IFS(C3>=1000,"High",C3>=300,"Medium" - Finish with TRUE and "Low": that's the outcome if none of the tests is true.
=IFS(C3>=1000,"High",C3>=300,"Medium",TRUE,"Low" - Close the bracket and press Enter: 340 doesn't pass the first test, but it does pass the second, so you see Medium.
=IFS(C3>=1000,"High",C3>=300,"Medium",TRUE,"Low")
More examples
Grading a score
D1 contains =IFS(B2>=16,"Excellent",B2>=12,"Good",B2>=10,"Pass",TRUE,"Fail"). 13 doesn't reach 16, but it does reach 12: Good. Also try 9 and 17 in B2.
A discount per band
B2 contains =IFS(A2>=1000,10%,A2>=500,5%,TRUE,0). 750 doesn't reach the band of 1000, but it does reach the band of 500: a 5% discount.
Choosing from fixed values with SWITCH
B2 contains =SWITCH(A2,1,"Low",2,"Medium",3,"High","Unknown"). SWITCH compares one cell with a list of fixed values. That's shorter than IFS when every test checks whether something is equal.
Order is everything
Excel stops at the first test that's true. So put the strictest test first and finish with TRUE as the default result.
Where it often goes wrong
- Without a final TRUE you get #N/A when no test is true.
- Available from Excel 2019 onwards.
Practise this in the lesson IFS. The first exercises are free.
Frequently asked questions
Is this better than nested IFs?
Yes, the formula is much easier to read. In very old versions of Excel you have no choice and have to nest.