The Excel IFS Function: Cleaner Than Nested IFs

The Excel IFS function does the job of a nested IF without the nesting. You give it a list of tests and results, and it returns the result for the first test that is TRUE. It's ideal for grades, price bands and status labels. This guide covers the syntax, the one trick every IFS formula needs, and which Excel versions support it.

How does the Excel IFS function work?#

IFS checks each test in order and returns the value paired with the first TRUE test. The syntax is:

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)

Arguments come in pairs: a test, then the value to show when that test is TRUE. You can have up to 127 pairs. As soon as one test is TRUE, Excel stops and ignores the rest.

Compare the same grade formula written both ways:

=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F")))
=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F")

Both return the same grades. The IFS version is one flat list, with one pair of brackets, so it's easier to read and harder to break. For the full trade-off, see the nested IF vs IFS comparison.

Why end IFS with TRUE?#

IFS has no "otherwise" result. If none of the tests is TRUE, it returns #N/A. The fix is a last pair whose test is simply TRUE:

=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F")

TRUE always passes, so any score that reaches it gets "F". Type TRUE without quotes: it is a logical value, not text. You will see this pattern in almost every IFS formula, and it's a common job-test question.

How to write an IFS formula for price bands#

Write the pairs in order, from the highest band down with >=, or from the lowest band up with <=. Here is a shipping table that starts with the lightest parcels:

=IFS(B2<=1,4.5,B2<=5,7.95,B2<=20,12,TRUE,"Quote")

To build it yourself:

1. Click C2 and type =IFS(.
2. Type the first pair: the test B2<=1, a comma, the result 4.5, a comma.
3. Add the next pairs the same way: B2<=5,7.95, then B2<=20,12,.
4. Finish with TRUE,"Quote") and press Enter.
5. Fill down with Ctrl+D (Windows) or ⌘D (Mac).

Order still matters. If B2<=20 came first, every parcel up to 20 kg would cost 12, because Excel stops at the first TRUE test. IFS is cleaner than nested IF, but it doesn't fix tests in the wrong order.

This column mixes numbers and text. That's fine for reading, but remember that SUM skips text: a total of column C leaves out the "Quote" rows rather than warning you about them. For more on <=, >= and the other signs, read the guide to Excel comparison operators.

Can IFS return a calculation?#

Yes. A value can be a number, text, a cell reference or a calculation. This one works out commission in one step by multiplying the sales by the rate IFS picks:

=B2*IFS(B2>=100000,0.08,B2>=60000,0.05,B2>=30000,0.03,TRUE,0)

For sales of 61000, the rate is 0.05, so the result is 3050. Keep the TRUE,0 pair: without it, sales under 30000 give #N/A, and #N/A times anything is still #N/A.

You can also use AND or OR as a test inside IFS, for example =IFS(AND(C2>=50000,D2>=4.5),"Top",C2>=50000,"Good",TRUE,"Coach"). See AND, OR and NOT in Excel for how those tests work.

Should you use IFS or SWITCH?#

Use IFS when the tests are ranges or different conditions, and SWITCH when you match one value against exact options. Both need Excel 2019 or later.

So grades and price bands suit IFS, and turning status codes into labels suits SWITCH. Read the SWITCH function guide for examples.

Which versions of Excel have IFS?#

IFS is available in Excel 2019, Excel 2021, Excel 2024, Microsoft 365 (Windows and Mac) and Excel for the web. Excel 2016 bought as a one-time purchase, and earlier versions, don't have it.

If you open a file that uses IFS in an older version, the cell shows #NAME? after it recalculates, and the formula appears with an _xlfn. prefix. If your file goes to people on older Excel, or a job test runs on an older install, use a nested IF instead. It works everywhere.

What are the common IFS mistakes?#

These cause most IFS problems:

The second row catches people who know SWITCH, which does accept a lone default value at the end. IFS doesn't: every argument must be part of a pair.

Also be careful about what IFS can't catch. If B2 holds an error, such as #DIV/0!, IFS passes that error on. Fix the source if you can, or handle it deliberately with IFERROR, used the right way.

For every argument in one place, see the IFS function guide.

Practise it#

Try the shipping exercise on this page, then practise IFS and nested IF in a live spreadsheet. The lesson builds grades with both functions, shows the #N/A you get without the TRUE pair, and finishes with a commission formula.