When you're faced with doing a repetitive task in a spreadsheet, there's a key question you should always ask yourself:
"Could I imagine a robot doing this task instead of me?" That is, does it seem like there should be a way to automate what you're doing?
If the answer is 'yes', there's a good chance that it is possible to build a spreadsheet formula that will automate your repetitive task. While there are lots of obscure functions in spreadsheets that can help you automate tasks, VLOOKUP is one of the most important and, often, the most overlooked. This article and video are going to give you an overview of the VLOOKUP function (which works the same in both Sheets and Excel, by the way) and some ideas for ways you can use it to automate repetitive tasks.
What is VLOOKUP and How Does it Work?
Remember telephone books? You'd have a name of a person or business in your head and you needed their phone number. You flipped through the pages of the telephone book and ran your finger down the left-hand column of names. When you found the name you were looking for, you then moved your finger to the right to find their phone number or their address.
VLOOKUP does the same thing with a spreadsheet. You give the function a value you're searching for, maybe a name or a number, and point it towards a 'phonebook' spreadsheet. The function will look for your value and, if it finds it, it will return the value of one of your value's adjacent columns.
For VLOOKUP to work properly, it needs four pieces of information; in spreadsheet functions, these pieces of information are called 'arguments'.
- The name you want to look up in your phonebook. This could be an actual name ("John Wick"), or it could be a reference to a cell (like B34) that has the information you want to look for.
- The location of the phonebook. Ideally, your phonebook will be a table of information that's in the same workbook, maybe on another spreadsheet.
- The information that you want VLOOKUP to return with. Do you want the person's address, or do you want their phone number? VLOOKUP needs the column number for where to find this information. If phone numbers are in the D column of your phonebook, then you need to tell VLOOKUP you want the information from column number 4.
- Whether you want VLOOKUP to find an exact match or an approximate match. 9 times out of 10, you probably want an exact match, so you need to type FALSE...don't ask me why.
VLOOKUP in Practice- Finding Addresses and Phone Numbers
Let's illustrate how to use VLOOKUP with an example. Say you have a list of donor information from 2015 that includes addresses, phone numbers, and email addresses. In this example spreadsheet, this information is recorded in the '2015 Donors' spreadsheet. Now, let's also say that you have a list of 2016 donors, but you don't have any of their contact information. You want to see if: a) they donated to the organization last year and b) if so, how you can contact them.
Now, of course, I could do this by hand. I could start with Judy, then comb through the list of 2015 donors looking for her. If I find her, I can copy her phone number, address, and email. But doesn't it seem like a robot could be programmed to do this job? Enter VLOOKUP. In cell B2, where Judy's phone number would be, I'm going to write out my VLOOKUP function. You can see that Sheets helpfully starts giving me some advice about what it needs to make the function work:
1. First, we need a search key. This is the name 'Judy', so I want to type A2 as the search key, since that's the cell with Judy's name in it. We could just write in "Judy", but by pointing VLOOKUP at a cell, it makes it easier to copy and paste this function later.
2. Next, we need a range. This is the 'phonebook', the spreadsheet with my 2015 data. I'm going to navigate to the 2015 donor spreadsheet, then select the range of cells that I want to use as my phonebook.
3. Next up, VLOOKUP wants an index. This is the column I want to pull data from in the 'phonebook'. In this example, I want the donor's phone number, which is column B of the phonebook. Since B is the 2nd column of my 'phonebook', I type '2' into the function here.
4. Finally, we come to the fourth argument of our function. Just write 'FALSE' and trust me on this. You only want to write 'TRUE' if you're OK with Sheets looking for names that are kind of similar to 'Judy'. In my experience, Sheets is pretty broad in what it considers 'kind of similar'.
5. That's all we need to do to make our function work. Now, we just close the parentheses and press Enter. And it works! VLOOKUP goes to the 2015 Donors tab, looks for the name 'Judy', finds her, then comes back with her phone number.
Making it Copy-Able
Now, ideally we want to copy and paste the function we just wrote into all of the neighboring cells. This gets a little more complicated and I'm not going to cover all of the related concepts here (though check out this article if you're curious). For now, let's just compare the function we wrote and the changes we need to make so we can copy it:
Original Function: =VLOOKUP(A2,'2015 Donors'!A2:G30,2,FALSE)
Copy-able Function: =VLOOKUP($A2,'2015 Donors'!$A$2:$G$30,COLUMN(B2),FALSE)
The main differences are the use of Absolute References (denoted by the dollar signs) and the Column function. These two changes allow the VLOOKUP function to be copied to every cell in our table of 2016 donors. And here's what we get:
This is pretty amazing; Sheets just did at least a half hour of work in half a second. Now, that may not seem like a huge time savings, but remember that this list could have had 200 names or 1,000 names and it would have worked just as well and taken just a short a time to calculate.
Notice the #N/A entries for Mattie and Suzy. If you hover over one of those #N/As, Sheets will tell you what's going on:
VLOOKUP can't find Mattie in the phonebook. In this example, she's probably a new donor. Or, it's possible VLOOKUP has made a mistake. This leads us to...
VLOOKUP Gotchas!
There are two main limits of the VLOOKUP function that you'll need to keep in mind:
- Your lookup values need to match EXACTLY with the data in your phonebook. If your lookup value is "Joe Bloggs" for instance, and there's an entry in your phonebook for "Joey Bloggs", VLOOKUP won't consider that a match. Be especially careful of nicknames and spaces, which will throw off your results.
- Your lookup value needs to be the left-most column of your lookup table or phonebook. VLOOKUP just straight-up doesn't work if you try and search for a value in any other column of your range. Keep this in mind when organizing your 'phonebook' table.