Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

migrated from Sep 22 '11 at 12:29

This question came from our site for professional and enthusiast programmers.

If you are doing this every morning that you might want to look at my Duplicate Master addin , 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
share|improve this answer
Looks excellent...m gonna try this.. – Confused Oct 31 '11 at 11:13
@Sangram , cheers - hope it works for you. I'm happy to take your feedback. :) – brettdj Oct 31 '11 at 12:48
  • 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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.