3

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)
-------------------------------------------------
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.