Guidebook: Excel Tip #2 - VLOOKUP

Vlookup is the essential function for comparing data in one list to data in another list.

In this example our first table has a list of addresses but no postal codes. The second table has a list of addresses WITH postal codes. We'll use the VLOOKUP function to find the right postal code in the second table and add the postal code to the first table.

For the VLOOKUP function to work properly both lists must have some data in common. And both sets of data should be in the same Excel workbook. Otherwise we have nothing to use to make a match. In this example the Address column will be used to find a match.


Let's add the Postal Code to the first table...
1. Create a new column to store the postal code in our address table.
2. The function in this cell is
VLOOKUP(lookup, value_table_array, col_index_num,[range_lookup])
lookup = the cell containing the address to match
value_table_array = the entire range of columns containing the second table
- click and select the columns you want to search for a match and the columns containing the data to find.
col_index_num = the column number containing the data you want to find in the table you just selected
[range_lookup] = can be TRUE or FALSE - specifies whether or not the data should match exactly

In the figure below we're going to look for the Address in C2 by searching in table I:J.
In the table I:J, Column #1 contains the address and Column #2 contains the Postal Code.
We're happy returning the first match rather than an exact match.


The function returns the correct postal code so we use "Fill Down" to apply the same function to the rest of the cells in the same column.


Now that you've had an introduction to VLOOKUP you probably can't believe how much time you spent comparing lists of data in the past.

Have fun!

Click here for the VLOOKUP help article on the Microsoft Office website.