ThePort Community

Random thoughts, musings and helpful hints from ThePort's MVE (Most Valuable Employee).

The magical powers of VLookup

Thursday, December 16, 2010

Anyone who has ever had the distinct pleasure of analyzing large amounts of data has spent a good deal of time using MS Excel. I'd like to share a how-to guide about a particularly useful formula that most who deal with Excel will run across at some point or anther: VLookup.

VLookup allows you to compare two sets of overlapping data to find duplicate records. For example, imagine you have a list of 500 user IDs from a community for users who have created a blog entry. You also have a CSV of all registered users (10,000 total) in the community including their user ID, name, e-mail, join date, etc. Using Vlookup you can compare your original list of 500 active bloggers by user ID and pull in pertinent information from your list of all registered users such as e-mail and join date.

How does it work? I'll use a simplified version of the example above to demonstrate.

First, your list of user IDs for active bloggers looks like this. Throughout the example I will refer to this as the active bloggers spreadsheet.

All you have is the user IDs for those active bloggers, but in a separate file you have a list of all users who have registered in the community, which looks like this. We will call this the registered users spreadsheet.

In this example we are using a very small data set, so its easy to see which ID corresponds to which user, but this quickly becomes impractical as more and more data is added to each list.

To execute a Vlookup you start in the spreadsheet you would like to pull data into. In our example this would be our active bloggers spreadsheet (because we want to know more about who these bloggers are).

Specifically, you would start by typing =vlookup in cell B2 of the active bloggers spreadsheet. Doing so will prompt you to add several pieces of information separated by a comma. That information is:

lookup_value- the lookup value is the unique identifier that you are using to match this record (in your current row) to the record you are looking up (our all registered users spreadsheet). In this example the lookup_value is the ID in cell A2. All you need to do is highlight cell A2 and type a comma to get to the next prompt.
table_array- table array is the array of data that you are attempting to compare your lookup_value against. In our example, the table_array would be all the data in the registered users spreadsheet. You simply have to highlight all cells on the registered users spreadsheet from cell A1::C7 and type another comma to get to the next prompt.
col_index_num- Column index number (col_index_num) is asking for the numbered column from your table array (1 represents the first column of values- A in our example, 2 represents the second column from the left most column, 3 represents the third column from the left, and so on). What this means is you can determine which value you would like the formula to return if it finds a match for your lookup_value. In our example we would use "2" if we wanted to return Name from the registered users spreadsheet and "3" if we wanted to return Join Date. For our example we will use "2" to return the name associated with this record. Simply type the appropriate value and hit comma to receive the next prompt.
[range_lookup]- Range lookup is an optional input that allows you to specify if your lookup should only match your lookup_value against EXACT matches. If you enter True the formula will return non-exact matches. False tells the formula to return only exact matches. In our example, and in most cases, you will want to type "False" here to finish the formula.

After you have specified the above inputs simply end the formula by closing the parentheses ")" and hit enter.

If you performed the lookup correctly your active bloggers spreadsheet should look like this:

We now know that ID 123456 is associated with Bob. You can drag this formula down column B to apply the same lookup against all records in your active bloggers spreadsheet. You can also use the same lookup, slightly altered, to return join date in column C.

I hope you find this useful. I know some of my co-workers at ThePort will.

Anonymous: I love how you state 'some of my co-workers at ThePort' as if I don't know you're talking about me!!! :) Great write up, Kev. I'll make sure to use this the next time I'm scrubbing data. Very helpful indeed!!!
Kevin Fielding: Posting anonymously is a poor practice Kyle. Clean up your act...
kcoleman: Kevin, I'd appreciate if you could please remember that you have to add parenthesis after the formula is entered. That was my downfall this time. Next time will be perfect. PROMISE!
comment