Hi, I am trying to build a spreadsheet that will give me the cost of each recipe I might want to cook. I have created fields containing each ingredients price per g (ppg) and the idea is that (ppg)*(amount used)=(cost for this ingredient within this recipe). What i need is for excel to take (price per g) and multiply it by (amount used) I am currently using =if([ingredient]=[ingredient a(from ingredient list)],[ppg a from list]*[amount used],if([ingredient]=[ingredient b],[ppg b]*[amount used], if(ingredient=[ingredient c].....

as you can see this formula is going to be quite long to write if I have a list of over 200 ingredients so I was hoping to find a more intelligent formula that can take the properties of one column in a row based on the selection of another column in that row. using F:F doesn't seem to work as it takes the row the formula is on rather that the target's I have experimented with access as well but i don't know it very well so am hoping to get somewhere with this. Thanks, Ed
 

---

Answer from Richard Rost:

Have you considered using VLOOKUP to get the price from a separate pricing sheet?

You can use the VLOOKUP or HLOOKUP function.

VLOOKUP is a powerful function in Microsoft Excel that allows you to look up a value from another spreadsheet - or a smaller subtable (lookup table).

See this FREE tutorial for help with VLOOKUP:

http://599cd.com/tips/excel/vlookup/?key=BlFAlEx