Use the new Excel IFS function instead of nested IF

From this short tutorial you'll learn about the new IFS function and see how it simplifies writing nested IF in Excel. You'll also find its syntax and a couple of use cases with examples.

Nested IF in Excel is commonly used when you want to evaluate situations that have more than two possible outcomes. A command created by nested IF would resemble "IF(IF(IF()))". However this old method can be challenging and time consuming at times.

The Excel team has recently introduced the IFS function that is likely to become your new favorite one. Excel IFS function is available only in Excel 365, Excel 2021 and Excel 2019.

The Excel IFS function - description and syntax

The IFS function in Excel shows whether one or more conditions are observed and returns a value that meets the first TRUE condition. IFS is an alternative of Excel multiple IF statements and it is much easier to read in case of several conditions.

Here's how the function looks like:

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2]…)

It has 2 required and 2 optional arguments.

  • logical_test1 is the required argument. It's the condition that evaluates to TRUE or FALSE.
  • value_if_true1 is the second required argument that shows the result to be returned if logical_test1 evaluates to TRUE. It can be empty, if necessary.
  • logical_test2…logical_test127 is an optional condition that evaluates to TRUE or FALSE.
  • value_if_true2…value_if_true127 is an optional argument for the result to be returned if logical_testN evaluates to TRUE. Each value_if_trueN relates to a condition logical_testN. It can also be empty.

Excel IFS lets you evaluate up to 127 different conditions. If a logical_test argument doesn't have certain value_if_true, the function displays the message "You've entered too few arguments for this function". If a logical_test argument is evaluated and corresponds to a value other than TRUE or FALSE, IFS in Excel returns the #VALUE! error. With no TRUE conditions found, it shows #N/A.

The IFS function vs. nested IF in Excel with use cases

The benefit of using the new Excel IFS is that you can enter a series of conditions in a single function. Each condition is followed by the result that will be used if the condition is true making it straightforward to write and read the formula.

Let's say you want to get the discount according to the number of licenses the user already has. Using the IFS function, it will be something like this:

=IFS(B2>50, 40, B2>40, 35, B2>30, 30, B2>20, 20, B2>10, 15, B2>5, 5, TRUE, 0)
The example of writing IFS function in Excel

Here's how it looks with nested IF in Excel:

=IF(B2>50, 40, IF(B2>40, 35, IF(B2>30, 30, IF(B2>20, 20, IF(B2>10, 15, IF(B2>5, 5, 0))))))
The example of writing nested IF function in Excel

The IFS function below is easier to write and update than its Excel multiple IF equivalent.

=IFS(A2>=1024 * 1024 * 1024, TEXT(A2/(1024 * 1024 * 1024), "0.0") & " GB", A2>=1024 * 1024, TEXT(A2/(1024 * 1024), "0.0") & " Mb", A2>=1024, TEXT(A2/1024, "0.0") & " Kb", TRUE, TEXT(A2, "0") & " bytes")
Conversion using IFS

=IF(A2>=1024 * 1024 * 1024, TEXT(A2/(1024 * 1024 * 1024), "0.0") & " GB", IF(A2>=1024 * 1024, TEXT(A2/(1024 * 1024), "0.0") & " Mb", IF(A2>=1024, TEXT(A2/1024, "0.0") & " Kb", TEXT(A2, "0") & " bytes")))
Conversion using nested IF

50 comments

  1. =IF(A3=0,AVERAGE(A2:B2),IF(A4=0, Averge(A2:B3), AVERAGE(A2:B4)))

    A B
    1 2
    3 0
    0 0

    Result= #NAME?

    What is wrong in formula?

  2. I am trying to write formula that if the value $K$7="ABC", and value in L16 is less than 45, it should show amount 45. If value returned in L16 is greater that 45, it should return the value whatever is in L16

    AND

    Similarly if the value $K$7="XYZ", and value in L16 is less than 2, it should show amount 2. If value returned in L16 is greater that 2, it should return the value whatever is in L16.

    what formula can I use. Can't get my head around it, its driving me nuts.

    I am using below formula now but it is not giving correct figure. Tried IFS and CONCATENATE but didn't work for me either.

    =IF(AND($K$7="JNFX", L16<40), 45, L16)+IF(AND($K$7="SATCHEL", L16<2), 0, 0)+IF(AND($K$7="JNFX", L16<40), 0, 0)+ IF(AND($K$7="SATCHEL", L16<2), 2, L16)

    HELP!!!

  3. hi, i have a question, my formula as stated =IFS(F17>=5,F17/2,F1714,"7")
    if we work more than 5 year in the company, add'l 2.5 days entitle and add'l leave only up to 7 days , its mean if you work more than 14 year only can get up to 7 days additional leave.

    =IFS(F17>=5,F17/2,F1714,"7") is formula will show if work than 14 year it will give me the answer more than 7 days as the 1st logical test was put more than 5 year mean also included 14 year.. so how i need to modify my formula.

    thank in advance for your help!

    1. sorry amend my formula: =IFS(F2>=5,F2/2,F214,"7")

      1. why my formula keep changing with i copy ouT from my file.

        this is correct : IFS (F2>=5,F2/2,F214,"7")

        1. Hi! If I understand the problem correctly, the first condition in the formula includes the second condition.
          Therefore, either supplement the first condition or change the order in which the conditions are checked.

          =IFS(AND(F2>=5,F2<14),F2/2,F2>=14,7)
          =IFS(F2>=14,7,F2>=5,F2/2)

          1. thanks very much sir!

  4. I am working on a nested IF statement that looks at a start date and end dates, compares it to a column date and places a letter in the cell if that cell falls between the dates. I have read that the IF statements are not able to determine dates? How can I fix this in my nested if statement?
    Currently I have 8 nested if statements that read IF(start date >= cell date, end date<=cell date,"L"), if not than blank""
    The formula is working, but it is not right. wondering if the dates are not being read correctly. Any advice?

  5. I have a file with multiple tabs. I need to have information read from one tab to a roll up tab. The one tab has a question that can be answered YES, NO or N/A. The questions are answered by an X in the appropriate column. I need the answer to read to the roll up sheet as Yes or No or N/A depending on where the X was placed on the question in the tab.

    Tab 1
    Question Audits completed Yes No N/A
    X

    Roll up
    Audits Completed Yes

  6. Which formula need to used for calculating the commission for different seller vendor with different Rate complexity e.g.
    Vendor list....
    Vendor Name Sell Commission
    A 2000
    B 5000
    C 10000
    D 6000

    Vendor
    Name Percentage Flat Rate in Rs.
    A 10% OR 500 Whichever is low
    B 5%
    C 400
    D 15% 200 Whichever is low

      1. My query is how to calculate the commission on sale with provided commission table.

        1. If I understand correctly, you have a separate commission for each vendor. Write down 4 formulas.
          MIN(A1*10%, 500)
          A2*5%. ....
          etc.

          1. Hi Alexander,

            Need one single formula by which I can calculate all categories vendor transaction commission.

            Thank you for your support.

            Regards,
            Datta

  7. Trying to get this IFS statement to work. Seems simple enough but I seem to be missing something. I can get the result for the first statement or the third statement but not the second statement Middle). =IFS(K33L33,2,K33>L33,3)

    1. =IFS(L33<K33,3,"L33+5"K33,1)

      1. Hi!
        The expression "L33+5"K33 does not make sense. Perhaps L33+5=K33 ?
        Please read the above article carefully.

  8. Hello,

    I'm trying to get the following formula to work but this message keeps showing: "You've entered too few arguments for this function."

    The formula in question: *the excel software is programmed to be in french so SI.CONDITIONS translates to IFS*

    =SI.CONDITIONS(C20="Anglais 1"; 134-104; C20="Anglais 2"; 134-204; C20="Anglais 3"; 134-304; C20="Anglais 4"; 134-404; C20="Anglais 5"; 134-504; C20="Arts Plastiques 4"; 168-402; C20="Arts Plastiques 5"; 168-502; C20="Chimie 5"; 051-504; C20="Éducation Financière"; 102-522; C20="Espagnol"; 141-514;"0")

    I know in this article, it states that if that specific message appears it is because I never put a value_if_true. But I think I did, but now I'm not too sure. Is it because my values have a dash ?

    Please someone help me haha

      1. Hello,

        I tried using the double quotes as you suggested and the same message keeps appearing. Should I consider doing another formula ?

        1. IT WORKED !! I'm so happy haha Turns out that I had to eliminate the "0" value at the end and replace it with the end of my parenthesis. Thank you so much for your help !!

  9. Hi, I want to build a couple of formulas for current and previous month scores based on a table of data. Basically, if today's date is in the month of x (shown in column A), then display a specific score from column B.

    In A2:A13, I have the names of the month's of the year.
    In B2:B13, I have scores that are totals for that given month.

    In D1, I want to display the current month's score based on today's date.
    In D2, I want to display the previous month's score based on today's date.

    I know it involved an IFS statement using TODAY(), and the appropriate cell in column B not being blank, and I'm at a loss on how to put it all together.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =SUMIF(A2:A13,TEXT(TODAY(),"mmmm"),B2:B13)

      Hope this is what you need.

      1. Thanks Alexander, very close.

        I'm looking to show the specific score for the corresponding month, not the sum of the scores.

        So if A11's value is October, and Todays date is October 31st, show the value in B11

        1. Hello!
          Have you tried using a formula? If your data contains only one cell "October", then you will get the score for October.
          You can also use the VLOOKUP function to find the value:

          =VLOOKUP(TEXT(TODAY(),"mmmm"),A2:B13,2,FALSE)

  10. Hi, I hope you can help me. I am looking for a formula that will calculate the date range between cells, (NETWORKDAYS, no holidays), where the cells used in the date range will depend if the are populated.

    The calculation needs to consider the following:

    C1 is the end date, and the start date is either B1 or A1.

    B1 is the first start date to look at. If B1 is populated, then calculate the "networkdays" between B1 & C1.

    If B1 is blank, then calculate the "networkdays" between A1 & C1.

    If C1 is blank, the cell should be blank, even if A1 and B1 are populated.

    This is what I have, and it works until C1 is blank:

    =IF(B1="",NETWORKDAYS(A1,C1),NETWORKDAYS(B1,C1)))

    1. Hello!
      If I understand correctly, add another nested condition to the IF formula:

      =IF(C1<>"",IF(B1="",NETWORKDAYS(A1,C1),NETWORKDAYS(B1,C1)),"")

      1. Thank you very much, Alexander, it works (with the addition one an extra closed parenthesis).

  11. Hi, I'm trying to write formula that will return "BH" if cell contains "Brighton" and "LDN" if cell contains "London".
    It woks only for cells wit "Brighton", but not cells with London and I'm getting #VALUE! error.

    =IFS(SEARCH("Brighton",C48691),"BH",SEARCH("London",C48691),"LDN")

    Thank you.
    Aga

      1. Thank you. I tried your formula, but now I'm getting #N/A error in both cases

          1. Hi!

            I didn't notice it. Thank you very much for your help. That's great.

  12. Thank you for taking the time to read this.

    I would like to use IF to update a value in another field (that currently has a value). I am not sure how to enter just the current time.
    =if(c2="x",b1=Now)

    Ideally, someone makes c2 = x and then this happens. I don't know how to run a formula in the current field without overwriting the formula.

    Thanks

    1. Hello!
      If a value is written in a cell, then it is impossible to change it using a standard Excel formula. You need to use a VBA macro.

  13. Hi, I need Excel function for Calculating a certain % of Discount basis number of days from a date:
    If Cut off Date is Greater than the Month indicated then 0%; If cut off date is Lower than the month indicated then return Value as per discount.
    Cut off Date 1Apr 1May 1Jun 1Jul 1Aug 1Sep 1Oct 1Nov 1Dec 1Jan 1Feb 1Mar
    A - 1st Feb 2020
    B - 15th March 2020
    C - 1st September 2020
    D - 15th October 2020

    1. Hello!
      If I understand your task correctly, use something like this

      =IFS(A1>DATE(2021,3,1),1,A1>DATE(2021,1,1),2,A1>DATE(2020,12,1),3,A1>DATE(2020,11,1),4,A1>DATE(2020,10,1),5)

      In the first condition, use the farthest date.

  14. with nested IF(), latter redundant calculations are ignored (not calculated)

    =IF(1=1,"Answer","this bit could be processor heavy but is ignored")

    but using the 'evaluate formula' it would appear that IFS() works out all the bits of the formula, even if the first condition is met and therefore is redundant

    =IFS(1=1,"answer", other condition IS verified, redundant terms all calculated)

    Is this the case?

    essentially, IFS does indeed look simpler to untangle, BUT is it actually getting excel to do a less efficient amount of calculating?

    1. Mike you are correct. It appears as though IFS is horribly inefficient compared to traditional nested IFS statements. Much easier to write but you end up sitting around longer waiting on calculations to finish.

    2. Hello, Mike,

      We haven’t investigated the efficiency of the IF() and IFS() functions in all configurations.
      Most likely, they work in the same way. For example, if you create two formulas like the ones below:
      =IF(1=1,"1",IF("=1","2"))
      =IFS(1=1,"1", "=1", "2")
      They return the same result equal to «1».
      But following your hypothesis, the =IFS(1=1,"1", "=1", "2") formula should have returned a #VALUE! error as the second condition in the formulas is wrong.

  15. hi
    why in my excel(2010 version) "IFS" function does not work??
    what should I do??

    1. Google pwrIFS as an option.

    1. self-reply: same issue affects all three URL in the "You may also be interested" section.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)