• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel IFS Function

You are here: Home / Excel Formulas / Excel IFS Function
October 18, 2016 by Mynda Treacy

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

Subscribe YouTube

Download the Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook from the video and follow along. Or Download the Excel Workbook from the written tutorial below. Note: these are.xlsx files please ensure your browser doesn't change the file extension on download.

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:

IFS function example

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.

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.
Category: Excel Formulas
Previous Post:Excel SWITCH Function
Next Post:Creating a Reference to PERSONAL.XLSB for User Defined Functions (UDF’s)creating a reference to personal.xlsb for user defined function udf

Reader Interactions

Comments

  1. Trish

    May 23, 2019 at 6:38 am

    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!

    Reply
    • Catalin Bombea

      May 23, 2019 at 1:21 pm

      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.

      Reply
  2. Star Gagnon

    May 24, 2018 at 11:00 pm

    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]=””,””)

    Reply
    • Mynda Treacy

      May 26, 2018 at 8:01 am

      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

      Reply
  3. Jim Abbott

    April 19, 2018 at 4:02 am

    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.

    Reply
    • Mynda Treacy

      April 19, 2018 at 2:33 pm

      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

      Reply
    • Harry Blijenberg

      May 18, 2018 at 3:42 am

      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.

      Reply
      • Mynda Treacy

        May 18, 2018 at 9:57 am

        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

        Reply
        • Harry Blijenberg

          May 19, 2018 at 9:24 am

          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

          Reply
          • Catalin Bombea

            May 19, 2018 at 3:29 pm

            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

  4. David Blumberg

    October 19, 2016 at 3:39 am

    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

    Reply
    • Mynda Treacy

      October 19, 2016 at 9:08 am

      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

      Reply
  5. Col Delane

    October 18, 2016 at 5:39 pm

    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.

    Reply
    • Mynda Treacy

      October 18, 2016 at 8:21 pm

      Hi Col,

      Great to see you’re not one of the many who nest endless IF’s 🙂

      Mynda

      Reply
  6. Oz du Soleil

    October 18, 2016 at 12:49 pm

    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.

    Reply
    • Mynda Treacy

      October 18, 2016 at 2:21 pm

      🙂 that is the hope.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x