The VLOOKUP is part of a series of lookup and reference functions that allow you to pull information based on data in a particular row. VLOOKUP is my most used function in Excel as I am constantly working with separate data sets that I need to join together to pull over relevant information.
You have a spreadsheet with an Employee ID, Name, and Hours Billed per month. You have a second spreadsheet with Employee ID, Name, and Address. You need to bring this address over to the first spreadsheet in order to send these employee’s a paycheck based on their billed hours.
If the list of employees is small, you could filter by Employee and copy over each address individually; however, this is time-consuming and prone to user error. Let’s write a Vlookup formula instead to bring address over.
For Easier viewing, I have combined both sets of data into one sheet. In my experience however, you will get sent this information via two separate Excel workbooks. You could also combine these data sets via a query, but this type of access is typically restricted to IT and those in an Analyst type role.
Below is our starting point. We will be putting the Vlookup formula into column F to bring over address.
If you would like to follow along, please download the file attached below.
Writing a VLOOKUP
- Label Column F as “Address”
- Hit the
- Search for Vlookup and hit go
- Select Vlookup and hit OK
Your screen should look like the below
This is going to be your Employee ID, Column A.
Since we are placing this formula in cell
F2, you need to reference the ID in
A2 or left mouse click on cell
This is going to be the set of Data or “Range” that you want to find your address in; in this case,
Using the lessons learned in Navigate and Select Data, you can either:
- Type in
J2with your mouse and drag the selection over to
J2and press c
trl+shift+downarrow and then
- Select columns J through M or
For the purposes of this exercise, I will do the last option and explain why further on.
This refers to the column in your Table Array that you want to see. In this example, Column M.
M is the 13th column on the spreadsheet, but it is the 4th column in your Table_Array so we will type in 4 to reference the 4th column.
This refers to whether you want Excel to find an exact match “0” or an approximate match “1” I have never found a use for the latter so I always specify 0 in the box.
- Hit OK to complete your formula and you can see that it has brought over an address for Doe,John.
- Double click or drag down the black box in the bottom right corner of cell
F2. Your cursor will change to a black
+sign to let you know that you are on it
Your screen should look like the below.
In the next post, we will go over ways to handle the
#N/A formula errors as well as best practices for Vlookups.