Matching data in 2 cells in Excel

Today's quick blog article will talk about a favorite formula string I use time and time again.  If you read my blog, you know I use Excel as my tool of choice for data manipulation.

My challenge today was to find and match emails in two columns.  What I wanted to do was weed out emails that were duplicated in both columns.   I used a formula and will explain it below.

Let's use the following list as my example:

The formula I would use on the sample above would be:

=IF(ISERROR(MATCH(A1,$C$1:$C$4,0)),"",A1)

I place the formula in the first row of Column B and copy it to the end of the rows to match Column A.  In my example I want to know which email in Column A is also in Column B.

Explaining the formula, the part of the formula describing the C column should start with the first row and end with the last row.  For example, let's say column A had 20 rows and column C had 40 rows.  Then, the formula would read as follows:

=IF(ISERROR(MATCH(A1,$C$1:$C$40,0)),"",A1)

Note that the section describing the C column reads $C$40 which defines the range of data in the C Column.

What I end up with after inserting this formula in my example is the following:

I can now sort the data together and remove the items in Column A.  Quick way to find matching data.  I was doing an import in my ACT database and I didn't want to import records I already had. In this case it was emails, but it could be names, addresses, etc.  You get the idea.

Note: the data in column C does not have to be sorted.