Help with Formulas

[mntraynum]

Hello all,

I'm trying to create a spreadsheet that will help my wife and I calculate food macros based off a single drop down box. The drop down box will have a food item and I would like to auto populate all the calories, fats, carbs and protein based off the drop down selection. Here is a quick example.

I have a chart with all the food macro information in it beside the food item. I'm not sure what formula/s to use to get to auto populate the cells.
When I choose the Peanut Butter 1 serving from the drop down box, I want it to fill in the rest from the chart below.
Just a quick example... Thanks in advance for your help!
[TABLE="width: 594"]
<colgroup><col width="163" style="width: 122pt; mso-width-source: userset; mso-width-alt: 5961;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;" span="8"> <tbody>[TR]
[TD="width: 163, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 78, bgcolor: transparent"]Qty[/TD]
[TD="class: xl65, width: 78, bgcolor: transparent"]Calorie[/TD]
[TD="class: xl65, width: 78, bgcolor: transparent"]Cal From Fat[/TD]
[TD="class: xl65, width: 78, bgcolor: transparent"]Fat[/TD]
[TD="class: xl65, width: 78, bgcolor: transparent"]Carbs[/TD]
[TD="class: xl65, width: 78, bgcolor: transparent"]Fiber[/TD]
[TD="class: xl65, width: 78, bgcolor: transparent"]Net Carbs[/TD]
[TD="class: xl65, width: 78, bgcolor: transparent"]Protein[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Peanut Butter 1 serving[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Peanut Butter 2 servings[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]item[/TD]
[TD]Qty[/TD]
[TD]Calorie[/TD]
[TD]cal from fat[/TD]
[TD]fat[/TD]
[TD]Carbs[/TD]
[TD]Fiber[/TD]
[TD]Net Carbs[/TD]
[TD]Protein[/TD]
[/TR]
[TR]
[TD]Peanut Butter 1 serving[/TD]
[TD]32g[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Peanut Butter 2 servings[/TD]
[TD="bgcolor: transparent"]64g[/TD]
[TD="bgcolor: transparent, align: right"]400[/TD]
[TD="bgcolor: transparent, align: right"]380[/TD]
[TD="bgcolor: transparent, align: right"]34[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[/TR]
[TR]
[TD]Butter 1 tbsp[/TD]
[TD]1 tbsp[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Butter 2 tbsp[/TD]
[TD="bgcolor: transparent"]2 tbsp[/TD]
[TD="bgcolor: transparent, align: right"]200[/TD]
[TD="bgcolor: transparent, align: right"]200[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jproffer]

Something like this would do what you want, I think. I've put them out of order because otherwise it just looks like your table ...but it would work with a dropdown in a cell to show only the one you want on a single row.

Book1
	A	B	C	D	E	F	G	H	I
1		calorie	carb	protein	fiber
2	Butter 1 tbsp	100	0	0	0
3	Peanut Butter 1 serving	200	4	8	3
4	peanut butter 2 servings	400	8	16	6
5
6
7
8	item	Qty	Calorie	cal from fat	fat	Carbs	Fiber	Net Carbs	Protein
9	Peanut Butter 1 serving	32g	200	190	17	4	3	1	8
10	Peanut Butter 2 servings	64g	400	380	34	8	6	2	16
11	Butter 1 tbsp	1 tbsp	100	100	11	0	0	0	0
12	Butter 2 tbsp	2 tbsp	200	200	22	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
B2	=VLOOKUP(A2,$A$9:$I$12,3,FALSE)
C2	=VLOOKUP(A2,$A$9:$I$12,6,FALSE)
D2	=VLOOKUP(A2,$A$9:$I$12,9,FALSE)
E2	=VLOOKUP(A2,$A$9:$I$12,7,FALSE)

As you can see in line 3, vlookup is not case sensitive, but if you're using a dropdown list that shouldn't matter in this case.

 

[mntraynum]

Thanks for the reply jproffer. Would this work on a blank sheet prior to selecting from the drop down?

 

[jproffer]

As long as you refer to the cell the dropdown will eventually fill, then yes. That cell would go in place of my cell A2 (first argument)

 

[mntraynum]

I'm not sure what is wrong but this section of the formula is giving me an error. $A$9:$I$12 highlighted red

 

[mntraynum]

So on my sheet th formula should be C3. =VLOOKUP(B2,$K$3:$S$6,13,FALSE)

 

[mntraynum]

Or would that be HLOOKUP?

 

[jproffer]

Your formula only shows 9 columns (K through S) in your table, but you're wanting to return column 13. No can do .

It's Vlookup.

 

[mntraynum]

Got it! Thanks again! Also, is there anyway to make the process quicker? Like do the entire column vs each individual cell?

 

[jproffer]

Now you kind of lost me. You want to return all the info for, say, "butter 1 tbsp" at one go instead of having 10 (or however many) Vlookup formulas to find each individual cell of information? I don't think so, and I don't think you would gain a lot of time even if you could.

You're only looking up one item from your list at a time, is that right? And 10 or so informational items for that food item?

Even if you have 1000s of food items in your list, it should only take milliseconds to find all your nut. information items for that food item after you select it.