Help populating cells with formulas based on contents of another cell

[Mr_Intuition]

Hi all. This is my first post and I'm hoping some of you excel gurus out there can help me. I have a spreadsheet I have made up that allows me to build a weekly menu and helps aid the user in counting calories. The first worksheet has the meal for Monday. Once I get the formulas entered, I'll just copy for the rest of the days of the week. Under "breakfast" on Monday, a user can use a drop down menu to pick a food item that comes from a list on another worksheet in the same workbook. Once they choose a food item, I want it to populate specific cells with corresponding multipliers. The user then enters the amount of the serving and that is multiplied by the multipliers mentioned above and thus populates one cell with number of calories, one cell with grams of fat, one cell with grams of protein and one cell with grams of carbohydrates. Hopefully this makes sense.

Below is a snapshot of the monday worksheet:

So when someone chooses bacon from the drop down list like shown above, I want it to lookup the nutritional information on this page:

and apply those multipliers/value to the cells on the Monday worksheet under "calorie", "fat", "carb", etc. That value will then be multiplied by the number in the "serving" column and an actual value will be placed in the row so the user knows how many calories, fat, etc will be consumed in a serving. The totals for the meal will be at the bottom (I know how to do that formula) letting the user know if the meal is in line with the diet he/she is on.

Hopefully this makes sense and isn't too confusing. I talked with one person and they said it would require some visual basic commands...which I know nothing about. Help please someone! I'm hoping this isn't too hard of a thing I'm trying to do!

 

[sheetspread]

You don't need VBA here, a vlookup formula can return the multiplier.

 

[Mr_Intuition]

Thanks Sheetspread. Can you give me an example of how that formula should be constructed?

 

[Mr_Intuition]

I try the vlookup function and it keeps returning a different value than what I intended. For instance, when I do this with the Bacon example, it returns a value that is for C column (3) instead of the B column which is 43. Any idea why? here is the formula I have come up with so far: =VLOOKUP(B4,Food!1:65536,Food!B:B,FALSE)

 

[sheetspread]

Your lookup range is too large and the second comma should be followed by a number or formula whose result is a number:

try: =vlookup($B4,Food!$A$2:$E$32,column(b1),0)