Forum Discussion
Solved
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' name...
=IF(SUMPRODUCT(COUNTIFS(Tabelle1[@[Wri1]:[Wri4]],Tabelle1[@[Dir1]:[Dir3]]))-MMULT(MMULT(N(Tabelle1[@[Dir1]:[Dir3]]="(n/a)"),ROW($1:$3)^0),MMULT(N(Tabelle1[@[Wri1]:[Wri4]]="(n/a)"),ROW($1:$4)^0)),"Y","")
This formula returns the expected result according to what i understand.
Here it is - expected result in the final row
Hi
Still with your Table4 a 365 option:
=IF(
SUM(
IFNA(
XMATCH( TOCOL( IFS(Table4[@[Dir1]:[Dir3]] <> "(n/a)", Table4[@[Dir1]:[Dir3]]), 2 ),
TOCOL( IFS(Table4[@[Wri1]:[Wri4]] <> "(n/a)", Table4[@[Wri1]:[Wri4]]), 2 )
),
0
)
),
"Y", "N"
)
Thanks Lorenzo - unfortunately that returns an error if ALL director name fields contain "(n/a)" (e.g. the second row in my screenshot).