code for determining error in s/sheet

[pdeshazier]

I have a spreadsheet which contains 3 columns, such as:

COL C COL D COL E
ACI MEDICARE PART B UHCC
ACI TENNCARE MEDICARE
ACI MCR UHCC MEDICARE
ACI MCR MEDICARE

The "rule" is that col C should contain "ACI MCR" if EITHER columns D or E contain the word MEDICARE. If column C = "ACI MCR" and neither column D nor E contain 'MEDICARE', I need the word ERROR returned for the user. Likewise, if column D or column E do contain 'MEDICARE' and column C does not contain "ACI MCR", i need the work ERROR returned. This latter scenario is the most probable. I've tried nested if statements, using MID, AND, OR to try to achive the desired result, but can't get it to work correctly on every scenario. Here's what I currently have as a formula in 2 different columns, but need to combine them so the user only has to look for "ERROR" in one column:

IF(MID(D1,1,8)="MEDICARE",IF(C1="ACI MCR","","ERROR"))
IF(MID(E1,1,8)="MEDICARE",IF(C1="ACI MCR","","ERROR"))

Thanks in advance for any help.

[gnod]

=IF(ISERR(SEARCH("MEDICARE",D1)),IF(ISERR(SEARCH("MEDICARE",E1)),"ERROR","A CI MCR"),"ACI MCR")

[Charlize]

in column F : =IF(OR(AND(D1="MEDICARE";C1="ACI MCR");AND(E1="MEDICARE";C1="ACI MCR"));"";"ERROR")
It's possible that you've got to change the ; in , in your formula.

Charlize

[pdeshazier]

thanks, but it didn't work. it ERRORED the record shown in attached s/sheet.

[pdeshazier]

thanks, but this also errored the first record as shown in the attached file. i tried changing it to the formula shown below since MEDICARE will only comprise the first 8 chars of those D and E cells, but it still failed.

IF(OR(AND(MID(D1,1,8)="MEDICARE",C1="ACI MCR"),AND(MID(E1,1,8)="MEDICARE",C1="ACI MCR")),"","ERROR")

[Bob Phillips]

=IF(OR(AND(COUNT(D1:E1,"*MEDICARE*")=0,C1<>"ACI MCR"),AND(COUNT(D1:E1,"*MEDICARE*")<>0,C1="ACI MCR")),"ERROR","ACI MCR")

[pdeshazier]

thanks, but that too also errored the first record as shown in attached spreadsheet and shouldn't have. does it have anything to do with not isolating the first 8 chars in D and E, or does *MEDICARE* do that?

[gnod]

thanks, but it didn't work. it ERRORED the record shown in attached s/sheet.

if col D or col E contains the word "MEDICARE", col C should be "ACI MCR".. right?

can you clarify, maybe i don't understand your conditions..

[Bob Phillips]

There is only one that I see. Are you saying = ACI MCR or contains ACI MCR?

Create a sheet with all the different error conditions and a good condition.

[moa]

=IF(OR(AND(COUNTIF(D1:E1,"MEDICARE*"),C1<>"ACI MCR"),AND(NOT(COUNTIF(D1:E1,"MEDICARE*")),C1="ACI MCR")),"ERROR","")