Microsoft Office Tips: How to Use VLOOKUP in Excel

Excel is a pretty amazing piece of software. On the surface it looks like a basic spreadsheet application, but just below this is a wealth of tools that can make even the most complex and time-consuming jobs easy.

Take, for example, the VLOOKUP function. This amazing little tool can take seemingly meaningless data like model numbers or employee IDs and magically turn it into information we can read, such as names and email addresses. Instead of copying and pasting the correct email address next to every name in a list, VLOOKUP can automatically assign the right email address to the right person. It doesn’t matter if your list has 10 entries or 10,000 – VLOOKUP can make it work.

Read on to see an example of VLOOKUP in action and find out how you can use it.

All you need to use VLOOKUP is a set of data to reference and a list that needs to reference that data. If you want to see the example in action, just download the example file used below by clicking here.

Note: The example below shows you the manual method for creating a VLOOKUP and is shown with Office for Mac. This works the same for Windows, and while there are tools to build this inside of Excel, it’s far quicker to understand how it works and build these functions manually.

RELATED: Hidden Tips & Tricks In Excel for Mac 2011

About VLOOKUP

VLOOKUP is an Excel function that uses a referential table of data to apply necessary information to another place. This data can be another table in the workbook you’re using, another table on a worksheet, or in another Excel file altogether. This means if you use Employee numbers, for example, you can have an employee Excel file and reference data from it inside of countless other Excel files, as long as they’re in the same folder.

So why is this useful? Let’s say you’re doing payroll. Most payroll applications only care about Employee ID or Social Security number, as each person only has one of these. But there can be multiple people with the same name. So, when you get your report it only has ID numbers on it. To make it readable you’d want to add names and possibly even departments. Instead of typing it by hand, you can reference it from a spreadsheet automatically.

This is best shown with an example, so let’s take a look at using VLOOKUP for employees.

How to Use VLOOKUP

The first thing you need is your source data. This is the sheet that has both the hard-to-read information like an employee ID as well as the easy-to-read information like Employee Name. We’ll call this the Lookup Table.

Microsoft Excel worksheet with source data

The VLOOKUP function uses four key pieces of information. This may seem imposing, but it’s actually pretty simple. For the sake of this tutorial we’ll do all of our work in one spreadsheet, but you can do this across Excel files in the same folder structure, too.

Using the file shown above, we’re going to make a new page named “Working” and rename Sheet1 to “Lookup.” We’ll pretend we have timesheet data that needs names and departments attached to it. This means there will be multiple entries for the same person. Here’s the data we’re starting with:

Microsoft Excel worksheet with new empty columns highlighted

The highlighted area is what we would like to fill in automatically with VLOOKUP. By referencing the data in the Lookup sheet, we’ll do this easily.

The arguments VLOOKUP needs are:

  • lookup_value
  • table_array
  • col_index_num
  • range_lookup

You can start the VLOOKUP function by typing =VLOOKUP in the cell you want data to show up in. For our example above, this would be cell D2. Excel will start to auto-format your formula, but just keep typing, as we’ll show you next what goes in here.

Duplicate contacts killing your productivity? See how Scrubly can help in this 100-second video.

lookup_value – This is the first part of the argument. This is the information you will be using for reference. In our example this is the Employee ID field. After typing =VLOOKUP into cell D2, our next step is to click on the employee ID in cell B2. This tells Excel that the employee ID we want referenced is there.

table_array – This argument refers to the range of data you want Excel to check. For our example, we need to click on the Lookup tab and then highlight the area we’re referencing. There are a couple of key rules that need to be followed here.

1. References to the Left – The left column must have the values being referenced. You’ll notice in our example at the top of this page that Employee ID is listed in the first column of the Lookup tab. In other words, you couldn’t place the name columns first.
2. No Duplicates – The column you’re using for reference can’t contain duplicates. Since this shows a list of employees, you should never have two employees with the same ID.

col_index_num – This argument refers to the column number on your lookup table that has the information you want displayed. Looking at our example, we want the employee name, which is stored in the fourth column on the lookup sheet. If you just wanted the last name, you’d want column #3.

Microsoft Excel worksheet with data filled in

range-lookup – The last field asks if you want a close match or an exact match. For this example we do indeed want an exact match, so the value we’ll enter is FALSE. More often than not, you’ll want to use FALSE here.

So, here’s what the formula looks like for our example:

=VLOOKUP(B2,Lookup!$A$2:$E$8,4,FALSE)

Important: You probably noticed the additional dollar signs “$” in the example above. These are used to tell Excel that the values are absolute. If you don’t use them, your VLOOKUP will get wonky really fast. Don’t worry too much about them and just add them in. You’ll be glad you did.

Excel uses the Employee ID on the Working sheet to match up on the Lookup sheet and grabs whatever data we have in the fourth column. In our case that’s the Employee Name. The result is that the employee’s name shows up.

To copy it to the rest of the cells in your table, just hover over the bottom right corner of the cell you just worked in and your cursor should turn into a small black plus sign. Click and drag down to all the cells you want filled. When you release, all the names should fill in automatically.

Microsoft Excel worksheet with name highlighted

To fill the Department column you would use the same formula, but this time you would reference column #5 for your col_index_num argument. When you’re done, your sheet should look like this:
Microsoft Excel worksheet with completed VLOOKUP data

There are countless reasons to use VLOOKUP, from data validation to making sheets that a human can easily read. Anytime you need to fill in data en masse, VLOOKUP is your best friend.

If you’d like to learn more about VLOOKUP, check out Microsoft’s support article on the topic.

Scrubly social-sharing menu.

Scrubly sign-up.