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?
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
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?
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
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.
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
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"
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
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
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!
Using Excel 2013
"Short answer yes with an if, long answer no with a but."
-Reverend Lovejoy, The Simpsons
Like this thread? Share it with others