Check if values of column A exist in column B?-CodePudding

I have two Excel sheets. The first "SH1" contains a column "A". The second "SH2" contains a column "B".

I want to check if all values in SH1.A are contained in SH2.B at least once. I want to output the value "contained" or "not contained" in a new column in SH1 to get the overview.

The values in SH2.B are manually set while the ones in SH1.A are created with a concatenation using a reference/formula.

Is it possible to do this with Excel's functionality? If yes, how?

CodePudding user response：

If we assume the following structure, then you can use COUNTIF and IF as follows in SH1 and fill this formula to the corresponding cells.

=IF(COUNTIF(SH2!B$1:B$11,A1)>0, "contained", "not contained")