Check if values in a list exisiting in another list-CodePudding

I want to check if the list in Column A contains only products from the list in Column B.

If yes in Cell D1 the value = yes
If not in Cell D1 the value = no

As cou can see in the example above Product C and Product E do not exist in the list in Column B, therefore the value in Cell D1 is no.

So far I was able to figure out this formula =IF(ISNA(VLOOKUP(A1;$B$1:$B$5;1;0));"No";"Yes").
However, I would have to add a helper column with this formula to each row next to Column A.

Is there a solution that solves the issue with one formula in in Cell D1?

CodePudding user response：

A basic formula might be

=SUMPRODUCT(COUNTIF(A1:A10,B1:B10))=COUNTA(A1:A10)

or more dynamic

=SUMPRODUCT(COUNTIF(A:A,B1:INDEX(B:B,COUNTA(B:B))))=COUNTA(A:A)

These can be wrapped in an if statement to give "yes" or "no":

=IF(SUMPRODUCT(COUNTIF(A1:A10,B1:B10))=COUNTA(A1:A10),"yes","no")

=IF(SUMPRODUCT(COUNTIF(A:A,B1:INDEX(B:B,COUNTA(B:B))))=COUNTA(A:A),"yes","no")