VLookups to Merge Spreadsheets to Send Paid Time Off (PTO) Balances

Have you ever been looking at a spreadsheet and noticed you were missing a column or two of information that would make all the difference?  Then you notice that information is on another spreadsheet, but there’s so many rows, it would take days upon days to go through and match yourself to have all the info on one spreadsheet?  Keep reading!  This blog will show you how to use the Vlookup in Microsoft Excel and Google Sheets.

Follow these quick steps to merge your sheets or tabs.  This works with any set of data you export to a spreadsheet from any system.

Sample Business Case Example and Instructions 

Let’s say you are given a spreadsheet with all the employees in the company.  However, it’s year end and your boss tells you to email each one with their paid time off (PTO) balance and time clock totals for the year by 3:00PM.  You say okay, except there’s one problem.  You look at the spreadsheet you have and it lists their home address, not their email address, and on top of it, these emails are personal, you cannot make a mistake in who gets which email and there’s no mass email option.  The file you have with their email has their employee number only.  You will need to take each file and merge them together somehow.  That’s going to take forever to try to match those up.  But wait, it doesn’t have to.  You can do this with a Vlookup in seconds.

A Few Notes…

  • A Vlookup requires a unique value to match to that is in both files/tabs.  This means that employee number could be used in this example because it’s unlikely that any other employee will have the same number, so this is a unique value.  If you tried to use an employee first name for example, you may have a lot of Bob’s and so, the match will not work as Excel can’t tell which Bob has which email.  
  • You also have to have something common between the spreadsheets.  In this case, the employee number is listed on both tabs, so this will work!
  • This example is a handful of records to show for simplicity, this function works with 1,000s of rows.

Using Microsoft Excel or Google Sheets

Link to Google Sheets example here 

  • Open the file and click on the “Employee Home Address” tab.
  • We are pulling in the “Employee Email” from the other tab, so click on cell F1 and name it “Employee Email”.

 To manually walk through the steps of the formula:

  • Click right below F1 to start, in F2 and type =vlookup(


Vlookup Example

  • Highlight column A by clicking on “A” for the “look_up value” and enter a comma ,.  This is the column you want to match to.
  • Click “Employee Email” Tab.
  • Highlight column “A” and “B” by clicking on “A” and dragging the mouse over to “B” for the “table_array” and type a comma ,.  This where the value is you want to bring over to your master sheet.
  • Enter the number 2, and type a comma , for the “col_ind_num” because you then have to count how many columns over the field is to match, so “Employee Email” is in column “B”, which is 2.
  • Enter 0 and close the brackets by typing a ) at the end to return results.
  • Hit enter.  This can be tricky, so an alternative is below.

To instead type the formula in rather than clicking step by step:

  • Enter the formula. 
    1. In Sheets, it should look like this: =vlookup(A:A,’Employee Email’!A:B,2,0)
    2. In Excel, it should look like this: =vlookup(@A:A,’Employee Email’!A:B,2,0)
  • An email address will appear now.  When dealing with 1000’s of rows, it’s always good to go back to the other tab to spot check one or two to ensure it’s correct.  The spot check looks good, you can see employee 158 is Steve, steve123@ontheclock.com.
  • Click on the tiny square at the lower right corner of the highlighted cell to drag down the results for the rest of the employees, or double click on the tiny square to get the same result.

Results

You now have your email addresses along with the employee name so you can get started sending those emails.

 

Leave Your Thoughts...

(required, will be shown)
(required, will not be shown)