Nested IF function not working

[otisvillain]

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

 

[Special-K99]

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?

 

[bbott]

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

 

[CharlesChuckieCharles]

surely its just

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

and copy down to row 7000

 

[otisvillain]

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

 

[otisvillain]

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"

 

[otisvillain]

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

 

[bbott]

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","")

 

[otisvillain]

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!

 

[bbott]

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.