Find Product Price in Excel with VLOOKUP

0 Comments | Rating: 0 | |

In Excel, you can create a table of information about your products, or other data you refer to frequently. Then, you can use Excel formulas to look up data from the master table. For example, you could have a table with product code, product name, and product price columns.

Once you have set up the lookup table, you can use VLOOKUP formulas in the workbook, to pull information from the product list. For example, to prepare an invoice, you can enter a product code, and formulas will get the product name or product price from the product lookup table.

The following video shows the step by step instructions for creating a VLOOKUP formula in a product order form.

Get the Flash Player to see this player.

Tips for setting up your lookup table:

· Store each lookup table on a separate worksheet in your workbook. Then, if you add or delete items in the lookup tables, you won't accidentally add or delete rows in any other table on the same sheet.

· Enter column headings in the first row of the lookup table

· Put the lookup values in the first column of the lookup table. The lookup value should be a unique key value on which you will base the lookup. For example, enter product codes in the first column, so you can find the price for a specific product code.

· If there is other data on the worksheet, be sure to leave at least one blank row at the bottom of thelookup table, and one blank column at the right of the lookup table, to separate the lookup table from the other data.

There are many other examples of VLOOKUP formulas and how you can use them in your Excel workbooks. You can combine VLOOKUP with other functions to prevent errors or to show a specific result if the lookup value is not found.

You can also use Excel functions INDEX and MATCH to pull data from a lookup table. These functions are very useful if the lookup values cannot be in the first column.