VLOOKUP - two criteria

[erahi]

I've created an excel calorie counter and i need some assistance to add a few functions to make it even more useful. Currently I have a drop-down food list and I use VLOOKUP to fetch the required figures (carbs, protein and fats etc) for that selected food item and the portion size in grams. I want to add an additional list thats dependant on the food list. For example if i select 'Whole Eggs' then i want the second list to show the different egg sizes, Large, X-Large, Jumbo or if i select 'Apple' then i want the second list to to show, small, medium, large or if i select 'milk' then the second list to show cups, millilitres etc. I know how to create a dependant list, however my issue is setting up the required table/s to fetch the required figures. How would i go about doing this? I've attached the file on here. Any assistance is appreciated!

[Ace_XL]

Something on these lines perhaps..

=SUMPRODUCT(--(Foodlist=FoodSelected),--(SizeList=SizeSelected),(Carbs))
=SUMPRODUCT(--(Foodlist=FoodSelected),--(SizeList=SizeSelected),(Protein))
=SUMPRODUCT(--(Foodlist=FoodSelected),--(SizeList=SizeSelected),(Fats))

[erahi]

Wouldnt you use vlookup ??

[oeldere]

Maybe you can show us in your file the wanted results (manual inputted in a few rows).

[erahi]

ive uploaded another with the results i want. Look under the 'Monday' tab. Hope this helps

[oeldere]

Egg: Whole 2 X-Large
Milk: Skim, Zymil, Pauls 1,5 Cups
Fruit: Apple 1 Small


where can we find the value 2 and x-large

I don't see them in the file.

[erahi]

ive manually typed it up to show you what id like to see. If i select Whole egg then i want the second dependant list to show 'Jumbo, X-Large, Large' or if i select Apple then i want the second list to show 'small, medium large, etc. then id type in the portion size number 1 or 1.5 or 2 for that particular item

[oeldere]

If you don't add the values in the sheet, i can't make a formula to find them (because, they don't exist).

Please Login or Register  to view this content.

You can use this to fill the sheet.