Analyst Toolbox

Use Excel's VLOOKUP Function to Compare Lists

Product: Microsoft Excel

Using MS Excel "vlookup" to compare lists.
As crime and intelligence analysts we often have to compare two lists to find data that appears on both lists. Finding case numbers, names, or locations that appear on different lists can be tedious and inaccurate if done manually, especially if the lists contain numerous entries.
One way of comparing lists is to use "vlookup" in MS Excel. The vlookup function takes a lookup value and compares that value to a table array. If a match is found the function returns the cell contents from a specified column index. Vlookup can return exact matches or close matches.
Let's say that we have two lists of names on an MS Excel spreadsheet and we want to find out what names are contained on both lists. List-1 is in column A and List-2 is in column D.

Column-A
List-1
SMITH
JONES
JOHNSON
WILSON
GREEN
NELSON
HANNIGAN
CHESBRO
HATCHER


Column-D
List-2
MASON
BRADLEY
WHITE
CHESBRO
BILLINGS
ROBERTS
WILSON


Our vlookup formula is =VLOOKUP(A1:A10,$D$1:$D$7,1,FALSE)

This formula tells MS Excel to compare each name in cells A1 - A10 with each name in cells D1 - D7 (note that we use the "$" Absolute Reference for the cells containing the table array). When the lookup value matches a cell in the table array our formula returns the contents of that cell (i.e. the matching name). The "False" at the end of the formula tells MS Excel to only return exact matches.
Note that if the table array contains multiple columns we can tell vlookup to return the contents of any cell in the associated row by changing the column index (in this case the number "1").
We enter our vlookup formula in cell B1 and fill down column B matching the number of cells we have in our lookup values in Column A.

Column-A
List-1
SMITH
JONES
JOHNSON
WILSON
GREEN
NELSON
HANNIGAN
CHESBRO
HATCHER
GRANT

Column-B
Vlookup
#N/A
#N/A
#N/A
WILSON
#N/A
#N/A
#N/A
CHESBRO
#N/A
#N/A

Column-D
List-2
MASON
BRADLEY
WHITE
CHESBRO
BILLINGS
ROBERTS
WILSON


The vlookup function returns the data in column-B. In this case we see that the names "Wilson" and "Chesbro" are found in both lists. Where the lookup value from column-A wasn't found in column-B, MS Excel returns #N/A.
The vlookup function is a useful tool which makes data matching quick and easy.

Author: Michael Chesbro
Updated 7/13/2009

View all toolbox tips | Send to a colleague