Excel function reference

IFS / SWITCH

Quick answer

Two ways to avoid a long chain of nested IFs: IFS checks a list of conditions in order, SWITCH matches one value against a list of possibilities.

IFS(logical_test1, value1, [logical_test2, value2], ...) · SWITCH(expression, value1, result1, [value2, result2], ..., [default])

IFS reads top to bottom and stops at the very first condition that's TRUE, exactly the way a chain of nested IFs would behave, but without the pyramid of closing parentheses that makes a deeply nested IF so easy to miscount. Because it stops at the first match, order matters: a broad condition placed too early can shadow a narrower one listed below it.

SWITCH solves a different, narrower problem: matching one value against a short list of exact possibilities, a grade letter, a day code, a status, rather than testing a range of conditions. Past a handful of cases, a lookup table is usually clearer than either one.

Syntax

IFS(logical_test1, value1, [logical_test2, value2], ...) · SWITCH(expression, value1, result1, [value2, result2], ..., [default])

Argument	Description
logical_test1	IFS: Required. The first condition to check.
value1	IFS: Required. What to return if logical_test1 is TRUE.
expression	SWITCH: Required. The single value being matched, e.g. a cell reference.
value1, result1	SWITCH: Required pairs. If expression equals value1, return result1; check further pairs otherwise.
default	SWITCH: Optional. What to return when nothing matches. Omit it and a non-match returns #N/A.

A real example: a letter grade from a numeric score

A trailing TRUE condition acts as a catch-all, the IFS equivalent of an ELSE at the end of a nested IF chain:

=IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",TRUE,"D")

Each threshold is checked in order, and the first one that's TRUE wins, so the thresholds have to be listed from highest to lowest for this to grade correctly.

Worked examples

A tier from a score range

	A
1	Score
2	82

=IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",TRUE,"D") → B

(the final TRUE acts as a catch-all, like an ELSE)

An exact match against a short list

	A
1	Code
2	2

=SWITCH(A2,1,"Monday",2,"Tuesday",3,"Wednesday","Unknown") → Tuesday

Common mistakes

Writing IFS conditions out of priority order. The first TRUE one wins, so a broad condition placed too early can shadow a narrower one below it.
Leaving off a catch-all in IFS and getting #N/A on a value nobody planned for. A trailing TRUE,"..." pair covers everything else.
Using SWITCH for a condition that isn't an exact match, like a score range. SWITCH only tests equality: IFS is for conditions.

Related functions

Try it yourself

A free, graded practice exercise using this exact function: instant feedback, no signup required.

Practice IFS / SWITCH →