IFS
The IFS function checks specified expressions and returns a value based on the first condition that evaluates to a boolean value of TRUE.
IFS(ifs-expression, ifs-true, ifs-expression…, ifs-true…)
ifs-expression: A logical expression. ifs-expression can contain anything as long as the expression can be evaluated as a boolean value. If the expression evaluates to a number, 0 is considered to be FALSE, and any other number is considered to be TRUE.
ifs-true: The value returned if ifs-expression is TRUE. ifs-true can contain any value. If ifs-true is omitted (there’s a comma, but no value) and ifs-expression evaluates to TRUE, IFS returns 0.
ifs-expression…: An optional logical expression to be evaluated if ifs-expression is FALSE. ifs-expression can contain anything as long as the expression can be evaluated as a boolean value. If the expression evaluates to a number, 0 is considered to be FALSE, and any other number is considered to be TRUE.
ifs-true…: The value returned if ifs-expression… is TRUE. ifs-true can contain any value. If ifs-true… is omitted (there’s a comma, but no value) and ifs-expression… evaluates to TRUE, IFS returns 0.
Notes
If all expressions are FALSE, an error is returned.
You can enter "TRUE" for the last ifs-expression… in your formula, followed by a default value for ifs-true…, to specify the result if all previous expressions (including ifs-expression and all instances of ifs-expression…) evaluate to FALSE.
Use IFS to make multiple comparisons |
|---|
The table below shows a list of student grades. |
A | B | C | |
|---|---|---|---|
1 | Student | Score | Rating |
2 | 1 | 4 | Distinguished |
3 | 2 | 2 | Apprentice |
4 | 3 | 3 | Proficient |
5 | 4 | Incomplete | |
6 | 5 | 3.2 | Proficient+ |
7 | 6 | 3.2 | Novice |
The table below returns the rating associated a score in the table above by using multiple ifs-expression… arguments.  In this formula, the following arguments are used:
To use the formula above with the other cells in the table, change B2 in each if-expression to another cell containing a score. |
Using TRUE for ifs-expression… |
|---|
Sometimes, none of the expressions evaluate to TRUE, but instead of returning an error, you can set the returned string in the final ifs-expression. In the table above, Student 4 does not have a score, so the previous formula would return an error for that student. Maybe this student was absent and still needs to complete the assignment, so rather than leave the error, you can add another ifs-expression to mark missing scores as "Incomplete":  In this example, the cell reference is changed to match the score cell for Student 4, but all of the arguments are the same as above, and one more ifs-expression is added to the end:
|
More complex comparisons |
|---|
In some cases, you may have more complex expressions in which you need to use a mixture of comparison operators. For example, the scale above uses whole numbers, but let’s say another student (Student 6) scored 3.2. You could use a formula like the one below to account for students whose scores are between the four main ratings (let B6 be 3.2, Student 5’s score):  In this example, additional ifs-expression… arguments are added in between the Distinguished, Proficient, Apprentice, and Novice ratings.
This works because IFS looks for the first true expression. If a student’s score is not 4, IFS checks to see if the score is greater than 3; Student 5 would receive a rating of Proficient+. As another example, a student with a score of 2.5 would receive a rating of Apprentice+, because their score is not 4, greater than 3, or 3, but it is greater than 2. |
Use IFS with other functions |
|---|
IFS can be used with other functions. For example, this function uses MOD to determine if the year in cell A2 (for this example, A2=2020) is a leap year:  To determine whether two values aren’t equal, use the comparison operator <>. |
Additional examples |
|---|
=IFS(A2>91,"A",A2>82,"B",A2>73,"C",A2>64,"D",TRUE,"F") returns the letter grade "A" for a number greater than 91, then returns a "B" for a number greater than 82 but less than 92, and so on for all values less than 65, which returns an "F". =IFS(A2>91,"A",A2>82,"B",A2>73,"C",A2>64,"D",A2<>0,"Attempted",TRUE,"Failed") returns the letter grade "A" for a number greater than 91, then returns a "B" for a number greater than 82 but less than 92, and so on for all values less than 65 but not equal to 0, which returns an "Attempted". If the score is 0, the formula returns "Failed". Let A2 contain "A dog" Let A1 = COUNTMATCHES(A2, REGEX("\w+")) =IFS(A1 = 0, "No word", A1 = 1, "One word", A1 = 2, "Two words", A1 > 2, "Multiple words") returns "Two words". |