Hello,

I am trying to develop a formula to check if any item on List 1 exists on List 2, for example:

List 1: Apples, Bananas, Oranges
List 2: Broccoli, Carrots, Bananas

I would like this formula to return TRUE as Bananas exists on both lists.

The challenge being that I don't want to rely on a "helper" column, where I simply do a INDEX/MATCH on each record in a helper column, and if the resulting sum is greater than 0, then we win!

However, I did want to apply that above logic as an array formula, and so I tried something simple-minded like ={SUM(MATCH(A2:A100,B2:B9000,0))} entered with CSE. This is returning an error, and I assume it is due to entering an array for the first argument in the MATCH. Any words of wisdom would be greatly appreciated, and I am not opposed to formula-writing or macros if a formulaic approach isn't feasible.

Thank you for your help,
Yogi