How to Use the IFS Function in Excel (With SWITCH for Exact Matches and the Google Sheets Version)

Microsoft says IFS can take the place of several nested IF statements and is much easier to read when you've got more than one condition. It's available on Windows and Mac with Office 2019 or a Microsoft 365 subscription; the support page lists Excel for Microsoft 365, 2024, 2021 and 2019.

IFS syntax and the 127-condition limit

The syntax is IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, ...). Every test comes with its own result, in pairs:

Argument	Required?	What it holds
logical_test1	Yes	The first condition, such as A2>89
value_if_true1	Yes	What Excel returns if logical_test1 is TRUE
logical_test2, value_if_true2 and onwards	No	More test and result pairs, checked in order

IFS allows up to 127 different conditions. Microsoft doesn't recommend using anywhere near that many, though: the conditions have to be entered in the correct order, and a long chain gets hard to build, test and update.

Microsoft's grade example, row by row

Here's what =IFS(A2>89,"A",A2>79,"B",A2>69,"C",A2>59,"D",TRUE,"F") returns for a score in A2:

Score in A2	Microsoft's grade formula returns
Above 89	A
Above 79	B
Above 69	C
Above 59	D
Anything else (the TRUE default)	F

Order is the logic here. IFS stops at the first TRUE, so a score of 95 is also above 79, 69 and 59, but it gets an A because A2>89 is tested first. Put the B test at the front and every score above 79 would come back as a B. That's why you list conditions from the most specific to the least.

Why IFS returns #N/A, and the TRUE default that fixes it

If no TRUE condition is found, IFS returns #N/A. Microsoft's fix is to enter TRUE as the final logical_test: if none of the other conditions are met, its value is returned. TRUE is always TRUE, so anything that falls through every other test lands there, which is exactly how the F works in the grade formula.

Microsoft's IFS page lists two other errors you'll run into:

What you see	What Microsoft says it means
#N/A	None of the conditions was TRUE; add TRUE and a default result at the end
You've entered too few arguments for this function	A logical_test has no matching value_if_true
#VALUE!	A logical_test resolved to something other than TRUE or FALSE

Using IFS to return a cell, not just a label

The result in each pair doesn't have to be typed text. Microsoft's second example returns a cell reference instead:

=IFS(F2=1,D2,F2=2,D3,F2=3,D4,F2=4,D5,F2=5,D6,F2=6,D7,F2=7,D8)

It returns whichever cell matches the number in F2, so a 3 in F2 gives you the value in D4. Look closely and you'll notice two things. Every test is an exact match on the same cell, and there's no TRUE default, so a number outside 1 to 7 in F2 would bring back #N/A. Exact matches on one value are the job SWITCH was built for.

SWITCH: the neater choice for exact matches

SWITCH evaluates one expression against a list of values and returns the result for the first match. If nothing matches, it returns an optional default. The syntax is SWITCH(expression, value1, result1, default or value2, result2, ...), and the default is simply the final argument with no result partner. Microsoft allows up to 126 value and result pairs, and functions are limited to 254 arguments in total.

Microsoft's example is =SWITCH(WEEKDAY(A2),1,"Sunday",2,"Monday",3,"Tuesday","No match"), which returns Monday when A2 is 2. Leave the default off, as in =SWITCH(A3,1,"Sunday",2,"Monday",3,"Tuesday"), put 99 in A3, and SWITCH returns #N/A.

	IFS	SWITCH
What it tests	Any logical condition: greater than, less than, equals	One expression for equality with listed values
Default result	TRUE as the last test	A built-in final argument
Limit	Up to 127 conditions	Up to 126 value and result pairs
No match, no default	#N/A	#N/A

IFS in Google Sheets

Google's IFS works the same way: it evaluates multiple conditions and returns a value for the first true one, with the syntax IFS(condition1, value1, condition2, value2, ...). If all conditions are FALSE, you get #N/A.

Google's example, =IFS(A2 > 90, "A", A2 > 80, "B"), returns B for 88, A for 92 and #N/A for 65, because 65 fails both tests and there's no TRUE default to catch it. Sheets also accepts array conditions, such as IFS({A1:A5} > 3, "bigger", {A1:A5} = 3, "equal"), and Google lists SWITCH, MAXIFS and MINIFS as related functions.

Writing an IFS formula in five steps

1. List your conditions from the most specific to the least.
2. Type =IFS( followed by the first test, a comma and its result.
3. Add the next test and result pair, and repeat for each condition.
4. Finish with TRUE and a default result, so you never see #N/A by accident.
5. If every test is an exact match on one value, use SWITCH instead.

Once IFS has labelled your rows, you can count how many got each label with COUNTIF or colour them with conditional formatting. And when the answer lives in a separate table rather than a list of rules, VLOOKUP is usually the better tool. For a single yes or no test, plain IF is still the simpler function.

My take on IFS

IFS replaces the nested-IF staircase, and you'll thank yourself for it the next time you open the sheet. Always add the TRUE default at the end: it costs you two arguments and saves you from a column of #N/A errors you didn't see coming. And if you're only matching exact values, skip IFS and use SWITCH. It's shorter, it has a default built in, and it's easier for anyone else to read.

Frequently asked questions

What does the IFS function do in Excel?

It checks one or more conditions and returns the value for the first condition that's TRUE.

Why does my IFS formula return #N/A?

None of your conditions was TRUE. End the formula with TRUE and a default result to catch everything else.

How many conditions can IFS handle?

Up to 127, but Microsoft doesn't recommend that many because the order gets hard to build, test and update.

What's the difference between IFS and SWITCH?

IFS tests any logical condition. SWITCH checks one expression for exact matches and has a built-in default argument.

Does Google Sheets have IFS?

Yes. It works the same way, and it also returns #N/A if all conditions are FALSE.