The Problem: It's taking way too long to find and update prices in a master price list
Attempt 1: Use CTRL + F to update the list
- Select the first part number from the new list in cell G3
- Press Ctrl+C to copy this item to the clipboard
- Select all of column A by clicking on the A heading
- Press Ctrl+F to open the Find dialog
- Press Ctrl+V to paste the part number into the Find dialog
- Click the Find button
Attempt 2: Use Find All to update the list
- 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 clipboard
- Press Ctrl+A twice to select all cells in the worksheet
- Press Ctrl+F to open the Find dialog
- Press Ctrl+V to paste the part number in the Find dialog
- Press Alt+A to Find All! This brings up a list of both the item in the original list and the item in the new list
- Switch 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.
The Solution: Speed up the process with VLOOKUP
- 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.
VLOOKUP explained
- 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,2
- Range_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)
Techniques for dealing with inevitable #N/A errors
- 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)
Use conditional formatting to highlight new prices
- Select C5:C18. Although you are formatting a large range of cells, you need to do the following steps thinking only about the first cell in the range, C5.
- Select Home, Conditional Formatting, New Rule.
- Choose Use a Formula to Determine Which Cells to Format
- In the rule description, type a formula of =C5<>B5
- Click the Format button. Choose a red font. The dialog will look like Figure 4.
- Click OK to apply the conditional format.
Pain is relative: Copy your VLOOKUP formula to other categories
Don't forget: Change those formulas to values before you delete the new price range
- Select all of column C
- Right-click and choose Copy
- Right-click and choose the 123 icon for Paste Values
I'm Speechless
What to do with your free time–perform a quick quality-check
- Add a column heading in I2 called "Is it OK?"
- The formula in I3 is =VLOOKUP(I3,A:A,1,False). This formula will look for the part number from I3 over in column A. If it is found, it will return the first column of the lookup table, which is the part number from column A. If it is not found, it will return #N/A.
- Copy that formula down by double-clicking the fill handle.
- Sort descending by column I. Select one cell such as I2 and click the ZA button on the data tab. If there are any #N/A values that come to the top of the list, it means that the part number in I is not found in column A.
- Do some research on these. Are they mis-typed? Are they new?