Comments on: Excel Nested IF statement: examples, best practices and alternatives

Though very popular, the nested IF statement is not the only way to check multiple conditions in Excel. In this tutorial, you will find a handful of alternatives that are definitely worth exploring. Continue reading →

Comments page 2. Total comments: 122

  1. i am trying to add to the if(and function to display information from other cells after it is through as shown below
    column info
    A-their first name
    B-their last name
    D their sex "M" or "F"
    E- client or staff

    =IF(E3="staff", "Dr.", IF(AND(E3="client", D3="M"), "Mr.", IF(AND(E3="client", D3="F"), "Ms.",)))

    I am trying to get the above formula to display (Dr./Mr./Ms.) (First Name from column A) (Last Name from column B) in one cell "Dr. Gregory House"

    1. Hello!
      Please try the following formula:

      =IF(E3="staff","Dr.",IF(AND(E3="client",D3="M"),"Mr.",IF(AND(E3="client",D3="F"),"Ms.",)))&A3&" "&B3

      Hope this is what you need.

  2. Hello,
    I am attempting and if and or statement, but having trouble getting to the correct result. part I
    If(AJ11="US","TRUE",IF(AJ11="",IF(AG11="US","TRUE",IF(AG11="",IF(AI11="US","TRUE","review"))))), returning false instead of True or review.
    there are 3 col with data points, if no US or blank = true, if any us, blank and non us, test

    if result is to test, the 2 part is to test for 2 letter code to either test or not test.

    suggestions?

  3. Hi there,

    I am struggling with my formula I'd love some help:
    I have 8 data values validated in cell I2, and will be entering a manual number in cell J2 and believe I need an IF formula in column K to easily calculate the following scenario.

    If the frequency = weekly (cell I2), and the number of hours =x, then the monthly hours when annualised would be (J2*52)/12).

    The other values in I2 are:
    Fortnightly
    2 x Weekly (needs annualising then multiplying by 2)
    3 x Weekly (needs annualising then multiplying by 3)
    4 x Weekly (needs annualising then multiplying by 4)
    5 x Weekly (needs annualising then multiplying by 5)
    6 x Weekly (needs annualising then multiplying by 6)
    7 x Weekly (needs annualising then multiplying by 7)
    One-Off (does not need annualising)

    The current formula I have is:

    =IF(I2="Weekly",((J2*52)/12),IF(I2="Fortnightly",((J2*26)/12),IF(I2="3 x Weekly",((J2*52)/12)*3),IF(I2="2 x Weekly",((J2*52)/12)*2""""))))

    It worked until I put the last IF combination in

    I'd really appreciate some help. Thank-you :-)

    1. Hello!
      Please check the formula below, it should work for you:

      =IF(I2="Weekly",J2*52/12,IF(I2="Fortnightly",J2*26/12,IF(I2="3 x Weekly",J2*52/12*3,IF(I2="2 x Weekly",J2*52/12*2,""))))

      1. That worked. Thanks very much!

  4. Hello,

    =IF(D5>75,16,IF(D5>70,15,IF(D5>65,14,IF(D5>60,13,IF(D5>55,12,IF(D5>50,11,IF(D5>45,10,IF(D5>40,9,IF(D5>35,8,IF(D5>30,7,IF(D5>25,6,IF(D5>20,5,IF(D5>15,4,IF(D5>10,3,IF(D5>5,2,IF(D5<=5,1))))))))))))))))

    Using above formula to show 1 manager for every 5 employees - (ie. 6-10 employees requires 2 managers, 11-15 employees would require 3 managers, 16-20 employees would require 4 managers)

    Is there a more efficient/condensed way to accomplish this?

  5. "IF(R10>1,Q10*5,IF(R10<0,Q10*7))" Is tje formula is right or contains any error, because i am getting only Q*5 result where it should be Q*7,

  6. Need help to have text & number sequencing based on a drop down list value (to confirm document type and number)

    Column C lists document type from a 3-choice drop down list "Policy","Standard", or "Other"
    Columns E: G contain sequential values for each of the document types.
    Column E "Policy" list of sequential values 0001-PL (continues as 0002-PL, etc. starting in row 2)
    Column F "Standard" list of sequential values 0001-ST (continues as 0002-ST, etc. starting in row 2)
    Column G "Other" list of sequential values 0001-OT (continues as 0002-OT, etc. starting in row 2)

    Since Column C would have a list that is not sequential, how can I ensure column D sequences document number correctly?
    User enters in Column C Row 2 "Policy", Column D will populate as "0001-PL"
    User enters Column C Row 3 "Standard", Column D populates as "0001-ST"
    User enters Column C Row 4 "Policy", Column D populates as "0002-PL"

    How can I make sure that when someone enters one of the three document types, column D picks up last sequence of "xxx-PL"?

  7. How do I combine =IFERROR(AVERAGE(E6:E8),"") with =AVERAGEIF(E6:E8,"0")

    1. Hi,
      The formula AVERAGEIF (E6: E8, ”0 ″) and AVERAGEIF (E6: E8,” ″) means that you are calculating the average over blank and text cells. It doesn't make sense as it will result in an error. Explain what you want to calculate.

  8. SOMEONE PLEASE HELP:
    So I am working on jasperactive, one of the projects has me come up with a function that will display Two different texts and if it doesn't match either, it will display a blank
    -the original function is: IF(AND(G2>F2,F2>E2),"Growing energy source","")
    -Then it asks for you to add in a function that will display "Shrinking energy source" is G2<F2 and F2<E2
    -i have been trying to make a nested formula for it that will work for hours with no luck. if anyone can help that would be amazing

    1. Hi,
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question

      =IF(AND(G2 > F2,F2 > E2),"Growing energy source", IF(AND(G2 < F2,F2 < E2),"Shrinking energy source",""))

  9. =IF(G17>1.63,12CFW, IF(G17>1.3,10CFW,IF(G17>0.978,8CFW,IF(G17>0,6CFW,""))))

    excel says there is a problem with this formula? help would be much appreciated, thank you.

    1. Hi,
      Text values in formulas must be enclosed in quotation marks.

      =IF(G17>1.63,"12CFW", IF(G17>1.3,"10CFW",IF(G17>0.978,"8CFW",IF(G17>0,"6CFW",""))))

      I hope it’ll be helpful.

  10. Could someone help me to figure out one If formula that involve multiple conditions.

    For example, I have 7 different project code name in G2, and G3 is associated project name.
    and when I clicked project code in G2, and G3 will auto pop out the right project name.
    I know how to compare within two code. but not sure how to do with multiple different ones.
    Below is an example that i made it up for comparing two projects.

    =IF(TASK_CODE="123456-789","Bill","Cheques")

    Thank you so much

    1. Hello!
      I recommend using the VLOOKUP function to select the desired value.
      Please check out the following article on our blog, it’ll be sure to help you with your task: How to do Vlookup in Excel
      I hope I answered your question. If something is still unclear, please feel free to ask.

  11. Hello- I am trying put a formula together for the below conditions

    For Example
    IF C2=7.5 Then it should RANK 5 similarly IF C2=8.8 Then it should RANK 4

    Below Scale for Each RANK
    >11 = RANK 1
    <10 - 9- 8-<=9 = RANK 4
    <8 = RANK 5

    Please help.

  12. Hi, I'm hoping that I'm not too far off the mark in this area :-).

    I have a register of risks where I want to flag as overdue. A critical must be attended to within 7 days, High 14, Med 30 and Low 60. My stab at it as below fails with an error. Any ideas please?

    X and F contain the age and severity data.

    =
    IF(AND(X10="Critical",F10<7),OK,
    IF(AND(X10="High",F10<14),OK,
    IF(AND(X10="Medium",F1060),OK,
    Overdue))))

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =IF(AND(X10="Critical",F10<7),"OK", IF(AND(X10="High",F10<14),"OK", IF(AND(X10="Medium",F10<60),"OK", "Overdue")))

      I hope my advice will help you solve your task.

  13. i am looking fro a formula for below problem.

    We have 3 cells a1,b1&c1.
    in cells d1-->if out of 3 cells only single cell contain value >0 then it will show Ok otherwise not ok.

    FOr EX:
    a b c d
    0 0 0 OK
    1 0 0 OK
    1 1 0 not ok
    0 1 0 ok

    1. Hello!
      The formula below will do the trick for you:

      =IF(COUNTIF(A1:C1,">0")=1,"OK","Not OK")

      Hope this is what you need.

  14. Hi
    I am trying to put a formula together and cant seem to get the nesting correct. What i am trying to do is get a concatenate value if the IF and AND are true based on another cell.
    My data:
    cell A1= Qtr 1
    cell J10 = Jan
    cell K10 = Feb
    cell L10 = Mar
    cell M10 = April
    cell N10= May
    cell L10= June
    cell F10 = project name
    cell G10 = project number
    What I am after is a formula that will look at cell A1 and if its equal to Qtr 1, and if cells J10(Jan), K10 (Feb) and L10 (Mar) are blank, then i want it to return blank, otherwise concatenate the project name and project number. But if A1= Qtr 2, and if M10, N10 and L10 are blank then return blank,otherwise concatenate project name and project number. And so on for quarters 3 and 4. Is this possible or is there a simpler way to do this?

    Appreciate your assistance.

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

      =IF(AND(A1="Qtr 1",J10="",K10="",L10=""),"", IF(AND(A1="Qtr 2",M10="",N10="",O10=""),"", IF(AND(A1="Qtr 3",P10="",Q10="",R10=""),"", IF(AND(A1="Qtr 4",S10="",T10="",U10=""),"",F10&G10 ) ) ) )

      I hope this will help

  15. Hello Alexander Trifuntov
    I was hoping you could help with my excel function have being having trouble with.
    Am trying to archive below multiple query.
    =IF(AND (A4 = "WEEK 1"(OR( D9=({"E", "EOC1", "EOC2", "L"})) & ( D10=({"E", "EOC1", "EOC2", "L"})), "WORKING", "NOT WORKING"))).
    I can also shortened it by saying----
    =IF(AND (A4 = "WEEK 1"(OR( D9=({"E*","L"})) & ( D10=({"E*","L"})), "WORKING", "NOT WORKING"))) using a wildcard for the E's.
    so if both cells contain the range values is TRUE else FALSE.
    The formula work for single cell like this
    =IF(OR(D9=({"E";"L";"EOC1";"EOC2"})),"WORKING","NOT-WORKING" ) but am having trouble with 2 cells and concatenating it.

    Thanks in advance

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

      =IF(AND(A4="WEEK 1", OR(D9={"E","EOC1","EOC2","L"}, D10={"E","EOC1","EOC2","L"})), "WORKING","NOT WORKING")

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      1. Hi Alex
        That work perfectly for me. Thank you so much, i really appreciate your quick response.

        Many Thanks

  16. E4 is either blank or contains a date
    I'm trying to get A4 to: 1) to be blank if E4 is blank or 2) place an X in A4 if e4=<today().
    Everything I have tried fails to produce the desired results. I have tried choose functions, if and nested ifs but nothing I'm doing is working.
    Thanks for any help.

    1. Hello Ray!
      If I understand your task correctly, please try the following formula:

      =IF(E4=TODAY(),"x", IF(E4="","","not today"))

      I hope it’ll be helpful.

  17. I need a formula if A1 is greater the or equal to 15 December 2019 or lesser then 14 March 2020 and if A2 says listed then say Yes if not say no or if A1 is greater then or equal to 15 March 2020 then say Yes.

    1. Hello Bharath!
      If I understand your task correctly, please try the following formula:

      =IF(AND(A1 > = DATE(2019,12,15),A1 < = DATE(2020,3,14),A2="listed"),"YES",IF(A1 > = DATE(2020,3,15),"YES","NO"))

      I hope it’ll be helpful.

  18. 1)If= first date of period all floor commission 2%
    2)If=second date of period
    lower floor commission 3% ,
    middle floor commission 3.5%
    Higher floor commission 4%
    3)If = third date of period
    Lower floor commission 2%
    Middle floor commission 2.5%
    Higher floor commission 3%

    *(Date of periods
    (1) 29-09-2019 to 02-02-2020
    (2) 03-02-2020 to 29-02-2020
    (3) 01-03-2020 to 30-03-2021)*

    Can you please help any formula to get correct commission %

    1. Please help it's very urgent....

  19. Please help me!!!

    I want to use an IF Command, but I want it in such a way that it fetches another IF Statement from a totally different Cell...

    How do I do it?

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

      =IF(C1,1,0)

      in cell C1 write down the formula

      =IF(A1>0,TRUE,FALSE)

      I hope it’ll be helpful.

  20. I have a table with values, no text. The data look something like this
    0.00 0.51 1.01 0.00 1.43
    0.82 2.48 5.40 1.96 7.75
    0.39 0.00 0.00 0.93 0.00

    I need to present these values in four categories:
    0 [presenting as 0]
    >0 and <0.05 [presenting as =0.05 and =1 [presenting as the actual value]

    I have not found a way for IFS to test against a range of values within a single unit of the function, as the old AND function used to enable in IF statements. Is this possible?

    1. A chunk of the four categories got erased. Here are the categories, again:
      0 [presenting as 0]
      >0 and <0.05 [presenting as 0.04999 and 0.99999 [presenting as the actual value]

  21. I can’t figure out how to write this...the cell that I’m evaluating is a %
    The formula I’ve tried: IF(B17≥65,"THRIVING",IF(B17≥50,"Ahead Of The Curve",IF(B17>35,"TURBULANT","Making Ends Meet")))
    Criteria: 66+% = Thriving,
    51-65% = Ahead Of The Curve,
    36-50% = Making Ends Meet,
    0-35% = Turbulent
    One cell I’m evaluating has a value of 45%, another of 88%...the formula says both are Making Ends Meet
    Can you help me?

    1. HI Donna,
      I thing this will you.
      IF(A11>=66%,"THRIVING","")&IF(A11<=65%,"Ahead Of The Curve","")&IF(A11<=50%,"Making Ends Meet","")&IF(A11<=35%,"TURBULENT","")

  22. I need a formula for excel -
    If (Salary<=13000) Then 9617 Else 12022 Elseif(Salary<=24000) Then 15100 ElseiF(salary<=30000) Then (Basic*0.45)

    Help me

    1. Sub Value()
      Dim Salary As Integer
      Dim Basic As Double
      Basic = Cells(?, "?")
      Salary = Cells(?, "?")
      If Salary < 13001 Then
      Cells(?, "?") = 9617
      ElseIf Salary < 24001 Then
      Cells(?, "?") = 15100
      ElseIf Salary < 30001 Then
      Cells(?, "?") = Basic * 0.45
      Else
      Cells(?, "?") = 12022
      End If
      End Sub

  23. '=IF(AND(F9>0,F9=11,F9<=15),30,IF(F9<15,25,0)))

    1. =IF(AND(F9>0,F9=11,F9<=15),30,IF(F9<15,25,0)))

  24. I run a badminton booking spreadsheet with 7 named players (as column headings in row 2) and I want to identify the first 4 people who have said "YES" (in row 3), working from the left. I have nested IFs, but I want to stop trying after I have achieved the 4th YES (because you can only get 4 player on a badminton court!).
    This:
    =CONCATENATE("This week it's ",
    IF(C3="YES","me, ",""),
    IF(D3="YES",$D$2,""),IF(D3="YES",", ",""),
    IF(G3="YES",$G$2,""),IF(G3="YES",", ",""),
    IF(I3="YES",$I$2,""),IF(I3="YES",", ",""),
    IF(J3="YES",$J$2,""),IF(J3="YES",", ",""),
    IF(K3="YES",$K$2,""),IF(K3="YES",", ",""),
    IF(L3="YES",$L$2,""))
    gives
    "This week it's me, Roger, Sanath, Agnelo, Greg, Alec"
    which is 6 names because José in column D had said "NO".
    I would like the result to read
    "This week it's me, Roger, Sanath, Agnelo".
    I think the more gramatically correct
    "This week it's me, Roger, Sanath and Agnelo"
    might be too much of a challenge!
    Any ideas please?

    1. Hi Guy,
      What if all the players reply in the affirmative? Will it be possIble to pick only four of them for a game without upsetting the others? If random selection sounds good to you, I can suggest applying a formula that will bring the names of those who want to take part (Step 1) and turning the values received into a 'Custom list' to delegate Ablebits' 'Random Generator' to take an unbiased decision (Step 2). If you like the idea, this is the formula which is needed in Step 1:

      =IF($3:$3="YES", CHOOSE(1, $2:$2), "")

  25. I need help in defining the reorder level using "IF" or any other formula in excel
    Column A = Shortage = 18500
    Column B = MOQ = 5000
    Column C = Reorder level = ?
    I would like to calculate the reorder level as follows :-
    (ie. Reorder level should be = to MOQ if shortage is less than MOQ
    OR Reorder level should be 20000 if shortage is between 15001 & 20000
    ie. Reorder level should be in multiples of MOQ but > shortage
    Kindly confirm how to use "IF" formula or any other formula in excel

    1. Tony,
      You do not need an IF function for this.
      Try:
      =CEILING(shortage, 5000)

      This will round your shortage number up to the next 5000.

      K

      1. Thanks a lot. it did work

  26. Error in your explanation of the SWITCH function. Line 1, you use SWIFT. I expect you mean SWITCH. #yourewelcome

    1. Of course, I meant SWITCH. Fixed, thank you!

  27. Not working. Please help please. I need to use 16 conditions for if statement but as you see, it is only 10 and not working. It said that "this formula uses more levels of nesting than you can use in the current file format". Anyone please.

    Thanks!

    =IF(S10>=95,20, IF(S10=94,19, if(s10=93,18, if(s10=92,17, if(s10=91,16, if(s10=90,15, if(s10=89,14, if(s10=88,13, if(s10=87,12, if(s10=86,11))))))))))

    1. I know this is kind of late, but try this:

      =IF(S10>=95,20, IF(and(S1085),s10-75,))

      1. For some reason that did not format correctly. I will try it one more time:

        =IF(S10>=95,20,IF(and(S10 85),s10-75,))

  28. Can I use nested IF to do the following :
    I have a column with three possibilities entered in the cells : ABC DEF GHI

    the next column in the contagious cell needs a number based on the above so that :
    ABC would be 123
    DEF would be 456
    GHI would be 789

  29. I'm having trouble with this formula. I have three conditions and they need to all be true to return "TRUE". If any are not true, it should return "FALSE". Here's what I've tried now, which does not work.
    =IFS(C:C="CSD_SERVICES", "TRUE", (K:K=0,"TRUE", (O:O="","TRUE","FALSE")))
    I've tried probably 25 different iterations but so far none returns the correct answer.

    1. Hi,
      It seems to me that any of the two formulas below can help you with your task:

      =IF(AND(C:C="CSD_SERVICES", K:K=0, O:O=""), "TRUE", "FALSE")

      =IFS(AND(C:C="CSD_SERVICES", K:K=0, O:O=""), "TRUE", TRUE, "FALSE")

      Please note that the necessity of one more 'TRUE' in the second formula is dictated by the syntax that the Excel IFS function has. Please press 'F1' if you feel like looking into it.

  30. Dear Sir,
    Kindly confirm one coloum are value 1-15, and secound Coloum value 15-30, if kindly confirm which sort out the value's of Below & uper.
    & kindly confirm vlookup formulas fungtions.

    1. Thanks for the reply.

      When a name is selected from H67,
      The result in H68 should be 000234-UMPT.

    2. I created a drop box of names in cell H67. I wanted my nested if statement to return some numbers and alphabets when a name is selected but it's not working.

      Eg.
      =IF(H67="M.ISAAC","000234-UMTP",IF(H67="D.MICHAEL","000678-UMPT",""))

      The statement is not working. What am I doing wrong?

      Do you offer classes online?

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