Check if range does NOT match another range

I have two ranges of data one in column A, one in column B - the data varies in its content.

I need a forumla that will check if all the data in col A also appears in col B and will alert me if it doesn't

So for example lets say col A contains a load of colours
BLUE
GREEN
BLACK
RED
BLUE
GREEN
RED
BLACK
GREEN
ORANGE
BLACK
RED

Col B contains the data to check against
BLUE
GREEN
BLACK
RED

The formula in this case would return an alert because there is data in col A that does not appear in col B (ORANGE) - however if ORANGE was removed, there would be no alert, because everything that appears in col A also appears in col B.

Thanks in advance for any support with this.

One way, provided that the Col B data is unique,

=IF(SUMPRODUCT(COUNTIF(A1:A12,$B$1:$B$12))=ROWS($A$1:$A$12),"all data in col B appears in col A","NOT all data in col B appears in col A")

see if that helps,

berlan

This will give a True/False result (True = all values in A exist in B)

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A12,B1:B4,0))))=ROWS(A1:A12)

Use Conditional formatting

Formula

=ISERROR(MATCH($A1,$B$1:$B$4,0))

FILL with required colour

Thanks all, is there a way to make the same thing work, but where the initial list in is col a and the second is row 1

ie col A contains a load of colours
BLUE
GREEN
BLACK
RED
BLUE
GREEN
RED
BLACK
GREEN
ORANGE
BLACK
RED

Row 1 contains the data to check against
BLUE
GREEN
BLACK
RED

Originally Posted by samcdavies

Thanks all, is there a way to make the same thing work, but where the initial list in is col a and the second is row 1

ie col A contains a loa....

I've come up with a bit of a workaround for this and it is:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:B9,A2:A500,0))))
where A1 is blank to allow for blank entries.

This should always give me the answer 499 if they match. Any less and there is no match. Thanks everybody!