Creating Excel Reports Part 3 – Using Data Tables and VLOOKUP

Out of all the tutorials in this series this one is perhaps the most important, so be sure to pay close attention and put good effort into fully understanding VLOOKUP. This command (along with its sister command HLOOKUP-- to be covered in a later article) alone will make you a much more efficient Excel programmer. While I consider them essential for anything besides the most basic of reports, they are also useful for all kinds of data manipulation and even sometimes for quick analysis. These commands are used to search thorough and look up data in a given table or range. Once it finds the data specified, it will output any other value that is part of the same row adjacent to it. The easiest way to visualize this is by example:

Step 1 – Set-up your Data Tables

Similar to the first step of the previous tutorial, we need to create a new sheet. This sheet is going to be used for storing data tables containing employee contact numbers, rate of profit per hour for each vehicle (revenue), and rate of pay (expense) per hour. These tables need not be complicated, just one column for the independent variable and one or more for the dependant. Take a look at my tables below, the entries are just plain data, no formulas whatsoever.

Now that they are made, we need to create a label for each of them. To do this, simply highlight each table one at a time, then type in a unique label in the name field in the top left field and press enter.

Once you have all of your data tables named, click the drop down menu next to where you filled in the names to ensure that all of the names are there.

 Step 2 – Getting started with VLOOKUP

Do not be intimidated by the programming nature of VLOOKUP or HLOOKUP, they are simple once you break it down into small pieces; I’ll go into great detail the first time around. They are just formula that get entered into a cell, just like the SUM(a,b,c,….) or COUNT(a,b,c….) that you have likely used before. Here is the formula structure of VLOOKUP() taken right from Excel when you first enter it:

Lookup_value: Self explanatory, value (or cell) you want to search a data table for.

Table_array: The table that you want VLOOKUP to look though; enter the name that was used in step 1.

Col_index_num: Column index number, specifies which column of the table the data should be pulled from when VLOOKUP locates the lookup value.

[Range Lookup]: Don’t worry about this one; just make sure you always enter FALSE for its value.

Now that we know what each component means, let’s try a real example and begin programming the report. I want to make the position of an employee and their contact number to automatically fill themselves out in our Shift Data sheet after the employee’s name is entered. Lets start with the position first. I highlighted the position and contact columns blue in order to show that data is automatically entered in these cells rather then by the user.

Let’s put the formula into the first position cell:

As you can see, the correct formula is =VLOOKUP(B2, EMPLOYEES, 2, FALSE)

“B2” is the cell name that contains our lookup value, the employee’s name. “EMPLOYEES” is the name I gave my table back in step one, and “2” is the column index of the “position” data in the table.

Try entering an example of your lookup value, to make sure everything works properly. Next we are going to do the exact same thing for the contact number column, however the formula will be =VLOOKUP(B2, EMPLOYEES, 3, FALSE). Notice the only difference is “3”; the third column in our employee table contains the contact numbers. Now drag down both of these formulas all the way to the bottom of the sheet. Don’t worry about the #N/A that will appear, it just means that the lookup value (blank) wasn’t found.

The first part of the report is now fully automated. This reduces the amount of data that needs to be entered on a daily basis, increasing data entry efficiency and speed. Another major advantage of using data tables with VLOOKUP should now be noted, if a constant such as someone’s phone number changes, all that is needed to update the report is to change the number in the data table, the old numbers will all be updated automatically. If you still have problems using VLOOKUP please consult the Excel Help files, or search on the web for more resources.