IFS and SWITCH
8 minutes · 4 steps · free, no sign-up
Nested IF formulas work, but after three levels they are hard to read and easy to break. IFS lists the tests one after another, and SWITCH matches one value against a list of possible answers. Both say the same thing in a much tidier way.
- Write IFS with several test and result pairs
- Use TRUE as a catch-all last test
- Match a code to a label with SWITCH
- Choose between IFS, SWITCH and nested IF
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceQuick reference
=IFS(test1, result1, test2, result2, ...)
=SWITCH(value, match1, result1, match2, result2, default)
IFS the first test that is true wins
SWITCH exact matches only, with an optional defaultThe key ideasSummary: IFS and SWITCH
IFS reads top to bottom and returns the result next to the first test that is true, so put the strictest test first. If none is true it gives #N/A, which is why the last pair is usually TRUE, "something": TRUE is always true, so it works as an "otherwise".
SWITCH is for exact matches: one value, a list of what it could be, and what to show for each. The final argument, with no match beside it, is the default. For ranges of numbers (a score of 70 or more) use IFS. For labels and codes use SWITCH.
Watch out forCommon mistakes
- Putting the tests in the wrong order. For grades, test
>=90before>=70. If 70 comes first, a score of 95 is stopped there and never gets its A. - Forgetting the catch-all. Without a final
TRUEpair, a value that matches nothing gives#N/A. - Using SWITCH for ranges. SWITCH only matches exact values. It cannot ask "is it 50 or more?".
- Mixing up the pairs. Every test needs a result straight after it. An odd number of arguments in IFS is an error.
Next stepTry it in the real Google Sheets
IFS and SWITCH are in Google Sheets and in current versions of Excel (SWITCH since Excel 2019). Older Excel versions need nested IF instead.