Compare two data ranges and indicate if there are new/unknown values

[bert3000]

Hello,

I am trying to write a formula to compare two sets of data and indicate if there is a value in range A that does not appear anywhere in range B. I have used a COUNTIF function to give a TRUE or FALSE outcome for whether a single value appears in a range, however I am currently unable to figure out how a formula can give a TRUE or FALSE outcome to whether ANY value in one range does not meet any of the entries in another range.

Example:
A1:A10 - 10 rows of written information (1,2,3,4,5,6,7,8,9,10)
B1:B500 - 500 rows of data (random numbers that SHOULD be between 1-10)
Cell C1 - (here I want the formula to tell me if any entry in range B1:B500 does not meet one of the values in A1:A10 - so if all values are equal to one of the entries in A1:A10 then display TRUE, but if any value is different then display FALSE)

Any help would really be appreciated (apologies if this answer already exists but I've searched for about 2 hours and unable to find the way to do this)

Rob

 

[Robert Mika]

If your data are consecutive numbers
Try
=IF(COUNTIF(B1:$B$500,">"&10),TRUE)

 

[bert3000]

Thank you for the swift reply, but I should have been more clear.

I need this to work if the entries are not necessarily numbers. But say if the 10 entries in Column A were, for instance, words. So if they were: Cat, Dog, Mouse, Pig, Cow, Sheep, Fox, Elephant, Rhino, Horse.

Then if all entries in B1:B500 were one of the above animals, then the formula would show TRUE, but if Crocodile is one of the entries then show FALSE

Regards