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

Table of contents

70 comments

  1. The IFS function is actually sometimes worse than even some of the posters point out. I wrote a recursive function that called itself within an IFS statement (if certain boundary conditions such as out of range etc. were met, exit gracefully, but in the base case, recursively call the Lambda function). I wracked my brains for a long time trying to figure out why I continued to get a #Value error, strongly suspecting that I was running into an infinite loop. Then I read a post that IFS evaluates ALL conditions even if it only executes the first TRUE condition, and rewrote the IFS to include a nested IF only for the recursive call. The program worked fine. Microsoft seems to have improperly documented this but it is likely a common way to get hung up in recursion.

  2. Can you please help to write if (and nested formula for following conditions?

    IF TAXABLE INCOME IS OVER: BUT NOT OVER: THE TAX IS:
    $0 $23,200 10% of taxable income
    $23,200 $94,300 $2,320 + 12% of the amount over $23,200
    $94,300 $201,050 $10,852 + 22% of the amount over $94,300
    $201,050 $383,900 $34,337+ 24% of the amount over $201,050
    $383,900 $487,450 $78,221 + 32% of the amount over $383,900
    $487,450 $731,200 $111,357 + 35% of the amount over $487,450
    $731,200 No limit $196,669 + 37 % of the amount over $731,200

  3. =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?

  4. 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!!!

  5. 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!

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

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

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

        • 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)

          • thanks very much sir!

  6. Am trying to find the best formula to spread a quantity (total sales) between 2 dates with a prorated value per year in between.
    I suppose I need a combinations of IFS. So far, I could only do this manually. But this would get extremely time-consuming if I had 200+ rows.
    My focus is which IFS formula can help me calculate the values from H9:M17 ?

    • Your task is not completely clear to me. Please clarify your specific problem or provide additional information to understand what you need. Provide me with an example of the source data and the expected result.

  7. I am trying to write the following formula:

    =IFS(J19="Monday",B15,J19="Tuesday",C15,J19="Wednesday",D15,J19="Thursday",E15,J19="Friday",F15)

    The formula works if I type the day in cell J19, but J19 contains a formula which returns the specific day, which the above formula does not seem to read?

  8. 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?

  9. IFS(), as it is presently implemented in Excel, has a significant drawback. IFS(logical1,expr1,logical2,expr2,logical3,expr3,TRUE, default expr ) always evaluates all of the logicals and all of the expressions. The corresponding nested IF(logical1,expr1,IF(logical2,expr2,IF(logical3,expr3,default expr ) ) ) will always evaluate logical1, but will only evaluate expr1 when logical1 is true, and will only evaluate logical2, when logical 1 is false, etc.

    IFS() does not return errors from logical tests that would never be reached by the nested IF() form, and does not return errors from expressions that would never be evaluated by the nested IF() form.

    IFS() will perform time consuming operations like table lookups and data queries that would never be reached by the nested IF() form.
    IFS() will return #NA when none of the logical expressions was true. When logical1, logical2, and logical3 are all false.,
    IFS(logical1,expr1,logical2,expr2,logical3,expr3) returns #NA
    IF(logical1,expr1,IF(logical2,expr2,IF(logical3,expr3) ) ) returns FALSE

    I'm sure the rational for choosing the present implementation was along the lines of "it corresponds to evaluating a vector expression", or "it corresponds to modern CPU architectures that perform speculative execution", "it allows for parallel execution on multiple cores". That these rationales also consume more CPU cycles and drive new hardware purchases that require a new OS license and a new Office license is an example of (un) natural selection. None of the rationales is valid. Speculative evaluation of nested IF() functions could also be scheduled on multiple cores for parallel execution.

    Until Microsoft rewrites the IFS() function, using ctrl-enter to write

    =IF(logical1,expr1,
    IF(logical2,expr2,
    IF(logical3,expr3,
    default )))

    has enough advantages over

    =IFS(logical1,expr1,
    logical2,expr2,
    logical3,expr3,
    TRUE. default )

    to continue using nested IF() instead of IFS()

    • THANK YOU FOR THIS COMMENT!

      I have been losing my mind trying to figure out why my IFS functions are evaluating all the conditions when nested IFS do not. This is an incredible oversight and has major performance issues for people with large or complex tables/worksheets (my particular case uses UDFs with nested IFS for error handling).

      It really boggles the mind why they would make IFS function without the short-circuit ability built into the IF statement.

  10. 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

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)