Excel 101: VLOOKUP (The Easy Way)

This post serves as a note to self, seeing as I'm liable to forget a lot of the processes associated with this fairly complex tool, that few tend to know about.

So what is a VLOOKUP?

A VLOOKUP is a tool that allows a list of data to become dynamic in order to sift through large amounts of data very quickly. I'll show you how below.

STEP 1)

Bring up your data (I'm using a spreadsheet taken from the Guardian on opinion polls from 1984-2013)

STEP 2)

Copy the labels at the top of each column before you begin the actual lookup process and place them somewhere else on the sheet, like in the image above.

STEP 3)

Next we need to make a list out of the first column, so place your cell selector in the cell directly below the matching column title in the table you just created; bear in mind that I am using Excel for Mac (2011) and so there may be some differences in tool location. Select data validation; when doing this, you may need to select the source button  shown here to select large amounts of it. Once you've selected the list and validated the data (Mac-users should find it under the DATA tab,) you should check the first column. If you've followed my advice, there should be a drop down box in the selected cell, which should include all selected data, as shown below.

STEP 4)

Now we can use the VLOOKUP. Firstly, select the cell below the next column label in the small table you created.

Now place =VLOOKUP into the box (it will appear in a drop down menu when you start typing, so you can just click on it when it comes up.) 

In the parentheses that appear with the VLOOKUP tool, type in the cell reference for the list you just created, then place a comma after it.

STEP 5)

Now we need to select the relevant data: Highlight the first column of data from which we built the list AND the column next to it, which should match up with your created column labels. As you do this, a reference of data should appear like so:

STEP 6) 

Now this is important. Now that all the necessary data has been highlighted, an option is available that wasn't previously. As you highlight, in the bottom left of the screen (on a Mac) you will see the letter C with a number. The C is a COLUMN reference and the number is the amount of columns highlighted. Seeing as I just highlighted the first three columns, I have "C3" written there, ignore the other reference in the image. 

Using this column reference, you need to complete the argument by placing another comma after the data range and inserting the number of the column ONLY ("3" in this case.) After this, you should place a second comma and the number "0" before pressing enter to complete the argument. This is to solve an error of some kind that comes with completing such formulas. If everything has gone to plan, you should ideally see the data in all 'looked up' cells change, as different datums are selected from our dynamic list like the images below demonstrate.

 The first piece of data: "15-06-1984"

The second piece of data "15-07-1984"

And that pretty much covers it, that's how to do a VLOOKUP on Excel the easy way, and if you didn't like the explanation, or if image-based guidance isn't really your learning style, here's a video tutorial:

I hope that you found this helpful. Have a nice day!