Keeper4826
New Member
- Joined
- Nov 6, 2006
- Messages
- 47
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).
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).
