Nested VLOOKUP Alternative

[Keeper4826]

On my main sheet, I have a row with two drop down lists. In column B, you select a food category. In column C, you select a specific food item based on the food category selected. The remaining columns to the right of that are for the macro nutrients of the selected food item (carbs, sugar, protein, etc.). There is a one sheet for each food category, each with a table listing out the food items in that category and their individual macro data. There are a total of 7 sheets for the various categories. An additional sheet has been created with a table which correlates food category to corresponding table. For example, the "Fruit" category is on the Fruits sheet, which has Table3. My goal to populate the macro nutrient data of a select food item on the main sheet, pulling that data from the appropriate table based on the food category select.

The first step was to create a VLOOKUP formula based on the food item. I got that to work just fine.
=VLOOKUP(C3,Table4,3,FALSE) 'C3 is the individual item to be search for, Table4 is the table that matches the category of the item selected.

My challenge now is - how do I determine which table is appropriate for the search? My example is hardcoded. What I really need is a nested VLOOKUP based on the data in B3, which would return the appropriate Table value based on my category/table reference sheet.
=VLOOKUP(C3,(VLOOKUP(B3,Table8,2,FALSE)),3,FALSE) 'Searching able8 would return the proper table for the category selected in B3.

This appears to be an invalid formula, as nesting VLOOKUPs is not allowed. How do I solve this issue?

Once I crack this nut, I'd like to know if it's possible to return multiple values based on the search results. This would allow me to input one function in a single cell, but populate five cells (for the various macro data).

 

[wideboydixon]

Couldn't you just use INDIRECT in the first place?

=VLOOKUP(C3,INDIRECT(VLOOKUP(B3,Table8,2,FALSE)),3,FALSE)

WBD

 

[steve the fish]

Couldn't you just use INDIRECT in the first place?

=VLOOKUP(C3,INDIRECT(VLOOKUP(B3,Table8,2,FALSE)),3,FALSE)

WBD

I was thinking that. If the sheet name is the same as the category and theres a different sheet for each one then maybe something like this:

=VLOOKUP($C3,INDIRECT("'"&$B3&"'!A:G"),COLUMNS($A$1:C1),0)

Would negate the need for the lookup table of tables.

 

[Eric W]

I think the INDIRECT formula would be easier:

	A	B	C	D	E	F	G	H	I
1	Meal	Category	Item	Qty	Carbs	Fat	Protein	Sugar	Calories
2	MONDAY
3	Breakfast	Meats_Eggs_Nuts	Egg (large)		0.38g	4.97g	6.29g	0.38g	74kcal
5
6
7
8
9
10
11
12	Total				0.00g	0.00g	0.00g	0.00g	0.00g

<tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula E3 =VLOOKUP($C3,INDIRECT(VLOOKUP($B3,Table8,2,FALSE)),COLUMNS($E3:E3)+2,FALSE) <tbody> </tbody>

<tbody>
</tbody>

Put the formula in E3 and drag down and right as needed.

And to add to what Steve and WBD said, if you actually name the tables "Bread_Cereal_Rice_Pasta" and "Vegetables", etc., then the formula would just be:
=VLOOKUP($C3,INDIRECT($B3),COLUMNS($E3:E3)+2,FALSE)

The problem at this point is how to sum the values. With the g or kcal, it makes the values alphanumeric. It would probably be easier to change the headings on your tables to "Carbs (g)" and just put the numbers in the cell. Let us know what you think.