Match value in one cell with values in multiple columns

I have first set of data which is my rules and it contains values in 4 columns and approximately 100 rows. Some cells are blank.

I have second set of data in one column and approximately 50 rows. Now for this second set of data, I would like to compare value in each cell to all the values in first data to return a true or false based on if there is a match or not.

Now I tried the following formula:
=SUMPRODUCT(--('[file1.xls]Sheet1'!$C$2:$F$55=LEFT(B6,8)))

This works just fine and returns 0 or 1 until there multiple matches and I get numbers like 5 or 7. I would like to get a 0 or 1 back as soon as there is one or more match. I prefer not to change my first data set to put all my unique values in one column because that data set changes frequently and I get a download plus if anyone needs to verify that those "rules" are correct then it can not be done in one column of data.

Any thoughts?

Thank you in advance,
Jay

You only really need to use SUMPRODUCT if [file1.xls] is going to be closed when this function calculates else use COUNTIF.

Irrespective of which approach you use encase the result within a SIGN call

Please Login or Register  to view this content.

All positive values will return 1 and 0 will return 0