Help Linking Product ID to Cost

[TexasAg]

I have a table showing product waste by: Date, Type of Product (Product ID), Amount of Waste (feet), and the cost of the product/waste (USD).

for example: on 2/10/2009, i had 10 feet wasted, of Product A, that cost 1.25 per foot.

But.... on 2/11/2009, I had 20 feet wasted, of product B, that costs 2.50 per foot


I would like to format my table so that when a certain ID number (Product A) or (Product B) is typed in under "Product ID" it will automatily put the correct price in my forumla for calculating cost.

I currently have my cost as: =(Amount of Wate)*(cost of product per foot)

The problem is there could be 100 different product with a 100 different prices, so I dont know how to get the "Cost of Product" to automaticly make its way into the "Cost" forumla based on the "Product ID" column.

Any help with a formula or format would be greatly appreciated.

Thanks!

[TexasAg]

My first thought was to use (if,then,equals).

If "Product ID" equals "5615161", then cost per foot equals "$1.00"

But... that only takes care of one product ID, not the possible 100 different product ID's that are being used.

So would i have to make a massive formula to cover every "Product ID" using (if,then,equals) ?

Im not even sure that is possible, so that is why I am asking for help.

Thanks again.

[Masun]

Hi Texas,

I dont know but it seems to me that you need to make a reference table of som kind. Could be placed in a new sheet or in the existing one. When you have that table with the Product id and that corresponding Product cost you could use a VLOOKUP formula that search for the Product id and then finds that id:s cost. Thats how i would do anyway.

/Masun

[TexasAg]

I will try that out.

Thanks for the help.

[Masun]

If you cant make it work and If you have all the id:s and their cost i think i might help you if you uploaded your workbook.

/Masun