Excel Help!

[Jay_2]

I am trying to do this...

If column A2 contains yes and column B2 contains 1 then Yes else No

Can anyone help me with this one?

Thanks.

 

[PTNL]

Multiple conditions can be evaluated together with the "AND" function. That would create your condition clause of an "IF" function.

 

[Jay_2]

ok

so

=IF(AND(A2="Yes",B2="1"),"Yes","No")

 

[PTNL]

=IF(AND(A2="Yes",B2="1"),"Yes","No")

If the value in B2 is "1" and not 1 (without quotes), then, yes, that will work.

 

[Jay_2]

Sorry yes that was a typo.

 

[Jay_2]

ok what if I wanted to say

If column A2 contains "yes" (it could contains yes123 or yes 234 etc) and column B2 contains 1 then Yes else No

I can't use wild cards in an If statement.

 

[PTNL]

ok what if I wanted to say

If column A2 contains "yes" (it could contains yes123 or yes 234 etc) and column B2 contains 1 then Yes else No

I can't use wild cards in an If statement.

Look into the SEARCH and FIND functions, and read up on the error handling if a substring match is not found.

 

[Dogs]

ok what if I wanted to say

If column A2 contains "yes" (it could contains yes123 or yes 234 etc) and column B2 contains 1 then Yes else No

I can't use wild cards in an If statement.

Assuming by 'contains' "yes" you mean that it starts with "yes" like in your examples, this will give the desired behavior. If you want it to pull "yes" out of anywhere in the string, I.E. you want strings like '127yesBanana43' to match, then you'll have more work to do.

=IF(AND(MID(A2,1,3)="yes",B2=1),"Yes","No")

You can use MID() to pull out a substring from a string. In this case, you want to start at position 1 and pull out 3 characters from A2, as this will give you the first three characters of the contents of A2. Once you have that, simply check to see if it's equal to "yes".

Since you want both conditions to be true in order to display "Yes", you need to add both conditions to an AND() function call, and nest that in an IF() statement. If the AND() call is true, you want the IF() statement to return "Yes". Otherwise, you want it to return "No".

Are you able to follow how I came up with the function posted above?