My first data journalism post: Vlookup

I am a newbie when it comes to data journalism, excel and numbers in general.

When playing around with Excel I discovered a really useful function: VLOOKUP. Basically, it looks up values in a table that are listed in column format, given another value. And why? V stands for vertical. There is also a similar function called HLOOKUP (h=horizontal).

I decided to make a small tutorial with my knowledge about Vlookup.

Let’s suppose you have this table:

Captura de ecrã - 2013-05-23, 16.44.03

Given a list of names in another part of the table (or this case, column H), you want to figure out what kind of animals belong to these names:

Captura de ecrã - 2013-05-23, 16.54.22

Given a list of names in another part of the table (in this case, column H), you want to figure out what kind of animal it is:

The VLOOKUP function looks like the following:

=vlookup (lookup value, tbale where values reside, column # where values are located, true or false)

Let’s look at the function a bit closer:

The first thing that goes into the VLOOKUP function is the information you know and that will be used to lookup other values. In this case you know the names of the animals. In this case they also reside in Column H, from cells H2 to H6.

If we want to put the type of animal next to the name of the animal in column (so I2 would correspond to the name of the animal in H2), we insert the vlookup function there:

Captura de ecrã - 2013-05-23, 17.12.59

and put H2 as the first thing in our vlookup function:

Captura de ecrã - 2013-05-23, 17.16.49

Next, we need to know the location of the table where our values reside. These happen to be the cells from A1 to B6. Then we highlight with our mouse to insert the lookup function. You have to include all the cells in the table:

Captura de ecrã - 2013-05-23, 16.58.09

At the same time, the VLOOKUP function automatically puts in the cells you’ve highlighted like this:

Captura de ecrã - 2013-05-23, 16.56.48

Now we need the column number where the values are located. Always start with the first column (column A in this case) as 1 and count out to the right. In this example, the type of animal listed is in column 2, so that’s what we would need to insert in the vlookup function.

Captura de ecrã - 2013-05-23, 16.57.09

Finally, the last attribute that VLOOKUP takes is either “true” or “false”. What is the difference?

  • True: We write true if we want an approximate match, which means that if the exact match is not available True will pick the closest one to it.
  • False: We write false if we want to find the exact match in this case we want.

So this is your final function:

=vlookup(H2, A1:B6, 2, false)

Captura de ecrã - 2013-05-23, 16.57.20

Once we close off the parenthesis and hit “Enter”, vlookup automatically calculates:

Captura de ecrã - 2013-05-23, 17.24.23

And this will work for other cells. You have to make sure that the location of your keys and values is correctly selected.

When you copy and paste formulas on Excel the location of the data can move around relative to the cell. In this case you have to lock the range of the location by using $ symbol.

For example: $A$1: $B$6 instead of A1:B6

This way , as we move down A1:B6 does not become A2:B6 and it will stay with the original range of data. We can just copy what is in the cell to the rest of the cells:

Finally, here’s our result, after making the “$” changes:

Captura de ecrã - 2013-05-23, 16.57.56

Captura de ecrã - 2013-05-23, 16.58.09

I Know this was a very simple example but if you understand the mechanics on this one it get easier. Normally, vlookup is used between sheets and workbooks but by understanding this example you can try it between sheets.

What else can you tell me about VLOOKUP?

Advertisements
This entry was posted in Data and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s