This tutorial is applicable to Excel 2019 onward and with a Microsoft 365 license.
In Excel 2019 there are a load of new functions, one of which is the IFS Function. Given last week’s topic, ‘When to say no to Excel Nested IFs’ I thought it was a good time to introduce the Excel IFS function.
The IFS function removes the need to nest IF functions.
IFS Syntax:
=IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, logical_test3, value_if_true3,….)
You can nest up to 127 logical tests, but that doesn’t mean you should.
Watch the Video
Download the Workbook
Enter your email address below to download the sample workbook.
Excel IFS Function Example
In column B we can use the IFS function to return the State for each City in column A. I’ve added line breaks in the formula bar in the image below so it’s easier to read:
Here is the IFS formula without the line breaks:
=IFS([@City]="Sydney","NSW", [@City]="Melbourne","VIC", [@City]="Adelaide","SA", [@City]="Brisbane","QLD", [@City]="Darwin","NT",[@City]="Perth","WA", TRUE,"Not Found")
In earlier versions of Excel we’d use a nested IF like this:
=IF([@City]="Sydney","NSW", IF([@City]="Melbourne","VIC", IF([@City]="Adelaide","SA", IF([@City]="Brisbane","QLD", IF([@City]="Darwin","NT", IF([@City]="Perth","WA","Not Found"))))))
The benefit of the new IFS function is an easier to build and read formula.
IFS Value if False
You may have noticed that unlike the IF function, with the IFS function there is no ‘value_if_false’ argument. Instead we simply insert a logical test at the end that evaluates to TRUE, or just type in TRUE, and then insert our value_if_false argument.
=IFS([@City]="Sydney","NSW", [@City]="Melbourne","VIC", [@City]="Adelaide","SA", [@City]="Brisbane","QLD", [@City]="Darwin","NT",[@City]="Perth","WA",TRUE,"Not Found")
Limitations
The IFS Function is limited to 127 logical tests. However, if your formula uses more than 7 I’d consider an alternative, because this formula will get difficult to maintain and may result in performance issues.
There are more efficient ways to get the same results, as discussed in this tutorial ‘When to say no to Excel Nested IFs’. Even though this article deals with Nested IF’s, the same issues apply with IFS.
IFS in Earlier Versions of Excel
If a user opens an Excel file containing the IFS function in an earlier version of Excel it will still display the result, but the function will be prefixed with _xlfn. Like so:
=_xlfn.IFS([@City]="Sydney","NSW", [@City]="Melbourne","VIC", [@City]="Adelaide","SA", [@City]="Brisbane","QLD", [@City]="Darwin","NT", [@City]="Perth","WA", TRUE,"Not Found")
They can happily work in the file and save it without breaking the formula.
However, should the user edit the cell in a version of Excel that doesn’t have the IFS function the result will be converted to the #NAME? error. Opening it in Excel 2016 again will fix the errors.
Excel SWITCH Function
Trish
Im trying to calculate net pay and taxes to be owed and I’m not sure why my IF formula isn’t working. I have columns A – N. Column A contains the company name which I am currently contracted with. In column B is the hourly/flat rate, C – pay frequency but this is just for my reference, D – hours worked, E – Gross, F – Pre-tax benefits (this amount automatically deducts from gross prior to tax calculation using an IF formula),G-J Tax rates (fed, state, med, etc.) K – contains after tax benefit deductions, and in L should be net pay. I work for multiple companies only one pays per hour.
If column A contains company ABC (the hourly payer) then I want column L to calculate F minus G – K and return the result and if column A contains any other company name I want the result to be column E minus G-K. Every time i think I figured it out I get the #NAME? error and I just can’t seem to figure out the error.
Thanks in advance!
Catalin Bombea
Hi Trish,
Please upload a small sample of your data and formulas so we can see what’s wrong, it’s impossible to guess. Use our forum, you can upload there.
Star Gagnon
I am so stuck! Can anyone help me here?
=IFS([@AccountSupport]=”Wade Abern”,$L$6,[@AccountSupport]=”Alexis Comer”, $L$1,[@AccountSupport]=”Maggie Rawls”,$L$2,[@AccountSupport]=”Melanie Pietrobono”,$L$3,[@AccountSupport]=”Michael Kaufman”,$L$4,[@AccountSupport]=”N/A”,$L$5,[@AccountSupport]=””,””)
Mynda Treacy
Hi Star,
Difficult to see the issue from your formula alone. Please post your question and sample Excel file on our forum where we can see the issue and help further.
Mynda
Jim Abbott
I built a spreadsheet using the IFS function and for the last 2 months it has worked without any problems. When I opened the same file today, 4/18/18, the “_xlfn.” appeared before all my old cells. I have tried repairing Office and updating to latest version, each to no avail. I am using Office 2016 and Excel version 1803 ( build 9126.2152 click-to-run). Any ideas or help would be appreciated.
Mynda Treacy
Hi Jim,
Are you running Office 365? Have you had Excel reinstalled? This error means the function is not supported in your version of Excel.
Mynda
Harry Blijenberg
I have the same problem… the IFS function worked well in Excel 2016 (as part of Office Pro 2016, not Office 365) until a week ago. Apparently Microsoft no longer provides this function, as the problem started after installing the latest Excel updates.
Mynda Treacy
On the contrary, Harry. The IFS function is new in Excel for Office 365 users. If you don’t see it in your version of Office 365 then I’d say it just needs updating. I have it in version 1805 build 9330.2014 Click-to-run.
Mynda
Harry Blijenberg
Hi Mynda,
It seems I didn’t explain it very well…
In December 2017 I bought (activation key, one time purchase) Office Professionnel Plus 2016 and a new laptop. I downloaded this Office version from the Microsoft website, installed it on the new laptop and activated it. I’ve never used an Office 365 subscription version.
About 2 months ago, I discovered the IFS function and tried it on my laptop and it worked just fine in Excel. That is… until about a week ago, when I opened one of the spreadsheets that uses this function and was confronted with #NAME? error. That’s why I think Microsoft has actually removed this function from the non-subscription version of Excel 2016 in one of most recent updates.
Luckily I had only replaced a few IF statements for IFS statements in my spreadsheets…
Harry
Catalin Bombea
Interesting.
Can you review your updates history, and remove the updates installed a week ago, one by one, until IFS works again?
It will be useful to know which update does that.
Cheers,
Catalin
David Blumberg
I have Excel 2010 and the expressions IFS or _XLFN.IFS do not appear when I type into a cell. I gather that is only available 2016. Please confirm>
David
Mynda Treacy
Hi David,
Correct, IFS is only available in Excel 2016 (the Office 365 version). If you receive a file containing an IFS formula and open it in an earlier version of Excel you will see the function is prefixed with _xlfn.
That’s not to say you can use the IFS Function in Excel 2010 by typing _xlfn.IFS
Kind regards,
Mynda
Col Delane
Hi Mynda
As you mentioned in your earlier blog about nested IFs, often it was the wrong function to use in the first place. I see the same occurring with IFS. For my mind, IFS is still the wrong function to use for a multiple option (i.e. which City of 6?) but single level (return corresponding State) decision tree like the example you’ve given. As Oz suggests, a lookup style function or CHOOSE would be better suited to this sort of application.
Whilst perhaps not best practice financial modelling, I sometimes nest 2-3 IFs, where there may be two further options if the first test = TRUE, and a completely different option if the first test = FALSE. I don’t see IFS eliminating the occasional need to nest such functions in order to cater for several levels of a decision tree.
Mynda Treacy
Hi Col,
Great to see you’re not one of the many who nest endless IF’s 🙂
Mynda
Oz du Soleil
You now my feeling about IFS.
LOL!
That final argument just isn’t intuitive. But you and I agree that too many conditions should lead a person to abandon IF or IFS and think about another solution, like a lookup range with VLOOKUP or INDEX/MATCH.
Mynda Treacy
🙂 that is the hope.