Comparing values in two different columns, detecting those that are also present in the second

Question

I have two columns I'd like to compare data in. The first column has about 50 rows, whereas the second column has 200 rows.

I would like to be able to tell if any cell in the first column matches up with a value in the second column.

I first tried this with the QUERY() function

=QUERY(A12:B, "Select A where A = B")

but this didn't seem to work, I got a circular dependency error.

I then tried the array function.

=ARRAYFORMULA(if(A12:A=B12:B,"REMOVE_ME", "DON'T REMOVE ME"))

but this was outputting incorrect values. Is there an easier way to do this or am I missing something?

Here is sample data. The value 2 is given REMOVE ME because there is a value 2 in Column B. (sorry for the horrible formatting, I'm not sure how to do the grid correctly)

Column A   |    Column B   |     Column c   |
----------------------------------------------
1          |       2       |     DON'T REMOVE ME
-----------------------------------------------
5          |       7       |     DON'T REMOVE ME 
-----------------------------------------------
8          |       10      |     DON'T REMOVE ME
------------------------------------------------
2          |       13      |     REMOVE ME
-------------------------------------------------
 (blank)   |       24      |     (blank)
-------------------------------------------------
(Blank)    |       31      |     (blank)
-------------------------------------------------

Answer 1

This can be done by invoking match inside arrayformula, as follows:

=arrayformula(if(len(A12:A), if(isna(match(A12:A, B12:B, 0)), "Don't remove", "Remove"), ))

or with linebreaks,

=arrayformula(
   if(len(A12:A), 
     if(isna(match(A12:A, B12:B, 0)), 
       "Don't remove", "Remove"
     ), ))

The combination if(isna(match( means "if not found", literally "if match returned #N/A error". The outer if takes care of empty cells. Note that the trailing comma is intentional: this is how one correctly leaves a cell blank based on IF statement.