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.
These solutions are promising, but I should have mentioned that I would like the formula to ignore the text string "(n/a)", which is in many columns and therefore creates a false match. Can this string be excluded?
Could you post a pic. showing a few rows with those (n/a) + the expected result next to the corresponding records?