Essential Excel Concepts: Lookup Functions

During each day in the corporate world, you will be assigned tasks using Excel that you should feel comfortable completing. One of those essential tasks is using the VLOOKUP function to retrieve values needed from external data tables or worksheets.

The most common scenario where you will use VLOOKUP is when you will have data in two or more tables that have a mutual column shared between. An example of a shared column is Employee ID for example. Typically, you will want your shared column to be unique identifiers to help ensure the records you retrieve are accurate and reliable.

A unique identifier is a numeric or an alphanumeric string that associates or maps a string to a single item/entity in a system. For example, a jersey number in a sports game is a unique identifier to help the referees and fans identify players. We do not want two number 88s on offense at a time--that would be confusing.

Below you will see two tables that share a unique identifier called Employee ID. What also do you notice regarding the tables below? Figure A does not have Hire Date and Work Type columns as Figure B does. This is a common occurrence when working with different tables. However, this gives us an opportunity to use VLOOKUP ().

Figure A

Figure B

Next, we will look at a scenario that occurs frequently in the business world where VLOOKUP can be used to help resolve.

Task:

During your morning team meeting, your team leader assigns a task for you to pull in missing Hire Dates and Work Types from multiple tables within an Excel spreadsheet containing two worksheets (East Side Data, All Company Data).

The expectation is that you complete this task within the hour, so your team leader can use the data for a project for senior leadership.

Solution:

For this task, VLOOKUP will be perfect to use. However, before we begin, let us spend some time discussing VLOOKUP at a high level and the VLOOKUP syntax.

Recall, VLOOKUP (“Vertical Lookup”) is a powerful Excel function that searches for a value between two or more tables where there is a shared column. In our task, we will pull in values that correspond to our unique identifier, “Employee ID.”

I will talk about syntax now for VLOOKUP. But do not get discouraged if it does not make sense at first. As you practice using this function and follow through this this lesson, you will be more comfortable.

Syntax:

=VLOOKUP (Lookup Value, Range that Contains the Lookup Value, Column Number in the Range Containing the Return Value, Match Type TRUE or FALSE)

Note, in the syntax above the Lookup Value will be our unique identifier. The Range that Contains the Lookup Value will be our table to retrieve data from. The Column Number is simply the numerical value for the column we are after in our retrieval table (1, 2, 3, …). The Match Type will typically be always FALSE, which means we want an exact match.

Step 1:

We need to add two additional columns in the table in the “East Side Data” worksheet. Let us add a “Hire Date” column next the “Employee ID” and “Department” columns respectively.

To do this, just select column B (“First Name”) and right-click “Insert” to add a new blank column. A new column will be added to the left of column B. Note, in Excel, columns are added to the left of the column you right-click—you will get used to this.

Update the name to “Hire Date.” Do the same to add “Work Type” right beside the “Department” column.

When finished your table should look like the example on the next page.

Step 2:

We will now pull in the data we need from the All Company Data worksheet. First, we will start with pulling in the Hire Date.

Click your cursor in cell B2 in the newly created Hire Date column in the table in the East Side Data worksheet and begin typing =VLOOKUP. You will need to use “=” when using functions in Excel. As you can see, Excel will automatically add the criteria inside the VLOOKUP function (as well as all other functions), which is extremely useful.

Now we will just add in the criteria needed to pull in the hire dates.

For the first criteria, we will just need to click into A2 while in the “East Side Data” worksheet, so Excel can understand that we want to use “Employee ID” as our lookup value. Conversely, we could also just type in A2 rather than clicking, but this is personal preference.

We will now add in the information for the table_array criteria. Recall, we defined table_array as the Range that Contains the Lookup Value in our syntax section from earlier.

Since our tables are in different worksheets, we will need to click the All Company Data worksheet and select the entire table.

Note, your lookup value needs to be on the left of the table you are retrieving data from. The values we will be pulling in should always be on the right of the lookup value to use the VLOOKUP function successfully.

Since we are wanting to pull in the Hire Date data from the table on the All Company Data worksheet, we will pull data from column 3.

The last thing we will need to add as a criterion is match type, FALSE. Then close the parenthesis and hit enter.

The values should now be pulled into the table in the East Side Data worksheet.

Do you notice something off? There are numbers pulling in and not dates. Well, technically, this are date, but in number format. For example, 39700 is really 9/9/2008.

This formatting is quickly resolved when we select the entire Hire Date column and format to Date in the Excel Home tab.

See the following page for the update in formatting on the following page.

The result will be dates. I used the Short Date formatting, but you can use whichever date format of your choice,

We will now do the same thing for pulling in the Work Type data from the table in the All Company Data worksheet into our table in the East Side Data worksheet.

Click your cursor in the newly created Work Type column in the East Side Data worksheet. Type =VLOOKUP () as before. Use the same lookup value for “Employee ID” and the same lookup table from “All Company Data” worksheet.

The column to pull will be the only criterion that is different the before, as we will use 4 rather than 3 since we are after the Work Type data.

Now we have all the values added into our table in the “East Side Data” worksheet we need for our team leader.

Remarks:

Before we send this updated spreadsheet to our team leader, we should verify the data is accurate that we pulled into our “Hire Date” and “Work Type” columns. To be accurate, I am not referring to if the data within the “All Company Data” worksheet is correct. However, I am referring to if our VLOOKUP pulls are retrieving what we intended them to do.

There are a few ways to verify. Here are two ways you can do this on your own. However, note, there are more ways to do this.

1. Conduct a random sample of “Employee IDs” to verify the “Hire Date” and “Work Type” matches in both tables.

   
   

• Using 11062 as our test, we can see that the values match in both tables:

2.       Filter the “All Company Data” table to only show results for “East Side” in the column named “Location.” Then sort the results in the “Employee ID” column to ascending order:

Now do the same for the table in East Side Data.

Note, the Hire Date and the Work Type are matching (or 1:1).

Another way is using a logical test by inserting another column (Helper Column) in your table to test whether the values are the same from the other table.