How to use IFS in Excel
Returns the value for the first test that is TRUE.
Syntax
logical_test1A test, like F6>=100value_if_true1What to return when it’s TRUEExample
Here’s IFS on a small table of orders. The highlighted cells are the ones the formula reads.
=IFS(F6>=100,"Large",F6>=50,"Medium",TRUE,"Small")Edit this formula live Change the ranges and watch the answer update. Free, no sign-up.
Common mistakes with IFS
Add a catch-all
If no condition is true, IFS returns #N/A. End with TRUE,"Other" to cover everything else.
Order matters
IFS returns the first true condition, so test the biggest threshold first: >=100 before >=50.
What the error messages mean
Type these slips into Gridwise and this is the feedback you get:
Leaving off the closing bracket
=IFS(F6>=100,"Large",F6>=50,"Medium",TRUE,"Small"
Parentheses don’t match. You have 1 opening and 0 closing parentheses. Every ( needs a ).
Text without quotes
=IFS(F6>=100,Large,F6>=50,"Medium",TRUE,"Small")
Text needs quotes. Large isn’t a function or a cell, so Excel can’t read it. If you mean the word, write "Large" with quotes.
Questions
What does the IFS function do in Excel?
Returns the value for the first test that is TRUE. The syntax is IFS(logical_test1, value_if_true1, …).
Why is my IFS formula giving the wrong answer?
Add a catch-all: If no condition is true, IFS returns #N/A. End with TRUE,"Other" to cover everything else. Order matters: IFS returns the first true condition, so test the biggest threshold first: >=100 before >=50.
Does IFS work in Google Sheets?
Most Excel functions, including the core ones on this page, work the same way in Google Sheets. A few newer functions arrived in Sheets later or behave slightly differently, so test it on your own data.