I’m celebrating 100,000 YouTube views with an article on my favorite spreadsheet function: vlookup!

Vlookup is for merging and combining datasets, like when you’ve got program participants’ demographic data in one place and their scores or outcomes in another place. I can’t tell you how many times I’ve watched researchers and evaluators waste hours of their time by trying to merge these spreadsheets manually (e.g., by copying and pasting back and forth). You’ve got better things to do with your time, don’t you? Vlookup to the rescue!

In the example below, I had demographic data for 430 people in one spreadsheet and their mental health counselors’ rating of their progress for 137 people in a second spreadsheet. I knew I was supposed to have data for 460 people total. I couldn’t simply copy and paste columns of data together because, in real-life analyses, each spreadsheet contains a different number of people. (Don’t you just love missing data?) This magical formula allows you to combine those different spreadsheets, tabs, or data tables into a single master table that you can use for your  analysis.

vlookup

A disclaimer: vlookup has a learning curve. I first learned this function from Agata Jose-Ivanina when we were working together back in 2008. Bless her heart, she explained and re-explained the function to me a million times. I practiced on my own, and finally–probably a full month later–I got it. So don’t worry if this function seems tricky at first. Eventually everything will click in your brain, and you’ll be on your way to saving hours and hours of your time.

The function contains four pieces of information. It looks like this: =vlookup(lookup_value,table_array,col_index_num,range_lookup). And here’s what each piece of the function really means:

  • lookup_value: The cell that contains the person’s ID number. The ID numbers are the link or key that connect all the spreadsheets together. The ID numbers must be located in the first column of each table – in the first column of your new combined dataset and in the first column of every single table from which you’re pulling data.
  • table_array: This is the table from some other spreadsheet or other file from which you’re pulling data.
  • col_index_num: This is the column in that table where you’re pulling the data from. Just type in the number of the column. For example, if you want to pull in data from Column C of another table, you’d type “3.”
  • range_lookup: Always type “false” and you’ll be in good shape.

Check out these three step-by-step tutorials to learn more. I’ve purposefully repeated content through the tutorial series so that you can hear the information multiple times in multiple ways.

When to use =vlookup():

Why to use =vlookup():

How to use =vlookup():

 

Comfortable with vlookup? Apply time saver #1 and time saver #2 to take your spreadsheet to the next level. Then, try your hand at hlookup. Hlookup is essentially the same as vlookup, but the data table is rotated on its axis: vlookup is for vertical data tables (the ID numbers go up and down in a column) and hookup is for horizontal data tables (the ID numbers go left to right in a row).

Want to practice these skills on your own? My Patreons can download the spreadsheets used in these videos at www.patreon.com/annkemery. Practice, practice, practice, and then enjoy your newly-found free time!