Meth0dman27
New Member
- Joined
- May 6, 2018
- Messages
- 5
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:
<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.
<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!
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!