Want to get good at using spreadsheets (like, really good) while saving yourself precious time? Learn how to use the tool vlookup. You may have heard of vlookup. Its name is short for “vertical lookup,” and it’s the key to truly understanding spreadsheets and big data.

PR professionals use spreadsheets for a variety of purposes, but many default to manual data entry tasks when they could save hours of valuable time with functions like vlookup. 

Let’s say you want to combine data from multiple sources, like adding supplemental information from one spreadsheet into a master sheet. This could include, for example, combining multiple media lists into a single master list, digging through data across several spreadsheets to find news hooks, or simply managing a series of projects that involve lots of people and tasks.

Don’t waste time; use vlookup instead.

Despite its odd name, vlookup is just another function built into common spreadsheet programs like Excel and Google Sheets, just like the ‘Average’ or ‘Sum’ functions. But vlookup is a far more powerful tool that will help you combine data from multiple sheets.

How to Use the Vlookup Function

So … how does vlookup work, and how do I use it?

Think of vlookup as your personal scent hound. First, you give it the scent of your quarry, then let it track down and return information to you. It looks like this:

=vlookup(Search_Key,Range,Column,Is_Sorted)

It looks incomprehensible, I know. But this exercise will help you go through each individual piece and see how it works.

For now, let’s say there’s a table you use as a media contact list, something like this:

But you’re tired of having to look up client contacts from a DIFFERENT sheet in the same file…

You can combine these separate pieces of data by using vlookup to bring information from one sheet into the sheet you’re actively working in.

How? Follow this exercise, which shows you how to unlock this wonderful mystery in just a few simple steps:

1. First, go to this exercise sheet, and make a copy for yourself.

2. Second, create a column to receive your vlookup function (for this exercise, use column C, which we have named ‘Phone #’).

3. Then, enter the vlookup function into cell C2. Remember from the beginning of this article that vlookup requires four pieces of information:

 =vlookup(Search_Key,Range,Column,Is_Sorted)

In this jumble of code, you’re telling the function what it’s looking for (‘Search_Key’), where it’s looking for it (‘Range’ and ‘Column’), and what kind of matches you want (usually you want an exact match, so set ‘Search_Key’ to ‘FALSE’).  

1. • Search_Key – this is the “scent” that your hound will use to sniff out a match in the first column of the range. For this example, choose a client in the task list (cell A2).

• • Range – these are the fences within which your scent hound will limit his search. For this example, select the Client Contacts sheet, columns A through C.
    • • See below in the example how this range is built from two parts, an optional sheet reference ‘Client Contacts’! and non-optional column references A:C.
      • Importantly, if your range is columns C, D, and E, then your scent hound recognizes these as columns 1, 2, and 3, respectively.

1. • Column – This is the column from which your scent hound will retrieve information. For this example, choose the third column (3). In some spreadsheet applications, ‘Column’ is instead called ‘Index’.
   • Is_Sorted – This is a TRUE or FALSE value. Setting ‘Is_Sorted’ to FALSE is usually recommended, because it informs your scent hound that he should only retrieve exact matches, rather than vaguely similar ones.

As you begin typing in the function, your application should give you guidance on how to continue.

With this properly configured, vlookup will match what is in the yellow-outlined cell to the contents of the left column of the purple-outlined range. Once it finds a match, it will go to the column that you choose and return the corresponding information. In our example, the process would find a match in cell A5 and go to the second column in the range (column C, same row), returning the phone number: 555-0004.

Copy that function all the way down the column – you can use cmd-c (ctrl-c in Windows) to copy cell C2, then after selecting cells C2 to C5, cmd-v will paste your function into all of these cells.

And now, you’ve got phone numbers in your sheet!

You could repeat this process, adding another column for outlets, or you could insert two pieces of information into column C like this…

Which produces this:

An ampersand (&) allows you to string together multiple functions, and placing the comma and space inside the quotes (“, “) cleanly separates the output from each of the functions.

What’s especially great, is that if you update the outlets or phone numbers in your Client Contacts sheet, those changes will automatically feed through your vlookup functions into your Media Contact List.

Final Words

Vlookup is my favorite spreadsheet function. I use it daily, and without it I would not have the understanding of spreadsheets and data that I do today. If you design your sheets with vlookup in mind, your spreadsheets will be easier to read and easier to maintain and will make pivot tables easy to create. But that’s for another time …