Advertisement
  1. Business
  2. Microsoft Excel
Business

How to Use the Excel VLOOKUP Function - With Useful Examples

by
Difficulty:BeginnerLength:MediumLanguages:
This post is part of a series called How to Make and Use Excel Formulas (Beginner Bootcamp).
Quick Start: How to Make a Basic Formula in Excel
How to Work With Excel Math Formulas (Guide to the Basics)

What is a VLOOKUP in Excel?

A VLOOKUP, short for "vertical lookup" is a formula in Microsoft Excel to match data from two lists. Instead of jumping between spreadsheets and typing out your matching data, you can write a VLOOKUP formula to automate the process.

Combining two lists is a a great situation to use VLOOKUP
Combing two lists is a perfect situation to use a VLOOKUP. 

On the left (in the image above), we have employee shift information. We want to add the employee's job title to the shift data. With a separate list of employees and their job titles, we can write a VLOOKUP formula to pull in the title from a lookup list.

A successful VLOOKUP needs three things:

  • primary key in each list that you can use to match your data up. The two lists need to share at least one piece of data in common (in the Excel VLOOKUP example above, this is the employee ID)
  • Lookup List, which contains your "database", or basically the information (the list of employee job titles)
  • Your data, which you want to pull a match into (the shift data)

Quick Example of an Excel VLOOKUP Formula in Action

VLOOKUP is a Microsoft Excel formula that's essential for working with multiple sets of data. In this tutorial, I'll teach you how to master and use it.

Example VLOOKUP formula used to look up employee data
Example VLOOKUP formula used to look up employee data.

Using the example above, I've now written a VLOOKUP formula that looks up the employee's ID and inserts the job title into the shift data. Because both sheets have an employee ID, Excel can lookup the matching job title. The best part of VLOOKUP is that I can now drag the same formula down and it will look up each unique job title.

Free Excel Spreadsheet Download 

Before we move on, make sure to Download the free Attachment to follow along. It's an example spreadsheet workbook that I created, which we'll use to walk through this tutorial.

Watch and Learn: VLOOKUP

For the fastest way to learn the basics of the VLOOKUP formula, check out the screencast below. The video walks through several examples of the VLOOKUP formula, using the example workbook.

 

Keep reading to walk through the written instructions, and learn some additional techniques that aren't covered in the screencast.

How to Use VLOOKUP in Excel: Walk Through 

Use the tabs "Ingredient Orders" and "Supplier List" for this Excel VLOOKUP example.

Let's say that we manage a restaurant and are placing our weekly orders to suppliers. The chefs have given us a list of ingredients to order, and we need to insert information about the supplier.

There are three pieces we need to add for each order:

  • The Supplier Name
  • The Supplier Phone Number
  • The Supplier Delivery Day

In this workbook, there are two tabs:

  • Ingredient Orders - contains the list of ingredient requests from our chefs.
  • Supplier List - contains information about the suppliers, such as the supplier name and phone number.
Excel VLOOKUP list ingredient order and supplier list example
At the top, we have our order data, which is on the "Ingredient Orders" tab. Below is the "Supplier List", which will act as our lookup list.

The common field between the two tables is the Ingredient tab. Let's use it to lookup each of the three fields and add it to the order list.

Step 1: Start Our Excel VLOOKUP Formula

On the Ingredient Orders tab, let's click in the first blank Supplier cell, F5, and press the equals sign to start the VLOOKUP formula. Then, type " VLOOKUP( to start the formula.

 =VLOOKUP(

Remember that our primary key—the piece of data that appears in both lists—is the ingredient, so we'll use it for the lookup. Either click on cell B5, or type it into the formula. Next, add a comma after "B5" so that we can enter the next part of the formula. 

=VLOOKUP(B5,

Now, we need to give the formula our lookup list. With the formula still open, click on the Supplier List tab. Now, click on cell A3, and click and drag to highlight and select A3 to G13, the whole lookup table. Make sure and press the F4 key on your keyboard to make the formula an absolute reference (more on this later). Finally, enter another comma.

Select the Excel lookup list
Point Excel to the lookup list.

After you enter the comma for the lookup cell, switch tabs and point Excel to the lookup list. Click and drag between cells A3 and G3 to select the data to lookup from. Make sure and press F4 on your keyboard during this step to create an absolute reference, which will lock in the cells to use for the lookup.

=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,

Next up, we'll need to tell Excel which column to pull from. Remember that our first item to insert is the Supplier name, which is in the second column of the lookup list. Add the number to the formula to pull from the second column of the lookup, and another comma.

=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,2,

Finally, we'll add FALSE for an exact lookup, and then close the parentheses:

=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,2,FALSE)

Pulldown formula is working perfectly in Excel
Our formula is working up perfectly, pulling in the supplier for the Potatoes. 

The good news is that we don't need to rewrite this same formula over and over—just double-click in the lower right corner of cell F5 (you can see the small green box on the corner of the cell) to extend the formula down (as shown above).

The formula is working perfectly! Okay, now let's move on to pulling in the data for the other two fields: the supplier phone number and delivery day.

Step 2: Pull More Data With Our VLOOKUP

Because we used an absolute reference, we can basically reuse the same formula we wrote with a minor tweak. Let's add in the Supplier Phone Number next.

We'll start off by copying and pasting cell F5 (our Supplier cell) to cell G5. I typically just use the keyboard shortcuts Ctrl + C and Ctrl + V to copy and paste the entire cell. At first, this won't be working, and you'll see an N/A in the cell. 

Copying and pasting formula in Excel Lookup Moved
 Copying and pasting lookup with adjustments in Excel.

It's much easier to copy and paste our formula into another cell, but it requires some tweaking. At first, Excel will be looking to cell C5 for the lookup, but we need to adjust it to "B5" in the formula bar. Once we do that, the lookup will be working - almost.

We'll need to go up to the formula bar, and change the first part of the formula from C5 back to B5. When we moved the formula over by one column, Excel updated other parts of the formula. We were getting an "N/A" because Excel was attempting to match the quantity (Column C) to our lookup list, but our lookup list doesn't include the quantity.

So far, our formula so should be:

=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,2,FALSE)

However, notice in the screenshot above that we're pulling the wrong bit of data into the "Phone" column. We're still pulling the second column in the lookup list with the "2" in the formula. We need to change this to the column number with our supplier's contact phone.

Step 3: Fix VLOOKUP N/A Error Issue

Let's go back and check out the lookup list. You'll notice that the phone number is in the 7th column in the lookup list. Let's go back to our Excel formula and update the lookup to pull from the 7th column.

Lookup List Column numbers need to be updated
We need to update the "2" in our Excel formula to a "7" to pull from the 7th column in our lookup list.

All that we need to do is update the column section of our lookup list from a "2" to a "7" and it now works great!

Our Excel vlookup formula is Updated for Supplier Phone
Notice that the supplier phone is working great now that we've updated our formula to use the 7th column from the lookup list. Now, just drag the formula down to update it for all cells.

Our final formula for the supplier phone number lookup:

=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,7,FALSE)

Step 4: Add Another Column to Finish Our VLOOKUP Formula

Finally, let's work in the Supplier Delivery Day column. Copy and paste cell G5 to cell H5. Again, we'll need to fix our formula by changing C5 back to B5 to use the ingredient as the primary key. Then, just update the "7" to a "5" to use the 5th column from our lookup table.

Final supplier delivery day lookup formula applied
Final supplier delivery day lookup formula applied.

Our final formula for the supplier delivery day:

=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,5,FALSE)

That's it! We wrote basically one formula and tweaked it to pull in every bit of data we need to place our next order.

Troubleshooting VLOOKUP

So, you've written the VLOOKUP formula following the instructions step-by-step, but it's still not working. Excel takes things pretty literally, so we need to be careful with the data and the VLOOKUP formula. Let's take a look at several ideas for correcting a VLOOKUP formula that's just not working.

1. Multiple Matches

One of the most common issues with VLOOKUP is when there are multiple matches in your lookup list. When you're using a VLOOKUP, it will match to the first item that's in the list.

Troubleshooting Example of Excel VLOOKUP not working
Our VLOOKUP says that Carrie Richard is our Director of Marketing, but we know she was recently promoted to the company president. Why isn't our lookup working?
Lookup problem with two listings conflicting in our VLOOKUP
Whoops, it looks like there were two listings for Carrie Richard in the lookup list - one for Director of Marketing, and one for President. Once we delete the "Director of Marketing" line from the lookup list, our data is correct.

The point of VLOOKUP is to look up a matching item against a list. The lookup list shouldn't contain duplicates for the primary key, the item that you're using to match. Otherwise, Excel gets confused and only shows you the first match.

2. Leading or Trailing Spaces

Another common issue is that our data might not actually match in Excel's eyes. Data with a space before it is said to have a "leading" space, while data with a space after it has a "trailing space."

Trailing Space VLOOKUP Example Problem
This VLOOKUP is perfectly written, but it isn't working. You can clearly see the name is in the lookup list, but Excel isn't return a match. Read on to find out why.

If your matching piece of data has a space before or after it, Excel sees these two pieces of data as totally different, and won't return a match. Excel views "_Andrew", "Andrew_" and "Andrew" as three unique pieces of data that won't be matched in VLOOKUP.

Resolving Excel VLOOKUP Trailing Space Issue
Turns out there is a "trailing space", or a single space after the name in the cell. It's impossible to see, but it can break a VLOOKUP because Excel treats "Alyssa Reddall" and "Alyssa Reddall(space)" differently. Once you delete the space, the VLOOKUP will work normally.

If your data has leading or trailing spaces, use the TRIM function in Excel to clean it up. VLOOKUP should work great after using it to remove leading and trailing spaces.

3. Lock in Cell References

As you might know, you can drag a cell down by the handle to paste the formula into other cells. However, this sometimes breaks our VLOOKUP because the cell references change. 

Bad reference VLOOKUP problem
In this screenshot, I dragged the VLOOKUP down the list to apply it to other cells. However, notice that the lookup reference changed from A2 to B15, to A16 to B29, hence why the formula isn't working. My lookup list is actually in A2 to B15, so the lookup is now broken.

As you pull the formulas down, it pulls the reference for the lookup table out of alignment. Suddenly, the lookup table is missing rows from the lookup table and our VLOOKUP isn't working.

The fix is to make the formula an absolute reference, so that when you drag the formula down, the list it's pointing to doesn't change. Click in the cell where you've written your VLOOKUP, and then click somewhere in the lookup list reference. Then, press F4 . You'll notice that the formula changes to include dollar signs.

Updated Excel VLOOKUP Formula with Absolute Reference
Notice that the formula (shown at the top of this screenshot in the formula bar) now includes dollar signs in the lookup list reference, which shows that it uses an absolute reference. Now, when I drag the formula down, it locks the formula for the lookup list and works perfectly.

Recap and Keep Learning

VLOOKUP is one of those essential formulas for being a productive Excel users. There's simply not enough time to manually look up data and re-type it over and over again, so formulas like VLOOKUP are important to learn.

  • If you want to learn an assortment of more advanced Excel skills, check out Bob Flisser's 12 Techniques for Power Users tutorial. 
  • Additionally, Bob's course Introduction to Spreadsheets is the perfect guide to getting started in Excel
  • Sometimes, it helps to learn similar material from another resource. The Microsoft Office official documentation on the VLOOKUP formula is another great resource for mastering VLOOKUP.

If you're having any problems with using the VLOOKUP formula, feel free to leave a comment below for troubleshooting.

Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.