Help with my spreadsheet required!!!

[nigeywigey]

Hi all, im very new to excel and im I'm trying to create a recipe spreadsheet with calorie information next to my recipes.
I have a separate food list with the calorie info based on 1gm. How can I go about automatically populating the nutritional info when entering in an ingredient into my recipe template?

I've attached the file so you can see what I've got so far!

 

[etaf]

you have various names for an item , but with additional info , like Almonds and you would need to use the correct one to lookup its value
if you are using the correct name as in the list

Then a VLOOKUP() or index/match would work
and multiple buy the amount, BUT then what weights are you entering as text

I think using XL2BB would be better rather than i try and type all that out

OR
put the spreadsheet on a share I only look at onedrive / dropbox

Vlookup(A4, 'food nutrition for 1g'!A:N, 5,false). 5 is the column from N you want to return

Assume you want results from column K
Index('food nutrition for 1g'!K:K, Match( A4 , 'food nutrition for 1g'!A:A, 0 ))

Then you could * by the amount - in this case 100 - BUT not if entered as 1g 2KG etc - perhaps have a column for the Weight as a number and column for the amount
column B would have 100 and column C would have "g"
Then you could work out the Multiplying factor

 

[Toadstool]

Hi NigeyWigey,

I'm not sure why column F in the template repeats the ingredient name unless I'm missing something?

For the weight (as it's all in grams) you should omit the "g" as "100g" will be treated as text and fail.

This assume your columns for template and FOOD NUTRITION PER 1G are in the same sequence (i.e. Fat is column H and C)

Here's my subset of the data table

NigeyWigey.xlsx
	A	B	C	D
1	Food Name	Protein	Fat	Carbohydrate
2		Protein	FAT	Carbohydrate
3		pro	fat	Carbohydrate
4	Ackee	0.029	0.3	0.5
5	Agar	0.03	0.2	0.4
6	Allspice	0.05	0.3	0.5
FOOD NUTRITION PER 1G

Here's a formula you could put in G4 then copy down and across

NigeyWigey.xlsx
	A	B	C	D	E	F	G	H	I
3	Ingredients	Weight	Unit	Method		Food Name	Protein	Fat	Carbohydrate
4	Ackee	100				Ackee	2.9	30	50
5	Agar	200				Agar	6	40	80
6	Allspice	500				Allspice	25	150	250
template

Cell Formulas
Range	Formula
F4:F6	F4	=A4&""
G4:I6	G4	=$B4*INDEX('FOOD NUTRITION PER 1G'!B$4:B$9999,MATCH($F4,'FOOD NUTRITION PER 1G'!$A$4:$A$9999,0))