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).

 

[offthelip]

You might be able to do this with index and Match and possibly using the Indirect function. Another guaranteed way of doing this is to use VBa and variant arrays which is much faster and more flexible that using vlookup. I am not sure of the layout of you data so I can't write any code to help you. You could look at this thread which shows how to use VBA as an alternative to Vlookup. VBA has the advantage you can nest the "lookups" to any depth you like. Also you can return multiple values very easily
https://www.mrexcel.com/forum/excel-questions/1043185-vlookup-vba-alternative.html

 

[Keeper4826]

Thank you. Here are some of the sheets I'm working with.

This is the main sheet:

Unknown 64 bit

	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​	74.00g​
4​	​	​
5​	​	​
6​	​	​
7​	​	​
8​	​	​
9​	​	​
10​	​	​
11​
12​	Total				0.00g​	0.00g​	0.00g​	0.00g​	0.00g​

Sheet: Example

This is an example of one of the category sheets (this one is Table4):

Unknown 64 bit

	A​	B​	C​	D​	E​	F​	G​
1​	Meal Item	Qty	Carbs	Fat	Protein	Sugar	Calories
2​	Chicken Thigh (skinless)	4oz​	0.00g​	10.79g​	25.72g​	0.00g​	207kcal​
3​	Salmon	4oz​	0.00g​	6.72g​	24.52g​	0.00g​	166kcal​
4​	Lamb	3oz​	0.00g​	17.66g​	20.68g​	0.00g​	248kcal​
5​	Ground Turkey	100g​	0.00g​	13.04g​	27.13g​	0.00g​	233kcal​
6​	Egg (large)	1x​	0.38g​	4.97g​	6.29g​	0.38g​	74kcal​
7​	Cashews	1oz​	8.56g​	12.43g​	5.17g​	1.68g​	157kcal​
8​	Pistacios	1oz​	7.93g​	12.60g​	5.84g​	2.17g​	158kcal​
9​	Walnuts	1oz​	3.89g​	18.49g​	4.32g​	0.74g​	185kcal​
10​	Tilapia	1oz​	0.00g​	0.48g​	5.69g​	0.00g​	27kcal​

Sheet: Meats, Eggs, Nuts

This is the sheet that has the reference table that correlates food category to appropriate table:

Unknown 64 bit

	A​	B​
1​	Category​	Table​
2​	​	​
3​	Bread_Cereal_Rice_Pasta	Table1
4​	Vegetables	Table2
5​	Fruit	Table3
6​	Meats_Eggs_Nuts	Table4
7​	Milk_Yogurt_Cheese	Table5
8​	Misc	Table7
9​	Beverage	Table6

Sheet: Reference Tables

 

[Keeper4826]

To use the spreadsheet, the user will first select a category from a dropdown list in column B (B3 in the example). This will dynamically affect the choices presented in a dropdown list in column C. For example: select Fruit in B, you get a list of fruits in C. Change the selection to Vegetable, and the dropdown in C becomes a list of vegetables. This is currently working fine.

The formula I'm trying to build is in columns E, F, G, H, and I. The search criteria is in C, and B determines which sheet (which has a named table, such as Table4) the search is to be completed.

 

[offthelip]

this code should do it for you. I have had to make a few assumptions as to exactly which columns things are in , but hopefully you can debug those if they are not correct;

Sub gettable()

Dim tbl As ListObject
Dim sht As Worksheet
With Worksheets("Example")
 lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
 Meals = Range(.Cells(1, 1), .Cells(lastrow, 3))
 outarr = Range(.Cells(1, 3), .Cells(lastrow, 9))
End With


With Worksheets("Reference Tables")
 lastref = .Cells(Rows.Count, "A").End(xlUp).Row
 tableref = Range(.Cells(1, 1), .Cells(lastref, 3))
End With


' loop through the meals in Example
 For i = 3 To lastrow
  ' find table for category
    Category = Meals(i, 2)
    For j = 3 To lastref
     ' I assume the list of categories is in column B on the Refence tables sheet
     If Category = tableref(j, 2) Then
      ' we have found the reference to pick up the table
       tablename = tableref(j, 3)
       Exit For
     End If
    Next j
 ' now go and find the table
 ' I assume we can't rely on the Category being exactly the same string as the
 ' the tab name, because it it was it would save searching the workbook for hte table
 
     
'Loop through each sheet and table in the workbook
  tablefnd = False
  For Each sht In ThisWorkbook.Worksheets
    For Each tbl In sht.ListObjects
      tname = tbl.Name
      If tname = tablename Then
      ' we have found the table so load it into a variant array
      table1arr = sht.ListObjects(tablename).Range
      ' now search the table for the ingredient
       For k = 2 To UBound(table1arr)
         If Meals(i, 3) = table1arr(k, 2) Then
          ' we have found the ingredient so copy all the data across
           For mm = 1 To 6
            outarr(i, mm) = table1arr(k, 2 + mm)
           Next mm
         End If
       Next k
         tablefnd = True
         Exit For
       End If
      
    Next tbl
    If tablefnd Then Exit For
  Next sht
 Next i
 With Worksheets("Example")
  Range(.Cells(1, 3), .Cells(lastrow, 9)) = outarr
End With




End Sub

 

[Keeper4826]

Thanks for continuing to help. I'm trying to work with the code you presented. As I said, it's been a long time since I've messed around with functions and VBA. Having to re-teach myself as I go through it. LOL . I see a couple things that need to be addressed.

' I assume the list of categories is in column B on the Refence tables sheet.
*** No. Column A in the "Reference Tables" sheet is food categories. Column B is the corresponding table for the selected food category.

' I assume we can't rely on the Category being exactly the same string as the
' the tab name, because it it was it would save searching the workbook for hte table
*** Correct. I found that having multiple words in either my sheet name or dropdown list caused problem when trying to run a function. That's why I replaced commas and spaces with an underscore.

 

[Keeper4826]

OK, I've created the module using your code and attempted to run in a couple times - with not apparent results. I stepped through each line line, observing the results in the Locals Window.

1. It appears that this code is attempting to run against the sheet (a total of 49 rows) rather than simple running against the current row being inputted. My intent is not to input all the categories and food items and then run the macro to populate the nutrients data. I want the nutrients data to get inputted as each individual food item is selected (there's more math that will impact this once I figure out/understand how to pull this off). Then then user will move on to the next row and enter a new item.

2. None of the nutrient data is being populated into the sheet on "Example." I can't figure out why. With 49 interactions, its hard to track where the breakdown is. Or if there is code missing.

3. You'll note there is a row with "Total" in column A. This row automatically totals up the nutrient data in each column. This row, along with the first two, should not be impacted by the macro. In the example I showed, it only included "breakfast." This is what the full sheet looks like:

Unknown 64 bit

	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​	74.00g​
4​	​	Milk_Yogurt_Cheese​	Milk (non-fat)
5​	​	​
6​	​	​
7​	​	​
8​	​	​
9​	​	​
10​	​	​
11​
12​	Total				0.00g​	0.00g​	0.00g​	0.00g​	0.00g​
13​	​	​	​	​	​	​	​	​	​
14​	Lunch​	​
15​	​	​
16​	​	​
17​	​	​
18​	​	​
19​	​	​
20​	​	​
21​	​	​
22​
23​	Total				0.00g​	0.00g​	0.00g​	0.00g​	0.00g​
24​	​	​	​	​	​	​	​	​	​
25​	Snack​	​
26​	​	​
27​	​	​
28​	​	​
29​	​	​
30​	​	​
31​	​	​
32​	​	​
33​
34​	Total				0.00g​	0.00g​	0.00g​	0.00g​	0.00g​
35​	​	​	​	​	​	​	​	​	​
36​	Dinner​	​
37​	​	​
38​	​	​
39​	​	​
40​	​	​
41​	​	​
42​	​	​
43​	​	​
44​
45​	Total				0.00g​	0.00g​	0.00g​	0.00g​	0.00g​
46​	​	​	​	​	​	​	​	​	​
47​	DAILY TOTALS				0.00g​	0.00g​	0.00g​	0.00g​	0.00g​
48​	DAILY GOALS
49​	PROGRESS				0.00g​	0.00g​	0.00g​	0.00g​	0.00g​
50​

Sheet: Example

 

[offthelip]

No. Column A in the "Reference Tables" sheet is food categories. Column B is the corresponding table for the selected food category.

This will definitely cause it to fail because it won't be able to find any matches. You need to change the following two lines of code to pick columns a and b

    ' I assume the list of categories is in column A on the Refence tables sheet    
     If Category = tableref(j, 1) Then
      ' we have found the reference to pick up the table
       tablename = tableref(j, 2)

I tend to use number references for columns all the time becaue it is much easier to index through the rows and columns using numbers particularly in variant arrays where letter references for columns won't work

Can you make this change and try the code as it is to see whether I have got the "lookup" coding correct, before we try to modify it to your exact requirements. (Don't worry about the totals bit we will deal with that later)
To change the code to update the ingredients as the user enters the data can be done in two ways.
1: Put some code into the worksheet change event subroutine which will do the lookup
2: create a special user defined function to do the lookup
Both of these could end up making the workbook rather slow. the main reason this routine is slow is because of needing to search through the whole workbook to find which table to use. this will be done multiple times with either of the two soluitions while the user is entering data, so speed is of importance
Thus it would be much better if you could add the name of the worksheet which each table is on into the Reference table. This could be added as a third column. Is this possible?

 

[Keeper4826]

Is there a way to save the data in the locals window so I can share what happens when I run the macro?

 

[offthelip]

I don't know , I have never needed to do that, probably because I am usually just posting code