Comparing 2 columns- if a match is found return a value in 3rd column

**lineson** · 12-20-2011, 02:57 PM

In Excel 2010 I am trying to compare 2 columns -both contain USER ID's. I want to compare column A to column B. If a match is found in column B, return a value in column C of "keep". Can anyone help me with this?
Thank you.

**GeneralDisarray** · 12-20-2011, 03:07 PM

how do you want to compare? can you post an example?

could try this (if you want to compare each A value against the entire B column and return a "keep" value if a match is found for that row):

=IF(iserror(match(A1,B:B,0)),"","Keep") -- write this into cell c1 and copy/paste down the column

**lineson** · 12-20-2011, 03:21 PM

Thanks for your prompt reply. I have a column of UserID's- unfiltered- that I want to compare to a column of USER ID's that I have filtered. If the unfiltered ID does not exist in the filtered column, I want to delete that ID. Does this explanation help?

**lineson** · 12-20-2011, 03:31 PM

This formula does work- thank you. Next question (do I need a new post?) What is an easy way to paste the forlmula into 26,000 + rows?

**GeneralDisarray** · 12-20-2011, 04:48 PM

yes, just for the sake of this example lets say you have 26,500 rows of data starting in row 1 -- so column A will have entries from 1 to 26,500 --

Here's what i would do:

write the formula in cell C1
Press enter
select cell C1 but don't double-click (single click!)
press cntrl+c to copy the cell
single click on cell a1
press cntrl+down arrow (this will "fly" you to the last cell with data in it A26500)
single click on C26500 (the c column at this lowest row)
press shift+cntrl+up arrow (this will highlight the C column from 1 to 26,500)
Press Enter (this will paste the formula in every highlighted cell)