How to Use VLOOKUP for HR Analytics and Reporting

Excel may not be the most exciting topic to start a blog, but there’s one function that can revolutionize the way you work with data: VLOOKUP. Understanding how to use VLOOKUP is an essential skill for any HR professional who wants to leverage technology to streamline their work and provide data insights. In this post, I’ll guide you through the basics of VLOOKUP and show you a practical example of the formula for a common HR request.

VLOOKUP is a powerful function in Excel that allows you to search for a specific value in a table or range of cells and return a corresponding value from a different column in that table. This function can save you time and reduce errors by automating data lookup and retrieval tasks. For example, if someone gives you a list of Employee IDs and they want additional fields like email address, primary job title, department, etc. VLOOKUP allows you to easily connect that additional information to each Employee ID without having to manually go through a list and enter data.

To use VLOOKUP, you need to specify four arguments:

1. Lookup Value: The value you want to find in the first column of the table or range. In our example case, this will be the employee ID as we are matching it from the list provided to the source with the fields you want to pull.
2. Table Array: The range of cells that contains the data you want to search. This is the data source that you are pulling from. For example, an employee extract from your HR system with employee IDs, names, addresses, and job titles.
3. Column Index Number: The number of the column in the table or range that contains the value you want to return. We’ll look at this in a second.
4. Range Lookup: A logical value that indicates whether you want an exact or approximate match for the lookup value. I have never used anything but FALSE in this field.

Once you’ve specified these arguments, Excel will perform the lookup and return the corresponding value from the specified column.

Let’s look at an in-depth example. For the demonstration, I will be using a sample dataset from Microsoft called Northwind. Their original file only had 10 employees, so I have added some more data to allow for more robust examples. (This data is all randomly generated and does not reflect actual names or addresses of real people. This is also why there is such a random assortment of job titles). If you want to follow along, you can download the data set here.

In this scenario, you are given a list of 12 employees that are receiving a special service award. The person provides you with a list of names and employee IDs and wants you to provide their email addresses.

Here are IDs and names in list format so you can practice (when you paste into Excel, make sure to choose the Match Destination Formatting option when pasting:

ID	Name
110	Camila McClain
122	Tamara Beck
123	Lesly Larsen
72	Peter Ayers
31	Wesley Short
74	Freddy Cordova
41	Charity Neal
13	Charity Juarez
12	Ann Knight
104	Judith Chase
135	Bailey Dominguez
105	Lara Jennings

To use VLOOKUP to find the email addresses for the employees receiving the service award, follow these steps:

1. Open the Sample Employee Table worksheet set in Excel.
2. Copy and paste the list of employee IDs and names (you don’t necessarily need the names to do your VLOOKUP but it will help with verifying the data) provided to you into a new worksheet in the same Excel file.
3. Insert a new column heading next to the employee IDs and names column, and name it “Email”.

4. In the first row of the “Email” column, enter the VLOOKUP formula: =VLOOKUP(A2,Employees,5,FALSE).
   • Here, A2 is our lookup_value, the employee ID from the list provided that we want to match against our own employee data. You are telling Excel to search your Employee Data for any values that match the value in A2.
   • Employee is the table_array. This tells Excel where to search for value A2. In this case, the entire employee data set (excluding the column headings). Excel is matching against the left-most column in this array so it is important that you have the IDs in the left most column.
   • 5 is the column number in our employee data set that includes the email addresses.
   • FALSE indicates that you want an exact match for the employee ID.
   • What you are telling Excel is “I want you to find the exact value in A2 (in this case 110) in the worksheet Employees in the leftmost column (in this case column A). Once you have found 110, I want to know what the value is in the 5th column over in the same row as 110 (in this case Camilla.Mcclain@northwindtraders.com).
5. Press Enter to calculate the result of the formula for the first employee ID. You should see the corresponding email address appear in the “Email” column.

6. Copy the formula in the first row of the “Email” column and paste it into the rest of the rows in the column. Excel will automatically update the formula for each employee ID in the list. You can verify that the email addresses match the names of the employees.

Now, you should have a list of employee IDs, names, and email addresses for the employees receiving the service award. This process can be used to retrieve other employee information, such as job titles, departments, and compensation data. Below you can download the completed file to see exactly what I did.

VLOOKUP is just one of many powerful functions in Excel that can help HR professionals streamline their work and gain insights from data. Learning how to use VLOOKUP effectively can save time, reduce errors, and enable you to provide more accurate and timely information to your organization.

Stay tuned for my next post where we will look at using Generative AI (e.g. ChatGPT or Bard) to help with formulas like VLOOKUP.