In Excel, I have two spill ranges, how do I return TRUE if a value occurs in both?

Question

In an excel workbook, I have two spill ranges. I want a third column that will return "1" if the same value occurs in both spill ranges. Normally, I would write a formula like

=IF(T3='Timeliness Issues'!J:J,1,"")

T3 is one spill range and the J column is the other. However, I keep getting a #SPILL error. I assume I need to add something to the formula so that it only looks at the returned value in the spill range and not the formula?

Any suggestions are appreciated!

Answer 1

I would just use AND formula with 2 COUNTIF formulas inside.

Based on the following assumptions:
The first spill range "lives" in cell A1, and the second "lives" in cell B1.
and
You want to check whether the values in the spill range B1 appear in both spill ranges.
or
You have a UNIQE spill range of all values and want to know if a value contained in both spill ranges.