You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
MVP Bill Jelen has created this VLOOKUP tutorial for those of you who have a basic working knowledge of Excel but want to improve your skills. This tutorial assumes you've heard about the VLOOKUP function and its benefits, but that you don't really know how or when you might use it. Bill uses the example of updating prices in a product list to introduce the value of this time-saving function.
The Problem: It's taking way too long to find and update prices in a master price list
The Solution: Speed up the process with VLOOKUP
Suppose that you maintain an Excel workbook with over 10,000 prices for products that your company sells. Parts are arranged in sections by category. You have part numbers in column A, prices in column B, and other information in column C. Now, imagine that your manager just gave you a worksheet with updates for 475 of the items in your list.
You decide to copy the price update to a blank section of your master price list.
From this point, you start trying to find each item from the new list in the old list. Since the items in the old list are not sorted and cannot easily be sorted across the various sections of the price list, you decide to use the Find command to make the process easier.
Here is your first attempt:
You are now at row 2063 and fairly proud that you didn't have to click PgDn one hundred times to get here. Unfortunately, you've forgotten the new price so you have to start over.
Watch me try using CTRL + F to update the list:
You try jotting the new price on a sheet of scrap paper, but then you think there might be a faster way. After some experimenting, you end up with this awesome set of steps:
Watch me using Find All to update the list:
Even with this improved set of steps, it is taking almost 1 minute per item. You do some quick math and realize that 450 items are going to require 8 mind-numbing hours. Your first thought might be, "There has to be a better way, but what is it?"
I began working as a financial analyst in 1989. I've learned that data is rarely perfect.
This price list workbook is a case in point. It has been handed down in your department for the last dozen years. It was set up by some guy named Bob who no longer works here. In the workbook, there are lots of things that would have made life easier.
For example, it would have been nice if:
But, in real life, data is not perfect. When data is not perfect, knowing an Excel function called VLOOKUP can save the day.
VLOOKUP stands for "Vertical Lookup." The vertical means that your list of new prices is going down the spreadsheet instead of across. (If you are wondering, there is an HLOOKUP for when your lookup table is going across.)
The tooltip for VLOOKUP says you need:
Add a new column to your price list called New Price. Enter =VLOOKUP(A2,$G$3:$H$477,2,False) in cell C5. You get the new price. This looks promising!
Your first VLOOKUP returns the new price
Select the formula in C5 and double-click the fill handle to copy the formula down. (The fill handle is the square dot in the bottom right corner of the selected cell.) Because of the blank cells between categories, the fill handle will only copy the formula down to the end of the first category. Already, you can see that something seems to be wrong.
Uh-oh! #N/A errors are usually bad, but maybe not in this case
A few of the VLOOKUP formulas are returning numbers. Many more are returning the #N/A error. When you do a VLOOKUP, the #N/A error means that the value you are looking up is not found in the lookup table. In this case, this is to be expected, since only 475 of the 10,000 prices are being updated.
Since you are expecting the #N/A errors, you can use the IFERROR function (available in Excel 2007 or later) to replace the #N/A with something else. You might put a space. You might put a zero. Or...in this case, if you don't have a new price, then the old price is still valid, so you could put the old price!
Edit the original formula in C5. Here are the three formulas you could use:
Choose a formula and copy it down. Your new column shows the new price if there is one, and the old price if there isn't one.
=IFERROR() to the rescue
In this video, see how I define VLOOKUP and explain the VLOOKUP formula, and get rid of #N/A:
When I look at the two columns of data, it is hard to see which prices changed and which did not. You can use conditional formatting to highlight which items changed.
Any prices that have changed will appear in red.
So far, you've solved the problem for a very small category of the master price list. You need to copy the C5 cell to the first row of the next category and use the fill handle to fill down. With 10 more categories, you will repeat this process several times and it might take 5 minutes to perform the copy operation repeatedly. Maybe it would be easier to copy all the way down to row 10000 and then manually clear out the blank rows between the categories. Someday you should reorganize the spreadsheet, but for today, you've save a lot of time.
At this point, you decide you don't need the temporary range over in G:H any more. Don't delete it yet! Those VLOOKUP formulas back in column C are still using that range. You need to convert the formulas to values.
If you've followed along here, you might not know what to say. You might be upset that you wasted so much time using the Find method and wasted one day of your life every month for the last 18 months. You might be angry that you never knew about VLOOKUP before. You might be giddy that you solved this problem in 20 minutes instead of 8 hours. (And...once you gain more confidence with VLOOKUP, you'll be able to solve the problem faster and faster each month.) You might be plotting what you can do with all the time you just got back today ("long lunch!").
VLOOKUP solved this cool problem and can solve many more problems.
When you were manually finding the items, it was natural to start with the shorter list of 475 items with the intent to work through it. Since VLOOKUP is incredibly fast, you can throw the VLOOKUP at all 10,000 items and let the IFERROR clean up the unfound entries.
Many people get discouraged when they encounter the #N/A errors from VLOOKUP. Understand that some #N/A are to be expected. It is part of the messiness of data in real life.
Have you done this process manually in the past? Did the part numbers in the new price list match up correctly? As a human, you would recognize "A-1234" and "A1234" to likely be the same item, but VLOOKUP will not understand this. Take an extra five minutes to make sure that all of the items in your new price list are found in the original table:
This is just one example of the many ways to use VLOOKUP. Check out vlookupweek.wordpress.com for many additional articles on other ways to use VLOOKUP.
About the author: Bill Jelen is an Excel MVP and the host of MrExcel.com. He is the author of the e-book VLOOKUP Awesome Quick: From your first VLOOKUP to becoming a VLOOKUP Guru. This article appears as part of VLOOKUP Week March 25-31, 2012.
Mr. Excel ======> Your work is excellence,
This is a good tutorial but.. being the critic that I sometimes am.. Don't get me wrong; his examples work out and really showed you the flaws and caveats in certain workflows.
But having said that; in the first video he could have stored the "import data" (new stuff) on a different sheet. As such instead of having to leave the current location to find the new price again all he had to do was to press control-pagedown to make it visible; another control-page up and he could have filled it in.
Details, as said above; don't take this comment in the wrong way. But still; details which I tend to spot ;-)
Apart from that; very cool tutorial. I enjoyed myself /and/ learned a new trick in the process.
Lovely! Detailed. I enjoyed reading this blog on Vlookup thorougly. In addition, the videos made things easier to understand. Most people find Vlookup difficult to use in the beginning because of the parameters that you need to enter and the last parameter Range_lookup causes the most confusion. But Emily has explained Vlookkup in this article so well that I don't think anybody should now find it difficult.
Thank you Bill !