SUMIF INDEX MATCH across multiple sheets

[7527Amanda]

Hi,

i have three worksheets


1. worksheet "ingredientmatrix" x ingredient, y criteria i.e. column 24 states if the ingredient is of australian origin

https://www.dropbox.com/s/oji26p54i8ky7jz/Capture ingmatrix.JPG?dl=0

2. worksheet "recipesheet" x recipe, y ingredient with the quantities in the body

https://www.dropbox.com/s/3317danbplyep96/Capture recipe sheet.JPG?dl=0

I need a formula that will lookup sheets and

the following formula is currently looking up the "recipesheet" for a particular recipe and returning a value of an ingredient that is currently in a fixed position in of a working worksheet at v3.

=INDEX('[132.00 Ingredient and Allergen Matrix DRAFT.xlsx]Recipe Sheet'!$A$1:$BK$67,MATCH(VLOOKUP(REPLACE($K$1,4,1,"e"),'Product Specifications'!A:AB,3,FALSE),'[132.00 Ingredient and Allergen Matrix DRAFT.xlsx]Recipe Sheet'!$A$1:$A$67,0),MATCH('Product Information Form'!V3,'[132.00 Ingredient and Allergen Matrix DRAFT.xlsx]Recipe Sheet'!$A$1:$BK$1,0))

But what I need it to do is find sum all of the ingredients of that recipe that are of australian origin.

this returns if the one ingredient in v3 is australian
=VLOOKUP(V3,'[132.00 Ingredient and Allergen Matrix DRAFT.xlsx]132-01 Ingredient Matrix'!$A$3:$AA$76,27,FALSE)

I just don't know how to tell it to lookup all the ingredients for a recipe in the "recipesheet".

Any guidance would be much appreciated.
Amanda

 

[istiasztalos]

Hi Amanda,

If V3 has only one ingredient, where are the others that you want to check, do the surrounding cells, like V4, V5, etc... list all ingredients of that particular recipe?

Or do you want to add a column at the end of the recipe sheet that yes YES/NO whether all ingredients are Australian?

Thanks,

 

[7527Amanda]

Hi

the recipes are in the worksheet "recipesheet" with the recipe codes listed down column A3:A67 and all ingredient codes along row C1:BK1. i.e. There are only values in the body if that ingredient is in that recipe. I have attached a picture above if you can view it.

The formula will need to go into a another spreadsheet with the following product information form in the section for country of origin. I will also have to get it to say if the recipe is halal, organic, vegan etc using a similar formula that wont have to calculate but look up if they all have a yes in that row.

https://www.dropbox.com/s/6rscgq3px6bmv4l/Capture pif.JPG?dl=0

thanks

 

[istiasztalos]

Hi,

To my knowledge traditional lookup formulas, like INDEX, VLOOKUP don't allow looking up multiple elements at the same time, so I drafted up this one to circumvent this limitation:

=IF(SUM(MMULT(IF(ingredientslist=IF(OFFSET(ingredientsrow,MATCH(thisrecipe,recipelist,0),0)<>"",ingredientsrow),1,0),ROW(ingredientslist)^0)
*IF(origincolumn="Australia",1,0))=COUNTA(OFFSET(ingredientsrow,MATCH(thisrecipe,recipelist,0),0)),"Yes","No")

Past this in one row, it's just too long to fit here so I broke it in two pieces.

For the ranges, don't put the entire columns, just the actual list of ingredients/recipes (ingredients list has to have same number of elements as ingredients row)

THis is an array formula, so you'll need to add with CTRL+SHIFT+ENTER (Excel will add { } around it)



This will find ingredients regardless of their order in the columns where they are listed, if we can be sure that they are always exactly in the same order as the columns ont he recipesheet then it would be somewhat simpler

To check properties other than the origin, change this part, origincolumn="Australia" with any column range reference and the desired criteria, like "Y"

 

[7527Amanda]

Hello,

Thank you.

I have tried placing the variables in the formula and made it an array. Are you able to look at the items below and give me an indication of why it isn't working? Also could you advise what the Yes and No at the end of the formula refers to as there is no yes or no in the spreadsheet and I need it to return a summed value of the quantities.

ingredientslist - '[132.00 Ingredient and Allergen Matrix.xlsx]Recipe Sheet'!$C$3:BK67
recipelist - '[132.00 Ingredient and Allergen Matrix.xlsx]Recipe Sheet'!$A$3:$A67
thisrecipe - (VLOOKUP(REPLACE($K$1,4,1,"e"),'Product Specifications'!A:AB,3,FALSE))
Ingredientsrow - 'P:\Forms\[132.00 Ingredient and Allergen Matrix DRAFT.xlsx]Recipe Sheet'!$c$1:BK$1

=IF(SUM(MMULT(IF(‘[132.00 Ingredient and Allergen Matrix.xlsx]Recipe Sheet’!$C$3:BK67=IF(OFFSET(‘P:\Forms\[132.00 Ingredient and Allergen Matrix DRAFT.xlsx]Recipe Sheet’!$c$1:BK$1,MATCH((VLOOKUP(REPLACE($K$1,4,1,”e”),’Product Specifications’!A:AB,3,FALSE)),’[132.00 Ingredient and Allergen Matrix.xlsx]Recipe Sheet’!$A$3:$A67,0),0)<>"",’P:\Forms\[132.00 Ingredient and Allergen Matrix DRAFT.xlsx]Recipe Sheet’!$c$1:BK$1),1,0),ROW(‘[132.00 Ingredient and Allergen Matrix.xlsx]Recipe Sheet’!$C$3:BK67)^0), IF(origincolumn="Australia",1,0))=COUNTA(OFFSET(‘P:\Forms\[132.00 Ingredient and Allergen Matrix DRAFT.xlsx]Recipe Sheet’!$c$1:BK$1,MATCH((VLOOKUP(REPLACE($K$1,4,1,”e”),’Product Specifications’!A:AB,3,FALSE)),’[132.00 Ingredient and Allergen Matrix.xlsx]Recipe Sheet’!$A$3:$A67,0),0)),"Yes","No")

Thank you

 

[istiasztalos]

Hi,

Sorry I wasn't clear on what these names refer to

- 'origincolumn' is a variable range as well, it refers to the column in the ingredient matrix that tells the country of origin.
- ingredientslist should be just one column, with ingredient names, in the matrix where their properties are shown (halal, organic, vegan, origin, etc...)
- number of rows in ingredientlist has to be same number of columns in ingredients row (I assume the same ingredients are available for the recipes as the ones in the allergen matrix)
- ingredientrow is the header row showing all the possible ingredients of the recipes

I would suggest to save these names as named ranges in Excel and use the names in the formula, it would be easier to troubleshoot afterwards

The formula check how many of the ingredients in the recipe have Australia in this column and whether this is equal to the total number of ingredients int he recipe. If they equal (so all ingredients are from Australia) then it returns "Yes", other wise it returns "No"; you can change these words at the end of the formula depending on what you want as output if all ingredients meet the criteria. (same logic for halal, organic, vegan, etc...)

 

[7527Amanda]

Hi,

ok I see, i did misread a few things and the ingredient row and column are now the same size and have entered control,shift&enter at end for the array but still can't seem to get it to work. Any thoughts?
Also I just need it to sum if the ingredients are australian, they all don't have to be australian but it only needs to sum those that are australian.

ingredientslist -'[132.00 Ingredient and Allergen Matrix.xlsx]132-01 Ingredient Matrix'!itemcode
recipelist -'[132.00 Ingredient and Allergen Matrix.xlsx]Recipe Sheet'!$A$3:$A67
thisrecipe -(VLOOKUP(REPLACE($K$1,4,1,"e"),'Product Specifications'!A:AB,3,FALSE))
Ingredientsrow -'[132.00 Ingredient and Allergen Matrix.xlsx]Recipe Sheet'!ingredientrow
Origincolumn -'[132.00 Ingredient and Allergen Matrix.xlsx]132-01 Ingredient Matrix'!$AA$3:$AA64

=IF(SUM(MMULT(IF(‘[132.00 Ingredient and Allergen Matrix.xlsx]132-01 Ingredient Matrix’!itemcode=IF(OFFSET(‘[132.00 Ingredient and Allergen Matrix.xlsx]Recipe Sheet’!ingredientrow,MATCH((VLOOKUP(REPLACE($K$1,4,1,”e”),’Product Specifications’!A:AB,3,FALSE)),’[132.00 Ingredient and Allergen Matrix.xlsx]Recipe Sheet’!$A$3:$A67,0),0)<>"",’[132.00 Ingredient and Allergen Matrix.xlsx]Recipe Sheet’!ingredientrow),1,0),ROW(‘[132.00 Ingredient and Allergen Matrix.xlsx]132-01 Ingredient Matrix’!itemcode)^0), IF(‘[132.00 Ingredient and Allergen Matrix.xlsx]132-01 Ingredient Matrix’!$AA$3:$AA64="Australia",1,0))=COUNTA(OFFSET(‘[132.00 Ingredient and Allergen Matrix.xlsx]Recipe Sheet’!ingredientrow,MATCH((VLOOKUP(REPLACE($K$1,4,1,”e”),’Product Specifications’!A:AB,3,FALSE)),’[132.00 Ingredient and Allergen Matrix.xlsx]Recipe Sheet’!$A$3:$A67,0),0)),"Yes","No")

 

[istiasztalos]

Hi Amanda,

To just count Australian ones you can take the first part before the equal sign

I checked your version and corrected one thing, where I broke it onto a second line, it is *, the two arrays are multiplied

=IF(SUM(MMULT(IF(‘[132.00 Ingredient and Allergen Matrix.xlsx]132-01 Ingredient Matrix’!itemcode=IF(OFFSET(‘[132.00 Ingredient and Allergen Matrix.xlsx]Recipe Sheet’!ingredientrow,MATCH((VLOOKUP(REPLACE($K$1,4,1,”e”),’Product Specifications’!A:AB,3,FALSE)),’[132.00 Ingredient and Allergen Matrix.xlsx]Recipe Sheet’!$A$3:$A67,0),0)<>"",’[132.00 Ingredient and Allergen Matrix.xlsx]Recipe Sheet’!ingredientrow),1,0),ROW(‘[132.00 Ingredient and Allergen Matrix.xlsx]132-01 Ingredient Matrix’!itemcode)^0)*IF(‘[132.00 Ingredient and Allergen Matrix.xlsx]132-01 Ingredient Matrix’!$AA$3:$AA64="Australia",1,0))

If still not ok, please tell me what exactly is the range for your named ranges

 

[7527Amanda]

sorry not sure why it did post.
- what do you mean by "take the first part before the equal sign"?
- here are the ranges:

- 'origincolumn' ='[132.00 Ingredient and Allergen Matrix.xlsx]'132-01 Ingredient Matrix'!$AA$3:$AA$63
- ingredientslist ='[132.00 Ingredient and Allergen Matrix.xlsx]'132-01 Ingredient Matrix'!$A$3:$A$63
- ingredientrow ='[132.00 Ingredient and Allergen Matrix.xlsx]'132.03 Recipe Sheet'!$C$1:$BK$1
- recipelist ='[132.00 Ingredient and Allergen Matrix.xlsx]'132.03 Recipe Sheet'!$C$3:$BK$67
- thisrecipe =(VLOOKUP(REPLACE($K$1,4,1,"e"),'Product Specifications'!A:AB,3,FALSE))