Index Match on multiple columns

[babettedv]

Hi there,

My formula in H1 is not working, I should get a number, could someone help?
I am trying to find a product code in a different sheet which could be in multiple columns, of which the ingredients should SUM.

Product Costing Master 251120.xlsx
	A	B	C			F	G	H
1							Mince Giv	Mince Giv
2	COS	Raw Material Code	Description 1			Cost/kg	MFPC050.V1	MFPC048
3	Ingredients	RM008	554M - EXCEL FIT HI-CEL 554M (METHYL CELLULOSE SOLUTION)			10.95	0.1752	-
4	Ingredients	RM011.001	SUPRO 500 - SOYA PROTEIN ISOLATE - SUPRO 500			2.95	0.2643	-
5	Ingredients	RM014	R00003 - REFINED RAPESEED OIL			1.233696	0.1875	-
CoS FLAT

Cell Formulas
Range	Formula
G3:G5	G3	=SUMIFS(INDEX(INDIRECT("'"&G$1&"'!$C$8:$Y$100"),0,MATCH(G$2,INDIRECT("'"&G$1&"'!$C90:$Y90"),0)),INDIRECT("'"&G$1&"'!$B$8:$B$100"),$C3)
H3:H5	H3	=SUMIFS(INDEX(INDIRECT("'"&H$1&"'!$C$8:$Y$100"),0,MATCH(1,MMULT(--(INDIRECT("'"&H$1&"'!$C90:$Y95")=H$2),TRANSPOSE(COLUMN(INDIRECT("'"&H$1&"'!$C90:$Y95"))^0)),0)),INDIRECT("'"&H$1&"'!$B$8:$B$100"),$C3)

Product Costing Master 251120.xlsx
	B	C	D	E	F	G	H	I	J	K	L
88
89
90	Product codes								MFPC050.V1		MFPC048
91											MFPC057
92											MFPC131
93											MFPC060
94											MFPC104
95											MFPC106
Mince Giv

Thanks,

 

[KRice]

What is your expected answer in H3?...and have you entered the H column formula as an array formula, confirmed with Control-Shift-Enter?

 

[babettedv]

Hi there, the problem is with column H3 - I dont get a value there! As the product codes are spread over columns and rows on the other sheet?
The formula in column G3 works already perfectly, but thanks anyway

Looking forward to hear from you!

 

[Fluff]

Duplicate to: Index Match - one criteria - that can be in multiple ROWS and Columns

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.