Lookup_value is the part number that you are looking up.=VLOOKUP(lookup_value,table_array,col_index_number,) (If you are wondering, there is an HLOOKUP for when your lookup table is going across.) VLOOKUP stands for “Vertical Lookup.” The vertical means that your list of new prices is going down the spreadsheet instead of across. When data is not perfect, knowing an Excel function called VLOOKUP can save the day. Your manager would have just done this himself.īut, in real life, data is not perfect.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.The category information was in another column, so you could easily sort the list by part number and then back by category.In the workbook, there are lots of things that would have made life easier. It was set up by some guy named Bob who no longer works here. It has been handed down in your department for the last dozen years. This price list workbook is a case in point. I’ve learned that data is rarely perfect. I began working as a financial analyst in 1989. Your first thought might be, “There has to be a better way, but what is it?” The Solution: Speed up the process with VLOOKUP You do some quick math and realize that 450 items are going to require 8 mind-numbing hours. Įven with this improved set of steps, it is taking almost 1 minute per item. You can return to the right section of the new price list using the hyperlinks in the still-open Find dialog.At this point, it is probably easier to switch to the mouse.Switch between the two cells using the up and down arrow keys.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.Press Ctrl+V to paste the part number in the Find dialog.Press Ctrl+A twice to select all cells in the worksheet.Press Ctrl+C to copy that cell to the clipboard.Select the next part number from the new list (you are down to G9) by now.After some experimenting, you end up with this awesome set of steps: You try jotting the new price on a sheet of scrap paper, but then you think there might be a faster way. Attempt 2: Use Find All to update the list Unfortunately, you’ve forgotten the new price so you have to start over. You are now at row 2063 and fairly proud that you didn’t have to click PgDn one hundred times to get here. Press Ctrl+V to paste the part number into the Find dialog.Select all of column A by clicking on the A heading.Press Ctrl+C to copy this item to the clipboard.Select the first part number from the new list in cell G3.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. You decide to copy the price update to a blank section of your master price list.įrom this point, you start trying to find each item from the new list in the old list. Attempt 1: Use CTRL + F to update the list Now, imagine that your manager just gave you a worksheet with updates for 475 of the items in your list. You have part numbers in column A, prices in column B, and other information in column C. Parts are arranged in sections by category. Suppose that you maintain an Excel workbook with over 10,000 prices for products that your company sells.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |