A client once told me about a process that consumed several hours of their week. It involved hand entering quantities on a spreadsheet, then looking up a unit price on another spreadsheet and entering it in the next column. Naturally, they wanted a better way to complete this task. While I couldn\u2019t do anything about having to enter the quantities, I was able to save them a lot of time and reduce opportunity for errors by using Excel\u2019s VLOOKUP function to automatically bring in the correct price from their pricing spreadsheet.<\/p>\n
Let\u2019s look at a simple example of how this works. The spreadsheet below has sales information in columns A \u2013 D and the price list is in columns F and G.\u00a0 In a real life situation the price list would more likely be in a different workbook or at least on a different tab in the same workbook.\u00a0 But the formula still works the same way.<\/p>\n
You can type your vlookup<\/em> formula in from scratch or use the function wizard button on the formula bar to guide you through it. Here is what each part of the formula represents:<\/p>\n Once you have entered the formula on the first line, you can copy it down the rest of the column.\u00a0 So entering the formulas shown in column C here:<\/p>\n <\/p>\n A<\/p>\n<\/td>\n B<\/p>\n<\/td>\n C<\/p>\n<\/td>\n D<\/p>\n<\/td>\n E<\/p>\n<\/td>\n F<\/p>\n<\/td>\n G<\/p>\n<\/td>\n<\/tr>\n 1<\/p>\n<\/td>\n 2<\/p>\n<\/td>\n 3<\/p>\n<\/td>\n 4<\/p>\n<\/td>\n 5<\/p>\n<\/td>\n Will find the correct unit price for each item, as shown here:<\/p>\n A<\/p>\n<\/td>\n B<\/p>\n<\/td>\n C<\/p>\n<\/td>\n D<\/p>\n<\/td>\n E<\/p>\n<\/td>\n F<\/p>\n<\/td>\n G<\/p>\n<\/td>\n<\/tr>\n 1<\/p>\n<\/td>\n 2<\/p>\n<\/td>\n 3<\/p>\n<\/td>\n 4<\/p>\n<\/td>\n 5<\/p>\n<\/td>\n VLOOKUP is a great way to combine two sets of data into one. Once you are comfortable with it, you will find many ways to make use of it.<\/span><\/p>\n By the way, in case you’re wondering, there’s also an HLOOKUP function that works similarly for horizontal look-ups.<\/p>\n\n
\n\n
\n <\/td>\n \n \n \n \n \n \n \n \n \n ITEM<\/td>\n QTY<\/td>\n PRICE<\/td>\n EXT AMOUNT<\/td>\n <\/td>\n ITEM<\/td>\n UNIT PRICE<\/td>\n<\/tr>\n \n \n Gadget B<\/td>\n 2<\/td>\n =VLOOKUP(A2,F:G,2,FALSE)<\/td>\n <\/td>\n <\/td>\n Gadget A<\/td>\n 10<\/td>\n<\/tr>\n \n \n WIDGET 2<\/td>\n 1<\/td>\n =VLOOKUP(A3,F:G,2,FALSE)<\/td>\n <\/td>\n <\/td>\n Gadget B<\/td>\n 12<\/td>\n<\/tr>\n \n \n WIDGET 1<\/td>\n 4<\/td>\n =VLOOKUP(A4,F:G,2,FALSE)<\/td>\n <\/td>\n <\/td>\n WIDGET 1<\/td>\n 21<\/td>\n<\/tr>\n \n \n Gadget A<\/td>\n 5<\/td>\n =VLOOKUP(A5,F:G,2,FALSE)<\/td>\n <\/td>\n <\/td>\n WIDGET 2<\/td>\n 25<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\n
\n <\/td>\n \n \n \n \n \n \n \n \n \n ITEM<\/td>\n QTY<\/td>\n PRICE<\/td>\n EXT AMOUNT<\/td>\n <\/td>\n ITEM<\/td>\n UNIT PRICE<\/td>\n<\/tr>\n \n \n Gadget B<\/td>\n 2<\/td>\n 12<\/td>\n 24<\/td>\n <\/td>\n Gadget A<\/td>\n 10<\/td>\n<\/tr>\n \n \n WIDGET 2<\/td>\n 1<\/td>\n 25<\/td>\n 25<\/td>\n <\/td>\n Gadget B<\/td>\n 12<\/td>\n<\/tr>\n \n \n WIDGET 1<\/td>\n 4<\/td>\n 21<\/td>\n 84<\/td>\n <\/td>\n WIDGET 1<\/td>\n 21<\/td>\n<\/tr>\n \n \n Gadget A<\/td>\n 5<\/td>\n 10<\/td>\n 50<\/td>\n <\/td>\n WIDGET 2<\/td>\n 25<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n