VLOOKUP Tutorial: Updating prices in a master price list

AppId is over the quota
AppId is over the quota

In honor of VLOOKUP Week, MVP Bill Jelen has created a VLOOKUP tutorial for those of you who have a basic working knowledge of Excel but want to improve your skills. This tutorial assumes you know a little something about writing basic formulas in Excel. It assumes that you've heard of VLOOKUP and its benefits, but that you don't really know how to use it. Bill uses the example of updating prices in a product list to explore the following topics:

Part 1:  The Problem: It's taking way too long to find and update prices in a master price list

Part 2: The Solution: Using VLOOKUP to automatically update the price list

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.

Attempt 1: Using CTRL + F to update the 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:

Select the first part number from the new list in cell G3Press Ctrl+C to copy this item to the clipboardSelect all of column A by clicking on the A headingPress Ctrl+F to open the Find dialogPress Ctrl+V to paste the part number into the Find dialogClick the Find button

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:

Attempt 2: Using Find All 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:

Select the next part number from the new list (you are down to G9) by now.Press Ctrl+C to copy that cell to the clipboardPress Ctrl+A twice to select all cells in the worksheetPress Ctrl+F to open the Find dialogPress Ctrl+V to paste the part number in the Find dialogPress Alt+A to Find All! This brings up a list of both the item in the original list and the item in the new listSwitch between the two cells using the up and down arrow keys.At this point, it is probably easier to switch to the mouse. Click on the new price. Right-click and choose Copy. Click on the old price. Right-click and choose paste.You can return to the right section of the new price list using the hyperlinks in the still-open Find dialog.

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:

The category information was in another column, so you could easily sort the list by part number and then back by category.The list of new prices included all the items where the price did not change, and if it was in the exact same order as your list, so you could copy and paste huge ranges of prices.Your manager would have just done this himself.

But, in real life, data is not perfect. When data is not perfect, knowing an Excel function called VLOOKUP can save the day.

VLOOKUP formula explained

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:

=VLOOKUP(lookup_value,table_array,col_index_number,[range_lookup])

Lookup_value is the part number that you are looking up. To find a new price for the part number in A2, you are looking for A2. =VLOOKUP(A2,Table_array is the lookup table. The data that you are looking to match must be in the first column of the lookup table. That works in this case, because part number is in column G and new price is in column H. If those had been reversed, you would copy the prices so they are to the right of the part numbers. When you type the address of the table, make sure to use dollar signs so that the table reference doesn't change as you copy the formula down your worksheet. Instead of G3:H477, use $G$3:$H$477. Those dollar signs tell Excel to not lower the range as the formula gets copied down. =VLOOKUP(A2,$G$3:$H$477,Col_index_number tells Excel which column you want to return from the lookup table. In this case, you only have two columns. Part number is in the first column of the lookup table. New Price is in the second column of the lookup table. Since you want to return New Price, use 2 for the column. =VLOOKUP(A2,$G$3:$H$477,2Range_lookup - the square brackets in the tooltip say that this argument is optional, but in real life it is not optional! You should always put False as the fourth argument. If you leave the argument off, you are allowing Excel to find close matches. Close matches are never appropriate when you are doing the type of lookup described in this situation. =VLOOKUP(A2,$G$3:$H$477,2,False)

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

Techniques for dealing with inevitable #N/A errors

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:

To show a blank, use =IFERROR(VLOOKUP(A5,$G$3:$H$477,2,FALSE),"")To show a zero, use =IFERROR(VLOOKUP(A5,$G$3:$H$477,2,FALSE),0)To use the old price, use =IFERROR(VLOOKUP(A5,$G$3:$H$477,2,FALSE),B5)

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: 

 Come back tomorrow for Part II, where you'll how to use conditional formatting to highlight the items with new prices, and more...

View the original article here