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.
Variant that will work with Excel >/= 2021:
=IF(
SUM(
--(XLOOKUP( Table4[@[Dir1]:[Dir3]], Table4[@[Wri1]:[Wri4]], Table4[@[Wri1]:[Wri4]], "(n/a)" ) <> "(n/a)")
),
"Y", "N"
)