IF, AND, OR and LEFT

[Frank Bacchus]

Hi All,

I would like to create a formula that would provide the correct results based on the following conditions:

E2 = IF A2 = 1 and LEFT of B2 = "DC" Assign "DC Closed - Last Week" OR IF A2 = 1 and LEFT of B2 = "CM", assign "CM Closed - Last Week" Else E2,

I started with this but didn't get Too far: Help!

=IF((AND(LEFT(B2,2= "DC", A2 EQ 1, "DC Closed - Last Week"),E2) OR (AND(LEFT(B2,2= "CM", A2 EQ 1, "CM Closed - Last Week"), E2)

This 't work didn't Work

Appreciate your help

frank

 

[Somendra Misra]

Hi,

Try Below formula:

=IF(AND(A2=1,LEFT(D2,2)="DC"),"DC Closed - Last Week",IF(AND(A2=1,LEFT(D2,2)="CM"),"CM Closed - Last Week",FALSE))

In E2

In place of FALSE put something which will be result of if both the conditions not fulfilled

Regards,

 

[Frank Bacchus]

Hi All,

I would like to create a formula that would provide the correct results based on the following conditions:

E2 = IF A2 = 1 and LEFT of B2 = "DC" Assign "DC Closed - Last Week" OR IF A2 = 1 and LEFT of B2 = "CM", assign "CM Closed - Last Week" Else E2,

I started with this but didn't get Too far: Help!

=IF((AND(LEFT(B2,2= "DC", A2 EQ 1, "DC Closed - Last Week"),E2) OR (AND(LEFT(B2,2= "CM", A2 EQ 1, "CM Closed - Last Week"), E2)

This 't work didn't Work

Appreciate your help

frank

Thanks, Somendra. That was great. I have a similar issue with the OR side, I want to see if the left of the content in B2 contains "DC" assign "Queued" OR if the left of the content in B2 contains "CM" assign "STEP 60" or else assign the value of B2. I tried the below, but it didn't work.

Can you please shed some light on this?

=IF(OR(ISNUMBER(SEARCH("DC",B2)),"Queued","CM",B2,"STEP 60"),B2)

thanks

frank

 

[Somendra Misra]

Hi Frank,

Try this:

=IF(LEFT(B2,2)="DC","Queued",IF(LEFT(B2,2)="CM","Step 60",B2))

Regards,