Find and keep matches from 2 columns in Excel

Question

In Excel I have two columns, one with about 3000 rows and one with about 100. I need to find all of the matches between the two columns and put them in a separate column. I have to do this every morning so if there is a way to do so, I'd like to make it as automated as possible.

Answer 1

If you are doing this every morning that you might want to look at my Duplicate Master addin , http://www.experts-exchange.com/A_2123.html for a readily applied automated solution

1. It provides an output column (your query), highlighting, selection and deletion options regardless of data size
2. It can run over multiple sheets (if applicable)
3. The addin provides functionality for
   • case sensitive/case insensitive matching
   • ignoring all whitespaces (spaces, line breaks, CHAR(160))
   • ignoring non-printing characters
   • regular expression text substitutions

Answer 2

List 1 A1:A1000 List 2 B1:100

in column 3, =COUNTIF($A$1:$A$1000,B1)>0, and copy down to row 100. All the TRUE's represent #'s that are in both lists.

You can also do something similar with VLOOKUP.