Nested IF AND OR LEFT Statement Can't Wrap My Head Around

[brenda1996]

Hi all and thanks in advance for your help.

I am working a simple spreadsheet I can't wrap my head around a formula to return either OK or NOT OK.

The formula is =IF(AND(D2="C00"),OR(AND(LEFT(E2,1)="C"),OR(AND(LEFT(F2,1)="C"),"OK","NOT OKAY")))

I would like to Search for Column D2 C00 and in E2 and F2 any word that begins with a C would return a value of OK, else NOT OKAY.

This formula would then carry down to other lines.

I am missing something simple. The formula works but returns either a TRUE or FALSE instead of and OK or NOT OKAY.

Once that formula works I want to add to it to also return a OK value if anything in Column D begins with a R by using the LEFT(D2,1="R") statement but baby steps to get there.

If anyone can see what I am doing wrong with the formula above I sure would appreciate some guidance.

Kind regards and thanks in advance!

Brenda

 

[Fluff]

How about

=IF(AND(D2="C00",OR(LEFT(E2)="C",LEFT(F2)="C")),"OK","Not Ok")

 

[RoryA]

I think you mean:

=IF(OR(LEFT(D2,1)="R",AND(D2="C00",OR(LEFT(E2,1)="C",LEFT(F2,1)="C"))),"OK","NOT OKAY")

 

[brenda1996]

How about

=IF(AND(D2="C00",OR(LEFT(E2)="C",LEFT(F2)="C")),"OK","Not Ok")

Hi and thanks for your reply. That did not work, getting OK with E2 and F3 blank, that should ne NOT OK. Brenda

 

[Fluff]

If both E2 & F2 are blank then that formula will return Not Ok.

However I missed the 2nd part of your question, which Rory has dealt with, so try that.

 

[brenda1996]

I think you mean:

=IF(OR(LEFT(D2,1)="R",AND(D2="C00",OR(LEFT(E2,1)="C",LEFT(F2,1)="C"))),"OK","NOT OKAY")

Hi and thanks for your reply. That did not work, getting OK with all cells blank, D2, E2 and F2.Brenda

 

[brenda1996]

Hi and thanks for your reply. That did not work, getting OK with all cells blank, D2, E2 and F2.Brenda

I am sorry, YES it did work! I put the formula in line 4 as copied and forgot to change the formula. Thank you so much for your help. Looking at this it makes sense now! Yay!

 

[brenda1996]

Hi and thanks for your reply. That did not work, getting OK with E2 and F3 blank, that should ne NOT OK. Brenda

I am sorry, YES that also works but without the "R" portion, it did work! I put the formula in line 6 as copied and forgot to change the formula. Thank you so much for your help. Looking at this it makes sense now! Yay!

 

[brenda1996]

If both E2 & F2 are blank then that formula will return Not Ok.

However I missed the 2nd part of your question, which Rory has dealt with, so try that.

Yes Rory's formula works, thank you both! Looking at the formula makes perfect sense now. Thank you BOTH!! Brenda

 

[Fluff]

Glad we could help & thanks for the feedback.