# Multiple sheet, relational lookups into aggregate function

Based on feedback in comments, this is a complete rewrite of the post, including the title.

I am trying to create a Google Sheets document with 3 sheets (tabs). Two of the sheets are static data while the third is mostly populated with formulas based on the other two sheets. I can do most of what I need, but there is one thing I am trying to do, but can't seem to get right.

The first sheet with static data is the "Recipes" tab. It contains all of the recipes for products. The only "products" are Bikes and Cars in these examples. It also contains the quantity of the output produced per process (so this can be more than 1) along with the required input materials and the quantity required of those materials per process cycle.

So if a bike uses 2 wheels, and you're making 10 bikes per process, you will need 20 wheels total.

``````      A        B                  C              D
1     Output   Output Quantity    Input          Input Quantity
2     Bike     10                 Wheel                  20
3     Bike     10                 Breaks                 20
4     Bike     10                 Handle Bar             10
5     Bike     10                 Seat                   10
6     Car      5                  Wheel                  20
7     Car      5                  Breaks                 20
8     Car      5                  Seat                   20
9     Car      5                  Steering Wheel         5
``````

The second sheet with static data is the "Item Info" sheet. It contains a list of all items (both inputs and outputs) along with the size and price of the part/product. The order can not be relied upon and the same items may be used multiple times in different recipes:

``````      A               B               C
1     Product         Size Per Unit   Price Per Unit
2     Bike            50              200
3     Car             1000            25000
4     Wheel           10              50
5     Breaks          5               25
6     Handle Bar      5               30
7     Seat            25              50
8     Steering Wheel  15              100
``````

What I'm trying to get, is a in a third sheet, to be able to list multiple products I can create (So either Bikes or Cars) and then on the same row pull in the total size of all the inputs for that item.

So the final result in the third sheet "Production Stats" should look like this:

``````        A       B
1       Output  Total Input Size
2       Bike    600
3       Car     875
``````

Where the "A" column is static, and the "B" column is a formula.

I am trying to get a formula which achieves the following: Uses the value from `'Production Stats'!A2:A` to do a look up against `'Recipes'!A2:A` to find all the items used in the recipe and the quantity used (So `'Recipes'!C2:D9`). Then for each "Input" do another lookup against `'Item Info'!A2:A` and get the size of those items (`'Item Info'!B2:B`). For each "Input" it should multiple the values from `'Recipes'!D2:D` with the values from `'Item Info'!B2:B`. Finally, it should add up all those values.

So if we were processing the formulas for `'Production Stats'!B2` the math would be:

``````Wheel     : 20 * 10 = 200
Breaks    : 20 * 5 = 100
Handle Bar: 10 * 5 = 50
Seat      : 10 * 25 = 250
Then      : 200 + 100 + 50 + 250 = 600
``````

So I know something like this would be able to get the "Input Quantity" for each input, each on its own row:

``````=filter(Recipes!D\$2:D, Recipes!A\$2:A=index(A2:A,match(A2,A2:A,0),1))
``````

And something like this would be able to get the size of each "Input", spread across 4 rows:

``````=filter('Item Info'!B\$2:B, 'Item Info'!A\$2:A=filter(Recipes!C\$2:C, Recipes!A\$2:A=index(A1:A,match(A1,A1:A,0),1)))
``````

So I was thinking something like either of these might work:

``````=SUM(filter(Recipes!D\$2:D, Recipes!A\$2:A=index(A2:A,match(A2,A2:A,0),1))*filter('Item Info'!B\$2:B, 'Item Info'!A\$2:A=filter(Recipes!C\$2:C, Recipes!A\$2:A=index(A2:A,match(A2,A2:A,0),1))))
``````

And:

``````=filter(SUM(Recipes!D\$2:D*filter('Item Info'!B\$2:B, 'Item Info'!A\$2:A=filter(Recipes!C\$2:C, Recipes!A\$2:A=index(A2:A,match(A2,A2:A,0),1)))), Recipes!A\$2:A=index(A2:A,match(A2,A2:A,0),1))
``````

But the first returns the value 200, which is the value for the first sequential "Input", excluding the rest. Then the second has an error "Array arguments to MULTIPLY are of different size". But when I use each argument on their own, they return the same number of rows.

So while I thought nested `filter()` calls was the way to achieve what I want, I realize it may not be the best way. So I'm open to alternatives. Is what I'm trying to do even possible? If so, what's the best way to go about it?

Much appreciation!

• To be clear up front, I am not volunteering to work on this issue myself. But I will suggest that rather than telling the volunteer community here what you are doing that isn't working with your formulas, just clearly explain the data goal (as if neither you nor anyone else knows anything about formulas). Clearly indicate which pieces of data you want to manually enter (and where), which pieces of data you want returned (and where), and how you arrived at each of those returns. Then let whoever here is willing decide the best formulaic approach (which is likely not FILTERS within FILTERS). Commented Aug 29, 2022 at 7:40
• @ErikTyler Thank you for the feedback. I tried to be detailed and outline what I have, what I expect, what I tried and what I'm actually getting. But I probably provided too much detail and made it overwhelming. Also, you make a great point, that nested `filters()` may not be the solution I need. So once I get home I'll revise my question and remove as much unnecessary information as I can and instead of asking how to use nested `filters()`, I'll simply ask what is the best way to achieve what I desire. Thank you for taking the time to provide feedback. Commented Aug 29, 2022 at 14:19

``````=filter('Item Info'!B6:B, 'Item Info'!A6:A=filter(Recipes!C6:C, Recipes!A6:A= index(A11:A,match(A15,A11:A,0),1)))
• Thank you @Daniele . This seems to work, but I see you're just moving the searchable range on the filters from the second column to the sixth column. Should the notation of "A2:A" allow the `FILTER()` to look in the entire A column starting with row 2? I can't reliably ensure the order of items in the "Item Info" sheet And in some cases the same items can be used for multiple recipes. So a sequential list isn't possible for my use-case. Is there a reason why the filter can't just search for matches in the entire column and has to be positioned to the exact row? Commented Aug 27, 2022 at 20:33