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?
=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.
10 Replies
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" )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).
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?
=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.
That works, Oliver - many thanks!
Could you post a pic. showing a few rows with those (n/a) + the expected result next to the corresponding records?
Hi
=IF( SUM( IFNA( XMATCH( Table4[@[Dir1]:[Dir3]], Table4[@[Wri1]:[Wri4]] ), 0 ) ), "Y", "" )=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".
