|
Excel VLOOKUP Say you want to create an invoice that will automatically add the prices when you enter the item numbers. Create your invoice on Sheet One, and create a table of your item numbers and their corresponding prices on Sheet Two. In Sheet One you will add a VLookup formula that will pull the prices from Sheet Two when you enter the item number on Sheet One. Try it here. Change the item numbers in Cells A4 through A6 and see how the price changes in Cells C4 through C6. This interactive worksheet works in Internet Explorer only. Those using some other browser click here to see a screenshot of the spreadsheet if you do not see the Interactive version displayed. (You won't see it if you don't have Excel installed on your computer either. Or, if you are using a version of Excel other than 2002, the clsid value in the registry is different and you can download this registry fix to cure that. Just extract it to your desktop and double click it, then you can delete it.)
The formula in Cell C4 is =VLOOKUP(A4,$E$4:$G$8,3,FALSE)*B4. Double click in Cells C4, C5, and C6 to see the formulas. If you copy this formula down, note that A4 and B4 will change to A5 and B5, then A6 and B6, and so on down the column. Note that if your table is on Sheet Two, your absolute range (in this case, $E$4:$G$8) would have to include the sheet name of Sheet Two and would reflect the range of cells your table includes on that sheet and would look something like Sheet2!$A$1:$F$15). Note the "3" in the formula reflects the column number in the table where the desired information is located. If you change this to a "2", the result would show the information in the "Description" column instead of the "Price" column. This is a simple VLookup formula. Try adding other columns to the table on Sheet Two and adjusting the VLookup so it pulls from different columns. More info on VLOOKUPS: |
|
.
