Forum Discussion

Copper Contributor

Dec 17, 2025

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...

excel

Excel on Mac

Formulas and Functions

office 365

OliverScheurich
Dec 18, 2025
=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.

Logical test for same text string existing anywhere in both ranges..xlsx10 KB

Extopia

Copper Contributor

Dec 18, 2025

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?

OliverScheurich

Gold Contributor

Dec 18, 2025

=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.

Logical test for same text string existing anywhere in both ranges..xlsx10 KB

Extopia
Copper Contributor
Dec 18, 2025
That works, Oliver - many thanks!