Forum Discussion
Logical test for same text string existing anywhere in both ranges.
Hello. I have a Table of film credits, including the names of directors and writers. Some films have multiple directors (up to 3 individuals), whose names are in columns F, G and H. The writers' names (up to 4 individuals) are in columns J, K, L and M.
I want to test for whether the film has a writer/director - e.g, one of the director names in the range F:H is the same as one of the writer names in the range J:M.
I have created a column O to contain a formula with a logical test returning Y if there is a writer/director present.
I tried =IF(Table4[@[Wri1]:[Wri4]]=[@Dir1]:[Dir3],Y,N) but this returns a spill error.
Can anyone help?
1 Reply
=IF(SUMPRODUCT(COUNTIFS(Tabelle1[@[Wri1]:[Wri4]],Tabelle1[@[Dir1]:[Dir3]])),"Y","")You can apply this formula. It spills the result down the column. The table name in my example is "Tabelle1".
