Testing if 2 arrays include same value

[fatduck]

Hi there
I have 2 arrays, and I need to test whether any of the values in array A (cells A2:A7) occur in array B (Column H:H on a different worksheet). All I need to return is a basic "yes" or "no", but I'd rather a neat way of doing this rather than doing 5 separate lookups for each of the array A values.
If anyone has any advice I'd be very grateful!
Thanks!
Daniel

 

[Richard Schollar]

I would probably run a Find() against the column H of the other sheet suing the values in the first range in the What: argument. Find is optimised for searching ranges so should be reasonably efficient.

 

[mikerickson]

This works
=IF(SUMPRODUCT(--ISNUMBER(MATCH(A2:A7,H:H,0))),"Yes","No")

 

[fatduck]

Thanks for the great solutions!

 

[northwolves]

This works too.

=SUMPRODUCT(COUNTIF(Sheet2!H:H,A1:A8))>0

Regards
Northwolves