Having Trouble Tackling a larger formula: need to return multiple values based on multiple criteria

[Meth0dman27]

Hi All

I have a fairly tricky situation that I'm trying to solve that I'm not entirely sure is possible. I'll give a brief explanation below but if anyone wants to attempt it I can give a more detailed examples of the data.

I am trying to create a list that generates the items I need, in Sheet3, using data that is dispersed through sheets 1 and 2. Essentially I need a formula that will be able to do a "vlookup" based on multiple criteria but also be able to return multiple criteria. Further explanation below:

Sheet1 contains 3 pieces of relevant info: Recipe Code, Area, Recipe Type. In this instance, recipe type refers to protein, starch, veg, sauce, protein (halal). I am specifically looking for only non protein items, so I started my if stated with: =if(not(or(q2="Protein",q2="Protein (Halal)), ...... , .......)

Sheet2 Contains the breakdown of the recipe codes and shows the ingredients used via ingredient codes. Ingredient codes are what I'm trying to populated in sheet3. In this sheet, Column A is recipe code from sheet1, column B is ingredient code. Because each recipe has multiple ingredients it looks as such:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Recipe[/TD]
[TD]Ingredient Code[/TD]
[TD]Ingredient[/TD]
[/TR]
[TR]
[TD]2VE0001[/TD]
[TD]564[/TD]
[TD]Spinach[/TD]
[/TR]
[TR]
[TD]2VE0001[/TD]
[TD]215[/TD]
[TD]Mush[/TD]
[/TR]
[TR]
[TD]2VE0001[/TD]
[TD]669[/TD]
[TD]Butter[/TD]
[/TR]
[TR]
[TD]2VE0001[/TD]
[TD]293[/TD]
[TD]Oil[/TD]
[/TR]
[TR]
[TD]2VE0001[/TD]
[TD]584[/TD]
[TD]Salt[/TD]
[/TR]
</tbody>[/TABLE]

Because the "2VE0001" is on multiple lines a regular vlookup doesn't work as I may need to return multiple values

Sheet3 is where the data needs to be populated. This is also where another criteria I need to use comes into play. In this sheet I divide those ingredients further by "Area". So for example Area "Kit1" might have codes 2VE0001,2VE0002,2VE0003, and those 3 recipes might have 10 ingredients. I would need all 10 of those ingredients based on the area. Below is an example to give you an idea what I mean.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Area[/TD]
[TD]Ingredient Number[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]KIT1[/TD]
[TD]564[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]KIT1[/TD]
[TD]215[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]KIT1[/TD]
[TD]643[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]KIT2[/TD]
[TD]248[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]KIT2[/TD]
[TD]645[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]KIT2[/TD]
[TD]215[/TD]
[TD]60[/TD]
[/TR]
</tbody>[/TABLE]

So essentially the info I need is column B on the last table, which are obtained from sheet 2 using criteria from sheets 1 & 3. I realize this is probably very confusing but any help you might have would be very much appreciated!

 

[RasGhul]

Hi Method,

Can you post examples of the data from Sheet 1 and confirm what is the criteria you will choose from both requirements?

I assume you will choose e.g. "2VE0001", and Kit1 as criteria?

 

[Meth0dman27]

Hi!

Thanks for the reply.

Here's a condensed version of Sheet1:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Recipe[/TD]
[TD]Area[/TD]
[TD]Recipe Type[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2VE0001[/TD]
[TD]Kit1[/TD]
[TD]Veg[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2VE0002[/TD]
[TD]Kit2[/TD]
[TD]Veg[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2CX0001[/TD]
[TD]Kit1[/TD]
[TD]Protein[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2HL0001[/TD]
[TD]Kit2[/TD]
[TD]Protein (Halal)[/TD]
[/TR]
</tbody>[/TABLE]

To answer your question, yes I would need to use Kit1 and 2VE0001 as criteria. from here I'm looking for anything in column A that is not either protein or protein (halal). So to filter those out I was thinking of start an IF statement like below

[COLOR=#574123]not(or(q2="Protein",q2="Protein (Halal))[/COLOR]

Once I have those recipe codes, I need to then reference sheet 2 to get my ingredient codes.

This would be sheet2:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Recipe Code[/TD]
[TD]Inredient Code[/TD]
[TD]Ingredient Code[/TD]
[/TR]
[TR]
[TD]2VE0001[/TD]
[TD]123[/TD]
[TD]Mushrooms[/TD]
[/TR]
[TR]
[TD]2VE0001[/TD]
[TD]456[/TD]
[TD]Spinach[/TD]
[/TR]
[TR]
[TD]2VE0001[/TD]
[TD]789[/TD]
[TD]Garlic[/TD]
[/TR]
[TR]
[TD]2VE0002[/TD]
[TD]321[/TD]
[TD]Carrots[/TD]
[/TR]
[TR]
[TD]2VE0002[/TD]
[TD]654[/TD]
[TD]Onion[/TD]
[/TR]
[TR]
[TD]2VE0002[/TD]
[TD]987[/TD]
[TD]Celery[/TD]
[/TR]
</tbody>[/TABLE]

Finally, I would then need to have them placed into sheet 3 like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Area[/TD]
[TD]Ingredient[/TD]
[/TR]
[TR]
[TD]Kit1[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]Kit1[/TD]
[TD]456[/TD]
[/TR]
[TR]
[TD]Kit1[/TD]
[TD]789[/TD]
[/TR]
[TR]
[TD]Kit2[/TD]
[TD]321[/TD]
[/TR]
[TR]
[TD]Kit2[/TD]
[TD]654[/TD]
[/TR]
[TR]
[TD]Kit3[/TD]
[TD]987[/TD]
[/TR]
</tbody>[/TABLE]

 

[RasGhul]

Hi Method,

Maybe you could use this. Sheet 2 has a helper Column to bring the Area code into Sheet 2.

Column D counts the criteria for the array formulas.

Increase the ranges to suit your data. You will also need to increase the number of OR conditions in this line to match your "Not Proteins count"

SMALL(IF(OR(Sheet2!$A$2:$A$7=Sheet3!$D$8,Sheet2!$A$2:$A$7=Sheet3!$D$9)

Book1
	A	B	C	D
1	Recipe Code	Ingredient Code	Ingredient	Helper
2	2VE0001	123	Mushrooms	Kit1
3	2VE0001	456	Spinach	Kit1
4	2VE0001	789	Garlic	Kit1
5	2VE0002	321	Carrots	Kit2
6	2VE0002	654	Onion	Kit2
7	2VE0002	987	Celery	Kit2
Sheet2

Cell Formulas
Range	Formula
D2	=IF(C2="","",INDEX(Sheet1!$B$2:$B$5,MATCH(Sheet2!A2,Sheet1!$A$2:$A$5,0)))

Book1
	A	B	C	D	E
1	Area	Ingredient		Sheet2 Recipes Count
2	Kit1	123		6
3	Kit1	456
4	Kit1	789		Not Proteins Count
5	Kit2	321		2
6	Kit2	654
7	Kit2	987		Recipes Non-Proteins	Count
8				2VE0001	3
9				2VE0002	3
Sheet3

Cell Formulas
Range	Formula
D2	=SUM(E8:E9)
D5	=COUNTA(Sheet1!$C$2:$C$5)-(COUNTIF(Sheet1!$C$2:$C$5,"Protein")+COUNTIF(Sheet1!$C$2:$C$5,"Protein (Halal)"))
D8	{=IF(ROWS(Sheet3!$D$8:D8)>$D$5,"",INDEX(Sheet1!$A$2:$A$5,SMALL(IF(NOT(Sheet1!$C$2:$C$5="Protein"),IF(NOT(Sheet1!$C$2:$C$5="Protein (Halal)"),ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(Sheet3!$D$8:D8))))}
E8	=COUNTIF(Sheet2!$A$2:$A$7,Sheet3!$D8)
A2	{=IF(ROWS($A$2:A2)>$D$2,"",INDEX(Sheet2!$D$2:$D$7,SMALL(IF(OR(Sheet2!$A$2:$A$7=Sheet3!$D$8,Sheet2!$A$2:$A$7=Sheet3!$D$9),ROW(Sheet2!$A$2:$A$7)-ROW(Sheet2!$A$2)+1),ROWS($A$2:A2))))}
B2	{=IF(ROWS($A$2:A2)>$D$2,"",INDEX(Sheet2!$B$2:$B$7,SMALL(IF(OR(Sheet2!$A$2:$A$7=Sheet3!$D$8,Sheet2!$A$2:$A$7=Sheet3!$D$9),ROW(Sheet2!$A$2:$A$7)-ROW(Sheet2!$A$2)+1),ROWS($A$2:A2))))}
Press CTRL+SHIFT+ENTER to enter array formulas.