If/then statements

New Contributor

Looking for a way to put a value into a column if one column is even partially true to another


Column A --bobs cars; Column B --bobs trucks

since both have 'bob' in the column want a '1' placed in column C

Is this possible?


Thank you for the help. 

6 Replies
Hello! I use functions like this all the time! It'll sound clunky but each piece is needed and it works great! Formula: IF(AND(ISNUMBER(SEARCH("bob", [cell])), ISNUMBER(SEARCH("bob", [second cell]))),1,0) The last 1 and 0 will be whatever values you want for finding "bob" or not

@AnalystNerd but what if it isn't just "bob' in column A?  Basically if any word in column A matches column B want to place the '1' in the 3rd column.  Bob was just an example.


I appreciate the help.



Is it possible? Yes, and No. It all depends on where the matching string of letters is to be found. On how varied your two columns are going to be.


In the example you give, it's quite simple, since the three characters "bob" are the first three characters in each string, for then this formula works


But if the "bob" characters are buried elsewhere in the text, it becomes trickier. 

And if your real world issue is far more variable, sometimes with 2 characters matching, sometimes with 4 or 5, all the more elusive.



Let's make it more complicated still...if it's any word in any position....



Since you're adding complications as we go along, how about giving a much more complete picture here of what you're dealing with. How many words max? What's the bigger picture into which this little task fits?



You could try this (adjust range references as applicable and assuming you have the let function), but if your text has characters that are 'special' to xml (such as the & character) in column B, then it will not work. 



=Let(searchTerms, FILTERXML("<L><I>"&SUBSTITUTE(B1, " ", "</I><I>")&"</I></L>", "//I"),
         matches, SUM(--(ISNUMBER(SEARCH(TRANSPOSE(searchTerms), $A$1:$A$10)))),
         If(iserror(searchterms), "ERROR", --(matches>0)))