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

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:

RecipeIngredient CodeIngredient
2VE0001564Spinach
2VE0001215Mush
2VE0001669Butter
2VE0001293Oil
2VE0001584Salt

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

AreaIngredient NumberQuantity
KIT156410
KIT121520
KIT164330
KIT224840
KIT264550
KIT221560

<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
Joined
Jul 15, 2016
Messages
608
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
Joined
May 6, 2018
Messages
5
Hi!

Thanks for the reply.

Here's a condensed version of Sheet1:

ABC
1RecipeAreaRecipe Type
22VE0001Kit1Veg
32VE0002Kit2Veg
42CX0001Kit1Protein
52HL0001Kit2Protein (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 CodeInredient CodeIngredient Code
2VE0001123Mushrooms
2VE0001456Spinach
2VE0001789Garlic
2VE0002321Carrots
2VE0002654Onion
2VE0002987Celery

<tbody>
</tbody>

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

AreaIngredient
Kit1123
Kit1456
Kit1789
Kit2321
Kit2654
Kit3987

<tbody>
</tbody>
 

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
608
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></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #ED7D31;;">Recipe Code</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #ED7D31;;">Ingredient Code</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #ED7D31;;">Ingredient</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #ED7D31;;">Helper</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2VE0001</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Mushrooms</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kit1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2VE0001</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">456</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Spinach</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kit1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2VE0001</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">789</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Garlic</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kit1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2VE0002</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">321</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Carrots</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kit2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2VE0002</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">654</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Onion</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kit2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2VE0002</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">987</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Celery</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kit2</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=IF(<font color="Blue">C2="","",INDEX(<font color="Red">Sheet1!$B$2:$B$5,MATCH(<font color="Green">Sheet2!A2,Sheet1!$A$2:$A$5,0</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #ED7D31;;">Area</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #ED7D31;;">Ingredient</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #4472C4;;">Sheet2 Recipes Count</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kit1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kit1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">456</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kit1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">789</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #4472C4;;">Not Proteins Count</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kit2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">321</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kit2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">654</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kit2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">987</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #4472C4;;">Recipes Non-Proteins</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #4472C4;;">Count</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2VE0001</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2VE0002</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=SUM(<font color="Blue">E8:E9</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D5</th><td style="text-align:left">=COUNTA(<font color="Blue">Sheet1!$C$2:$C$5</font>)-(<font color="Blue">COUNTIF(<font color="Red">Sheet1!$C$2:$C$5,"Protein"</font>)+COUNTIF(<font color="Red">Sheet1!$C$2:$C$5,"Protein (Halal)"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E8</th><td style="text-align:left">=COUNTIF(<font color="Blue">Sheet2!$A$2:$A$7,Sheet3!$D8</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A2</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">$A$2:A2</font>)>$D$2,"",INDEX(<font color="Red">Sheet2!$D$2:$D$7,SMALL(<font color="Green">IF(<font color="Purple">OR(<font color="Teal">Sheet2!$A$2:$A$7=Sheet3!$D$8,Sheet2!$A$2:$A$7=Sheet3!$D$9</font>),ROW(<font color="Teal">Sheet2!$A$2:$A$7</font>)-ROW(<font color="Teal">Sheet2!$A$2</font>)+1</font>),ROWS(<font color="Purple">$A$2:A2</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">$A$2:A2</font>)>$D$2,"",INDEX(<font color="Red">Sheet2!$B$2:$B$7,SMALL(<font color="Green">IF(<font color="Purple">OR(<font color="Teal">Sheet2!$A$2:$A$7=Sheet3!$D$8,Sheet2!$A$2:$A$7=Sheet3!$D$9</font>),ROW(<font color="Teal">Sheet2!$A$2:$A$7</font>)-ROW(<font color="Teal">Sheet2!$A$2</font>)+1</font>),ROWS(<font color="Purple">$A$2:A2</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D8</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">Sheet3!$D$8:D8</font>)>$D$5,"",INDEX(<font color="Red">Sheet1!$A$2:$A$5,SMALL(<font color="Green">IF(<font color="Purple">NOT(<font color="Teal">Sheet1!$C$2:$C$5="Protein"</font>),IF(<font color="Teal">NOT(<font color="#FF00FF">Sheet1!$C$2:$C$5="Protein (Halal)"</font>),ROW(<font color="#FF00FF">Sheet1!$C$2:$C$5</font>)-ROW(<font color="#FF00FF">Sheet1!$C$2</font>)+1</font>)</font>),ROWS(<font color="Purple">Sheet3!$D$8:D8</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Last edited:

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top