Forum Discussion
If/then statements
Looking for a way to put a value into a column if one column is even partially true to another example Column A --bobs cars; Column B --bobs trucks since both have 'bob' in the column want a '1' p...
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)))