Find and keep matches from 2 columns in Excel

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

  • List 1: A1:A1000
  • List 2: B1:100

In column C, enter =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.