Logical
IFS function in Excel:syntax and examples
IFS checks several conditions in order and returns the value of the first one that is met, with no nested IFs. In Spanish Excel it is called SI.CONJUNTO. Syntax: =IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...).
Syntax
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)In English Excel, arguments are separated with a comma (,). In Spain and much of Europe and Latin America, Excel uses a semicolon (;).
Arguments
| Argument | What it is |
|---|---|
logical_test1 | Required. The first condition checked. Example: B2>=90. |
value_if_true1 | Required. What it returns if that condition is met. Example: "Distinction". |
logical_test2, value_if_true2, ... | Optional. More pairs of condition and result, up to 127 pairs. For the "everything else" case, make the last condition TRUE. |
How do you use IFS? A step-by-step example
IFS checks conditions one after another and returns the result of the first one that is met. In this example you grade each score: Distinction from 90, Merit from 70, Pass from 50 and Fail below.
| A | B | |
|---|---|---|
| 1 | Student | Score |
| 2 | Lucy Grant | 75 |
| 3 | Mark Rivera | 48 |
| 4 | Valerie Cruz | 50 |
| 5 | Daniel Ford | 92 |
| 6 | Cameron Hayes | 31 |
=IFS(B2>=90,"Distinction",B2>=70,"Merit",B2>=50,"Pass",TRUE,"Fail") returns Merit for Lucy.
B2>=90,"Distinction"is the first pair: not met with 75, so it moves on.B2>=70,"Merit"is met and the formula stops here.TRUE,"Fail"is the "otherwise": because TRUE is always met, it catches everything that did not match before. With Mark's score, the same formula returns Fail.
Conditions go from the strictest to the loosest, as in a nested IF.
IFS, nested IF, VLOOKUP or SWITCH?
All four give Lucy the same grade. What changes is how hard they are to read and maintain.
| Formula for Lucy | Result | Versions | When to pick it | |
|---|---|---|---|---|
| IFS | =IFS(B2>=90,"Distinction",B2>=70,"Merit",B2>=50,"Pass",TRUE,"Fail") | Merit | Excel 2019 and later | 3 to 5 bands, in an Excel that has it |
| Nested IF | =IF(B2>=90,"Distinction",IF(B2>=70,"Merit",IF(B2>=50,"Pass","Fail"))) | Merit | All | When you do not know which Excel opens the file |
| VLOOKUP by bands | =VLOOKUP(B2,R2:S5,2,TRUE), with the table 0-Fail, 50-Pass, 70-Merit and 90-Distinction in R2:S5 | Merit | All | More than 5 bands, or bands that change often |
| SWITCH | For exact values only, not for "greater than" | Excel 2019 and later | Translating codes: "Yes" to Handed in |
The VLOOKUP table has one advantage: if Merit starts at 65, you change one cell and touch no formula.
How do you combine IFS with AND or OR?
Each condition can be an AND or an OR. If passing takes a 50 and a submitted project:
=IFS(AND(B4>=50,C4="Yes"),"Pass",B4>=50,"Project missing",TRUE,"Fail") returns Project missing for Valerie, who has a 50 but did not submit.
How does IFS show up in an Excel test?
In a job interview you are asked to classify by bands (commissions, shipping rates, grades) and they care that the formula reads well. If you use IFS, what they look at is the order of the conditions and that you do not forget the final TRUE.
In a skills test:
What does
=IFS(A1>=90,"Distinction",A1>=50,"Pass")return if A1 is 31? a) Fail · b) FALSE · c) #N/A · d) An empty cell
Answer: c. No condition is met and there is no final pair with TRUE.
Practice IFS
The English exercises are on the way. Until then, copy the scores table into a blank sheet and write the grade with IFS, then with a nested IF, and check that both give the same result for all five students.
Available in: Excel 2019 and later, Microsoft 365 and Excel for the web. It is not in Excel 2016 or earlier. Source: Microsoft: IFS function. Results calculated with Formulo's engine.
Common mistakes
- #N/A: no condition is met
- With a 31, =IFS(B6>=90,"Distinction",B6>=50,"Pass") gives #N/A because no condition is true. Add a last pair with TRUE: ...,TRUE,"Fail") returns Fail.
- Everything gets the same grade
- IFS keeps the first condition that is met. If you start with B5>=50, a 92 comes out as Pass: =IFS(B5>=50,"Pass",B5>=90,"Distinction",TRUE,"Fail") returns Pass. Order the conditions from the strictest to the loosest.
- Excel does not accept the formula
- The arguments come in pairs. =IFS(B2>=50,"Pass","Fail") has three and Excel warns you when you press Enter. The "otherwise" is written as one more pair: TRUE,"Fail".
- #NAME?
- Your Excel does not have IFS: it is in Excel 2019 and later, Microsoft 365 and Excel for the web. In Excel 2016 or earlier use a nested IF.
Frequently asked questions
What is IFS called in Spanish?
SI.CONJUNTO. In a Spanish Excel: =SI.CONJUNTO(B2>=90;"Distinction";B2>=50;"Pass";VERDADERO;"Fail").
How do I add an else to IFS?
It has no argument of its own. Add a condition that is always met, TRUE, at the end with the value you want: ...,TRUE,"Fail").
How many conditions does it take?
Up to 127 pairs of condition and result. With more than 5 or 6 bands, a table with VLOOKUP is easier to maintain: you change a number in the table and leave the formula alone.
Can I use AND or OR inside IFS?
Yes, like any condition: =IFS(AND(B4>=50,C4="Yes"),"Pass",B4>=50,"Project missing",TRUE,"Fail") returns Project missing for Valerie.
What is the difference between IFS and SWITCH?
IFS evaluates conditions (greater than, less than). SWITCH compares one cell with exact values: =SWITCH(C4,"Yes","Handed in","No","Pending") returns Pending.
Related functions
Practice in a real spreadsheet
Exercises with real data and instant checking. The first three are free.