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

Meth0dman27

New Member
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:

 Recipe Ingredient Code Ingredient 2VE0001 564 Spinach 2VE0001 215 Mush 2VE0001 669 Butter 2VE0001 293 Oil 2VE0001 584 Salt

<tbody>
</tbody>

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.

 Area Ingredient Number Quantity KIT1 564 10 KIT1 215 20 KIT1 643 30 KIT2 248 40 KIT2 645 50 KIT2 215 60

<tbody>
</tbody>

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

Well-known Member
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

New Member
Hi!

Thanks for the reply.

Here's a condensed version of Sheet1:

 A B C 1 Recipe Area Recipe Type 2 2VE0001 Kit1 Veg 3 2VE0002 Kit2 Veg 4 2CX0001 Kit1 Protein 5 2HL0001 Kit2 Protein (Halal)

<tbody>
</tbody>

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

Code:
``[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:

 Recipe Code Inredient Code Ingredient Code 2VE0001 123 Mushrooms 2VE0001 456 Spinach 2VE0001 789 Garlic 2VE0002 321 Carrots 2VE0002 654 Onion 2VE0002 987 Celery

<tbody>
</tbody>

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

 Area Ingredient Kit1 123 Kit1 456 Kit1 789 Kit2 321 Kit2 654 Kit3 987

<tbody>
</tbody>

RasGhul

Well-known Member
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)

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

Last edited:

1,082,099
Messages
5,363,129
Members
400,720
Latest member
Pettel

This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...