Why use VLOOKUP?
Excel is an important part of how we automate reports at Trackpal. As an Excel plugin, Trackpal uses the spreadsheet program to collect and present your data in a beautiful report. Although it can seem a little daunting at first, it’s an extremely valuable tool, and it’s well worth spending some time learning about what it has to offer.
Whilst using the program every day for many years, we’ve learnt hundreds of tricks to save us time. We offer plenty of tips and advice here on our blog as well as our other blog, Make Reporting Sexy, which offers in depth articles on everything to do with reporting. We also highly recommend using Chandoo.org if you’re a beginner, or looking to brush up your Excel skills. There are countless formulas that you can use as short cuts to reveal the data that is most meaningful to you. As the person within the company with the least amount of technical Excel experience… (and I didn’t think I was too bad until I met Excel expert, Trackpal MD Scott Lawson!) I thought I would be the best person to run through some Excel tips in a back-to-basics beginners guide.
What this post will teach you
One of the most handy and simple tricks of all is the VLOOKUP function. Don’t be afraid! Once you learn this one quick function you will be able to do so much more with your data.
This post will run through the basics of what the function is and how it works.
So, what is VLOOKUP?
Once you understand what VLOOKUP is used for it will start to make a lot more sense.
In basic terms, VLOOKUP is used to look up a value from a different location in your workbook. It’s useful because when dealing with large amounts of data because you always need to look things up.
Here’s the description, courtesy of Excel:
VLOOKUP looks for a value in the leftmost column of a table,
and then returns a value in the same row that you specify.
This formula also works exactly the same way in Google Spreadsheets, however without the same explanatory interface, so it’s important to understand the formula before you try it out.
When would you use it?
VLOOKUP can be used for so many different sets of data. Here is an example of a simple set of data which I will use to explain how VLOOKUP works.
Typically you would use this function in a reusable spreadsheet, such as a report template. Then every time someone entered a code it would retrieve all the needed information.
For this example, we will start off very simply. I’ll then show you how to make it useful over a larger set of data.
What to look up?
For example, if you were using the data above, you might use the VLOOKUP function to identify which piece of fruit is associated with a unique item code.
As How To Geek, concisely describes,
“if you put the VLOOKUP function into a cell and pass it one of the unique identifiers from your database, it will return you one of the pieces of information associated with that unique identifier.”
Then VLOOKUP will deliver a value to a cell – and give you the answer you were looking for.
In my example case, you could enter an item code, and VLOOKUP would return to you the corresponding item’s description. You could also look up its price, or its availability (its “In stock” quantity).
You decide which piece of information you’d like to retrieve and alter the formula accordingly.
Let’s break the formula into parts
VLOOKUP needs you to give it 3 pieces of information.
The value you want to look up LOOKUP_VALUE
2) WHERE FROM?
The table of data you want the information retrieved from. The ‘look up table’ TABLE_ARRAY
3) WHICH COLUMN EXACTLY?
The column containing information you want to bring back. The column number column COL-INDEX-NUM
There is a fourth piece of information, which I’ll come back to later
4) TRUE or FALSE (I’ll explain in a moment.)
Let’s do a VLOOKUP
Let’s say that we have a long list of item codes and we want to look up specifics pieces of information for the codes.
We want to look up the item description for the code highlighted. The cell H11.
The next step is to select the cell where we want the information we retrieve using VLOOKUP to be stored. K11 in this case.
Next go to the Formulas tab and click, Insert Function.
Now select VLOOKUP from the list.
Now select VLOOKUP from the list.
The Function Arguments box appears, asking for the arguments (or parameters) needed in order to complete the VLOOKUP function. These are the three pieces of information I mentioned above.
Let’s enter the first argument
We want to look up the item code in cell H11, so we select that. Press Enter.
Now we want to go onto the next argument, the Table_array. Remember, this is WHERE FROM?
Now select the look up table, not including the header line. Press Enter.
Now we want to select the third argument WHICH COLUMN EXACTLY?
This argument helps us specify which piece of information from the database VLOOKUP needs to find to associate with our item code in H11.
In this example we count the number of columns. This table had 4 columns, but the information we want to retrieve is in column 2.
This means that we must enter a value of “2” into the Col_index_num box.
Now we come to the fourth piece of information. TRUE or FALSE.
TRUE will look for the value closest (but always below).
FALSE will look for an exact match.
In this case, because we’re looking up the name of a piece of fruit, we want EXACT MATCH. Enter False.
Pear is correctly revealed!
IMPORTANT: HOWEVER, this formula needs to have $ signs added to be really useful…
IMPORTANT: Using the $ sign will make it more useful
ON IT’S OWN THIS FORMULA ISN’T USEFUL for the whole main table. We want to be able to drag it down to apply to the rest of the table.
If all you need is one piece of information from the database, such as in the example above, it would be a lot of effort to go to to construct a formula with a VLOOKUP function in it.
If we were to drag this formula down now, it would try to increase in numbers as it goes down the spreadsheet. This means that Excel would try to work out where the VLOOKUP table is, but get it wrong.
We need to tell the formula that we only want it to look up from that one set ‘look up’ table – we want to make sure the formula STICKS to that section. Here is a more detailed explanation of how the $ signs work http://www.notjustnumbers.co.uk/2011/05/excel-tip-dollar-sign-in-formula-fixing.html
We need to use $ in the formula…
The $ signs need to go around the table array, the section we want to STICK. You can edit the formula by clicking straight into the cell. Here’s a close up below.
HOT TIP: Instead of typing in Shift+4 in the exact spots you want the $ sign you can just hit F4 within the formula (cursor needs to be on a cell range).
Now you can drag down to reveal all the other item descriptions.
One more time!
Let’s add a quick VLOOKUP for price too.
Add a column for price and make sure you set the format to currency. You can do this through the Home tab. See image below
Now let’s type in the formula, instead of doing it through the tab.
1) WHAT? LOOKUP_VALUE
We’re still looking up the item code, H11.
2) WHERE FROM? TABLE_ARRAY
It’s the same table but let’s add $ around the column letters so it sticks.
3) WHICH COLUMN EXACTLY? column COL-INDEX-NUM
It’s column 3 that contains the price.
FALSE – again we want an exact match.
Drag it down, and hey presto!
And that is how you use VLOOKUP.
Look how much money I spent on fruit today!
If you’re still having problems getting the function to work take a look at this handy post, Top 3 Reasons Why VLOOKUP Isn’t Working.
Looking to brush up your Excel skills further? Our team of analysis find Chandoo.org incredible useful.
If you find putting together reports in Excel is taking up a lot of your valuable time, contact us, and we can show you how you can harness the data collection processing power of Trackpal, with the reporting capabilities of Excel, to make your data collection and reporting life easier.