Using the lookup function with dynamically created named ranges

[IAimtoMisbehave]

Hello,


I am creating a nutritional data spreadsheet to track a diet I am on.
I'm wondering if there is a way to use the Lookup function to search through named ranges that are created depending on user selections in the first sheet.
To elaborate: I have on sheet 1 my meal for breakfast where I select the food I ate in the morning using dependent lists and the spreadsheet will automatically fill out and sum up the nutritional data for that meal. On sheet 2 I have all the basic food groups (Proteins, Dairy, Vegetables, Fruits, Grains) and a number of foods that I regularly eat input in the proper food groups with their nutritional data filled in.


I'm not sure if I should just post my spreadhseet, so I'll paste some examples instead... I would be happy to attach the actual spreadsheet to make things clearer to understand, I just don't know if that is allowed or not. If its okay and someone wants me to attach it then I will!


So an example of Breakfast in sheet 1 would be:
-------------------------------------------------------------------------------------------------------------------------

9/1/2014
Monday
Breakfast
Food Group		Proteins	Dairy	Fruits	Proteins
Food Item	Meal Totals	1. Egg	1. Mozarella Cheese	1. Sliced Pineapple	3. Bacon
# Servings		2	0.75	1	2
Calories	413.5	156	67.5	70	120
Fat	21.5	10	4.5	0	7
Cholesterol	415.25	374	11.25	0	30
Sodium	666.5	124	112.5	10	420
Potassium	126	126	0	0	0
Carbohydrates	16.75	0	0.75	16	0
Protein	29.55	12	5.25	0.3	12

<tbody>
</tbody>

------------------------------------------------------------------------------------------------------------------------------------------
The food group row selections are populated by selecting from a dropdown list, and the Food item dropdown list is then populated based on the foodgroup selection (So if Proteins is selected as the food group you will only get protein options to fill in the Food Items cell). Then the values for calories, fat, etc, are automatically looked up and selected from the proper lists on sheet 2.


Here is an example of Sheet 2:
-----------------------------------------------------------------------------------------------------------------------------------------

Proteins
Food Item	1. Egg	2. Tuna	3. Bacon
Calories	78	50	60
Fat	5	0.5	3.5
Cholesterol	187	30	15
Sodium	62	180	210
Potassium	63	90	0
Carbohydrates	0	0	0
Protein	6	13	6

<tbody>
</tbody>

------------------------------------------------------------------------------------------------------------------------------------------


The items in the "Food Item" row are in a named range called "Proteins", and the items in the Calories row are in a named range called "ProteinsCalories", the Fat row items in a named range called "ProteinsFat", etc...


So in sheet 1 when I select "Proteins" and "1. Eggs" for the first food item, I need to do a lookup to go grab the value for the calories for eggs listed on sheet 2.
I am wondering if there is a way to do that for all the different food groups using a dynamic lookup instead of using a bunch of nested IF statements for all the different food groups, which is what I currently have...


Is there any way to put in something like this:
=LOOKUP(C70,C69,C69&A72)
(Note that C70 is the "1. Eggs" Selection from a dropdown, C69 is the "Proteins" selection from a dropdown, and A72 is the cell labeled "Calories")


To make it easier to read for you, that essentially translates into: =LOOKUP(1. Eggs, Proteins, ProteinsCalories)


The only problem is that I don't know how to get excel to recognize that the lookup vector and results vector in the formula should be seen as named ranges on sheet 2 as opposed to simple cell references like I have selected.


Right now I have this formula (which works but is very convoluted)(Note that C71 is the "# servings" cell):
=C71*IF(C69="Proteins",LOOKUP(C70,Proteins,ProteinsCalories),IF(C69="Dairy",LOOKUP(C70,Dairy,DairyCalories),IF(C69="Vegetables",LOOKUP(C70,Vegetables,VegetablesCalories),IF(C69="Fruits",LOOKUP(C70,Fruits,FruitsCalories),LOOKUP(C70,Grains,GrainsCalories)))))




Essentially what this boils down to is does anyone know how to conglomerate two cells into a string that can then be recognized as a named range to be used in a lookup function?


Thanks in advance for any help you can provide!

 

[Teeroy]

Hi and welcome to the forum.

The following example (using your sample data) for the calories of egg (at Sheet 1, C7) should give you an idea. I don't have a html maker installed at work so I can't show how this can be extended.


C7: =F6*HLOOKUP(INDEX($A$1:$Z$10000,MAX(($A$4:$A7="Food Item")*ROW($A$4:$A7)),COLUMN()),Sheet2!$A$2:$R$9,2)

This is an Array formula and must be confirmed with CTRL+ALT+Enter.