Excel Tips and Tricks – Using the VLookup Function

In our last Excel Tips and Tricks article we discussed how to create a PivotTable. In this edition, we are going to explore another powerful tool, the VLookup function. For this example, we are going to revisit our dataset from the last article, which showed us each of our employees, their department, their location, and their 2016 bonus.

Now let’s assume that management has requested that we also add 2016 salary to this table to assist in their analysis. Because this is a small data set, we could just manually key in the salary numbers by looking them up in our payroll system. However, if we had hundreds of employees, this would be a very time-consuming task. The VLookup function can automate this process for us. The finance department can download a list of 2016 salary for all employees, as follows:

As you can see, it is not in the same order as our first table, and in many cases, it might not have the same number of rows as our other table. To get the salary information into the first table, we can make another column to the right of the Bonus column in the first table. We will label this column Salary. In the first cell under the Salary column, we enter the following formula:

=VLOOKUP(A2,$H$1:$I$12,2,1)

We can also enter this information from the Function Arguments screen by going to Formulas on the ribbon bar, selecting “Lookup & Reference”, and then “VLOOKUP”.

We would then copy this formula all the way down the Salary column.

So what exactly are we telling the function to do based on what we entered? Let’s look at each line:

• Lookup Value: We entered A2 to tell the function that in our second table, we are looking for the salary of the person in cell A2 (Marc). Note that when we copy this formula to the cell below, it will change to A3 so that it will then look up Eric’s salary and show that in the row with his information.
• Table Array: Here we are specifying our second table where we are looking up the value. In this situation, the table with two columns showing just employee and salary (see above) are in these cells. This essentially is where we are going to “look up” the information we are trying to add to our original table.
• Col Index Num: This specifies which column in the “look up” table (that we just specified in the Table Array) that we want to pull into our original table. We are trying to put the salary into our original table, and the salary column in our second table is the second column. Therefore, we enter “2”.
• Range Lookup: Here we are entering “False”, because we are looking for an exact match to our lookup value. Because we know the names in both tables match exactly, we can choose to only look for exact matches.

Our resulting table is as follows:

We have now used the VLookup function to add the salary data into our original table, and we now have the report requested by management without having to manually combine or enter any information.