How to Use IFS in Google Sheets
=IFS(condition1, value1, [condition2, value2, ...]). If every condition is FALSE, it returns #N/A.
What IFS does
A nested IF with three or four levels is hard to read. IFS lists the same logic as flat pairs: a test, then the result for that test. Sheets works down the list and stops at the first test that is TRUE.
IFS syntax
=IFS(condition1, value1, [condition2, value2, ...])
condition1: the first test, such asB2>=90.value1: what to return if that test is TRUE.condition2, value2, ...(optional): more test and result pairs. They are checked only if every earlier test was FALSE.
A step-by-step example
Here are four test scores in cells A1:B5.
| A | B | |
|---|---|---|
| 1 | Name | Score |
| 2 | Ann | 82 |
| 3 | Ben | 54 |
| 4 | Cara | 67 |
| 5 | Dev | 91 |
To give a grade of A for 90 or more, B for 80 or more and C for 60 or more:
=IFS(B2>=90, "A", B2>=80, "B", B2>=60, "C")
For Ann's 82 this returns B. Here is why:
B2>=90is FALSE, so Sheets moves on.B2>=80is TRUE, so IFS returns the paired value,"B", and stops.
Order matters. Put the strictest test first. If B2>=60 came first, every passing score would be a C.
The #N/A trap and the catch-all
Google's help page states that if all conditions are FALSE, IFS returns #N/A. Ben's 54 matches none of the three tests above, so his row would show #N/A. IFS has no built-in "else", but you can add one: use TRUE as the last condition, which is always TRUE.
=IFS(B3>=90, "A", B3>=80, "B", B3>=60, "C", TRUE, "F")
For Ben this returns F. You could also wrap the whole formula in IFERROR or IFNA to replace the #N/A.
Common IFS mistakes
- No catch-all. A value that fits no test returns #N/A.
- Tests in the wrong order. The first TRUE test wins, even if a later one is a better fit.
- An unpaired argument. Every condition needs its own value. An odd number of arguments is an error.
- Missing quotation marks around text results.
Try it yourself
Use the score table above (A1:B5). Type your formula into the box, starting with =, then press Check.
Exercise 1: Three grades
For Ann's score in B2, write an IFS formula that returns A for 90 or more, B for 80 or more and C for 60 or more.
Exercise 2: Add a catch-all
For Ben's score in B3, use the same three grades but add a final pair so that anything below 60 returns F instead of #N/A.
What to learn next
- IF: run a test and return one of two results.
- SWITCH: match one value against a list of cases.
- IFERROR: replace an error with a clean result.
- IF with multiple conditions: combine AND and OR inside IF.
Syntax and rules in this guide were checked against Google's IFS help page. See all guides.