Tips for Excel, Word, PowerPoint and Other Applications

Comparing Two Lists in Excel

Why It Matters To You

Comparing one list against another is something we do all the time, which can be both time consuming and frustrating if you can't automate the process. Maybe you're comparing a list of employee IDs against a master list HR file, or you're evaluating an invoice and are matching cost centers against a master file. As we conducting the matching process, identifying items in one list that aren't in the other is just as important as finding the matches.

How To ...

Conceptually, this is simple. We want to take each item on the short list and see if it exists on the larger list, which I'll call the master. If it exists on the master, we want to execute one action, return a value, give a message, or something. If the value doesn't exist on the master, then we want Excel to execute a second action. Simple enough?

You can use the sample file called, comparing_lists.xls, which has two short example lists and some examples. Here are my two sample lists. They're pretty short, for illustrative purposes, but the concepts are the same for larger data sets.

Mechanically, this requires a matching function and an IF statement to perform an action based on the results of the match.

There are at least two ways to perform the match:

Method 1: VLOOKUP/ISERROR

The first method uses a VLOOKUP function, which will return a #NA error is it doesn't find a match. The ISERROR function is the logical test that evaluates whether the VLOOKUP returns an error or not. If it does (TRUE), it means that no match was found and the IF statement should execute one action. If the VLOOKUP does not return an error (FALSE), this means a match was found and therefore, the IF statement should execute the second action. I typically use this structure when I want to match a list item and if found, associate it with another piece of information, hence the VLOOKUP.

=ISERROR(VLOOKUP(B19,Master_List,1,FALSE)

Here's an example. VLOOKUP tries to LOOKUP the value "San Francisco" in the master list and if it doesn't find it, resulting in the #NA error, it returns "No" and if it does find it, Excel simply returns a "Yes".

=IF(ISERROR(VLOOKUP(B19,Master_List,1,FALSE)),"No","Yes")

Often times, I'll write the VLOOKUP first to make sure I wrote it correctly, then encase it in the IF(ISERROR()) combo. When I do this, I simply copy the VLOOKUP function and paste it back in as the IF TRUE action.

Method 2: COUNTIF

You can also use COUNTIF to drive the logical test. If you COUNTIF each list value against the master, you'll either end up with a count of 0, meaning there was no match, or you'll end up with a count greater than 1, meaning there was a match. If we write this as a logical statement, it looks like this:

=COUNTIF(Master_List,B28)=0

Here's the full example. If the count of "San Francisco" in the master list is 0, then the IF statement returns a "No", otherwise it returns a "Yes".

=IF(COUNTIF(Master_List,B28)=0,"No","Yes")

Taking It Another Step Farther

Clearly the above 2 examples are pretty simplistic, but it's easy to do a lot more. For starters, you can use conditional formatting to identify any non-matching list items. You can also replace either the TRUE or FALSE actions with anything else. Maybe on TRUE, you want Excel to return a value from the master list. Maybe on FALSE, instead of returning a message, you want Excel to attempt a match on a second list. In fact, with nested IF statements, you could LOOKUP a value on up to 8 lists.

Notes

Last updated	8/15/08
Application Version	Excel 2003
Author	Michael Kan
Pre-requisites	None
Related Tips	Introduction to VLOOKUP VLOOKUP and IF/ISERROR Combos Advanced SUMIF and COUNTIF Using VLOOKUP to Match Lists