Using IFS in Excel

How to use it and what to do if IFS isn't working on your computer (usually with #NAME error) with Excel 365 or 2016 and later.

How to use =IFS()

Introduced in Excel 2016, the Excel IFS function is designed to replace "nested" IF functions, making it easier to do multiple tests returning a value based on the first TRUE result it finds.

The syntax is as follows:
=IFS (test1, value1, [test2, value2], ...)

test1 is just like a normal IF test, for example D3>H6

value1 is just like a normal IF result.  However, you put in what you would like if that particular test is true.  If the test is false, Excel will go on to the next test in the formula (test2, test3, etc.)

test2, value2, test3, value3, etc as required for up to 127 tests/values.

Example

Cell A1 contains the text "Bank balance"

Cell A2 contains the value 200

Cell A4 contains this formula:

=IFS(A2>0,"Account has money",A2=0,"Account empty",A2<0,"Account in debt")

Things to note

  1. If Excel finds a true test (for example using the formula above, that A2 is greater than 0) then Excel will not check the tests after that one.
  2. If all tests in the IFS statement are false, the IFS function will return a #VALUE error.  This can be controlled by making the last test term simply TRUE so that this triggers the associated last value (for example, using the example above:
    =IFS(A2>0,"Account has money,A2<0,"Account in debt",TRUE,"Account empty")
  3. IFS supports up to 127 tests.
  4. For most people using Excel versions 2013, 2010 and 2007, the IFS function will produce a #NAME error as these versions do not have the IFS function built-in, so care must be taken using this function if you intend to send it to users of Excel 2013 and earlier.

IFS not working on Excel 365 or 2016?

The IFS function is unusual in that it was rolled out after Excel 2016 was already released and therefore, some Excel version 365 and 2016 users will need to update their Microsoft Excel for the IFS function to operate.

For instructions on how to update your Microsoft Excel version 365 or 2016, click here to load instructions at the Microsoft website.