Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Nested IF function not working
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2015
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Nested IF function not working

    Hey all,

    I've tried my nested IF function about 10 different ways and it just isn't working. My data set is related to columns B (Insurance Plan Name) & C (Finance Class). My first IF function, only using 1 argument, works fine and is as follows:

    =IF($B$1:$B$7000="EMPIRE BCBS BLUE CARD PROGRAM", "COMMERCIAL",$B$1:$B$7000)

    For my nested IF function, I want the formula to evaluate the same data set, but also include "HIGHMARK BCBS" in the search. This is how I wrote the nested function, which is not working:

    =IF($B$1:$B$7000=EMPIRE BCBS BLUE CARD PROGRAM,"COMMERCIAL",IF($B$1:$B$7000=HIGHMARK BCBS,"COMMERCIAL"))

    Any suggestions?

    Thank you

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,164
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Nested IF function not working

    Are you sure your first formula works?
    It seems to return #VALUE or the first element in the range (B1) if it's an array formula.

    What results are you expecting to get?

  3. #3
    Board Regular
    Join Date
    Feb 2010
    Location
    Wisconsin
    Posts
    2,350
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Nested IF function not working

    Maybe you're looking for something like this that can be copied down?

    =IF(OR(B1={"EMPIRE BCBS BLUE CARD PROGRAM","HIGHMARK BCS"}),"COMMERCIAL",B1)

    Unless you're entering your initial formulas as array formulas???
    Using Excel 2013

    "Short answer yes with an if, long answer no with a but."
    -Reverend Lovejoy, The Simpsons

  4. #4
    Board Regular
    Join Date
    May 2011
    Location
    London
    Posts
    2,153
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Nested IF function not working

    surely its just

    =IF(OR($B1="EMPIRE BCBS BLUE CARD PROGRAM",$B1="HIGHMARK BCBS"),"COMMERCIAL",$B1)

    and copy down to row 7000
    Always Test on a copy ALWAYS

    No warranty or guarantee is explicitly or implicitly implied

    You accept and use any code or solution at your own risk

    If you are given a solution beyond your current skill level use with an appropriate level of caution and if using at your place of work it is your responsibilty to inform others of any support or understanding shortfall due to the source of the solution.

    lack of responses on this site is often down to a lack of detail in the question.

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Nested IF function not working

    Quote Originally Posted by bbott View Post
    Maybe you're looking for something like this that can be copied down?

    =IF(OR(B1={"EMPIRE BCBS BLUE CARD PROGRAM","HIGHMARK BCS"}),"COMMERCIAL",B1)

    Unless you're entering your initial formulas as array formulas???
    Thanks for the reply. What I'm trying to do is change a value in column C to "COMMERCIAL", only if a cell in column B equals either "EMPIRE BCBS BLUE CARD PROGRAM" or "HIGHMARK BCBS".When I use this formula, and paste it down (I'm in column C) , it is changing all cell values in column C to whatever parallel value is in column B. Any thoughts?

    Thanks

  6. #6
    Board Regular
    Join Date
    Mar 2015
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Nested IF function not working

    Thanks for the reply. Yeah, the first formula is working good. I'm looking to change a value in column C, only if the parallel value in column B is equal to "EMPIRE BCBS BLUE CARD PROGRAM" or "HIGHMARK BCBS"

  7. #7
    Board Regular
    Join Date
    Mar 2015
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Nested IF function not working

    Quote Originally Posted by CharlesChuckieCharles View Post
    surely its just

    =IF(OR($B1="EMPIRE BCBS BLUE CARD PROGRAM",$B1="HIGHMARK BCBS"),"COMMERCIAL",$B1)

    and copy down to row 7000

    Thanks for the reply. I tried your formula also, but it's changing all values in column C to the parallel value in column B. What I'm trying to do is change a value in column C to "COMMERCIAL", only if a cell in column B equals either "EMPIRE BCBS BLUE CARD PROGRAM" or "HIGHMARK BCBS". When I paste it down (I'm in column C) , it is changing all cell values in column C to whatever parallel value is in column B. Any recommendations?

    Thank you sir

  8. #8
    Board Regular
    Join Date
    Feb 2010
    Location
    Wisconsin
    Posts
    2,350
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Nested IF function not working

    Quote Originally Posted by otisvillain View Post
    Thanks for the reply. What I'm trying to do is change a value in column C to "COMMERCIAL", only if a cell in column B equals either "EMPIRE BCBS BLUE CARD PROGRAM" or "HIGHMARK BCBS".When I use this formula, and paste it down (I'm in column C) , it is changing all cell values in column C to whatever parallel value is in column B. Any thoughts?

    Thanks
    That means that there are not matches for either of your of your two criteria in that row (i.e. B1 does not contain "EMPIRE BCS..." nor "HIGHMARK BCS", so C1 returns B1, not "COMMERCIAL").

    If you just want it to be blank when no match is found, change it to:

    =IF(OR(B1={"EMPIRE BCBS BLUE CARD PROGRAM","HIGHMARK BCS"}),"COMMERCIAL","")
    Using Excel 2013

    "Short answer yes with an if, long answer no with a but."
    -Reverend Lovejoy, The Simpsons

  9. #9
    Board Regular
    Join Date
    Mar 2015
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Nested IF function not working

    Quote Originally Posted by bbott View Post
    That means that there are not matches for either of your of your two criteria in that row (i.e. B1 does not contain "EMPIRE BCS..." nor "HIGHMARK BCS", so C1 returns B1, not "COMMERCIAL").

    If you just want it to be blank when no match is found, change it to:

    =IF(OR(B1={"EMPIRE BCBS BLUE CARD PROGRAM","HIGHMARK BCS"}),"COMMERCIAL","")
    Sweet, it is working. Real quickly, column C has values that I want to remain the same, if column B does not match my argument. In the blank quotes at the end, what would I put in there to keep the value of column C the same if it's not a match? I tried putting in "C1", but it's changing the value actually to C1, not the value of C1.

    Thanks!

  10. #10
    Board Regular
    Join Date
    Feb 2010
    Location
    Wisconsin
    Posts
    2,350
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Nested IF function not working

    Quote Originally Posted by otisvillain View Post
    Sweet, it is working. Real quickly, column C has values that I want to remain the same, if column B does not match my argument. In the blank quotes at the end, what would I put in there to keep the value of column C the same if it's not a match? I tried putting in "C1", but it's changing the value actually to C1, not the value of C1.

    Thanks!
    If you're entering the formula in C1 it will overwrite the value of C1. A cell can contain text/numbers or a formula, but not both. If you actually want to replace the value of C1 with "COMMERCIAL" when B1 contains one of your criteria, you would need to use VBA.
    Using Excel 2013

    "Short answer yes with an if, long answer no with a but."
    -Reverend Lovejoy, The Simpsons

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •