How to OR in my Index/Match/Indirect formula

[babettedv]

Hi There,

Is it possible to link my Index Match with Indirect formula (G3), which is already good, to multiple lines.
Currently my G2 in linked to C90, but I have more lined in which the codes could be, C90-C100 (see yellow highlighted cells). So the formula is not only applicable to row 90.

Would be great if you can help. Thanks!

Product Costing Master 251120.xlsx
	A	B	C	D	E	F	G	H	I
1							Mince Giv	Mince Giv	Mince Giv
2	COS	Raw Material Code	Description 1	Description 2	Supplier	Cost/kg	MFPC050.V1	MFPC048	MFPC057
3	Ingredients	RM008	554M - EXCEL FIT HI-CEL 554M (METHYL CELLULOSE SOLUTION)		FIT	10.95	0.1752
4	Ingredients	RM011.001	SUPRO 500 - SOYA PROTEIN ISOLATE - SUPRO 500	Soya protein	FIT	2.95	0.2643
5	Ingredients	RM014	R00003 - REFINED RAPESEED OIL	Oil	Kerfoot	1.2337	0.1875
6	Ingredients	RM018	ORAFTI HP		Kreglinger	4.78	0.0956
7	Ingredients	RM010.001	4410 - TEXTURED SOYA PROTEIN CONCENTRATE - DUPONT	Soya protein	FIT	3.38	-
8	Ingredients	RM010.003	CONTEX 18	Other	Solbar	1.84	-
CoS FLAT

Cell Formulas
Range	Formula
G3:G8	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)

Product Costing Master 251120.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
87
88
89
90		Product codes								MFPC050.V1		MFPC048		MFPC071
91												MFPC057
92												MFPC131
93												MFPC060
94												MFPC104
95												MFPC106
96
97
98
99
100
101
102
103
Mince Giv

 

[jasonb75]

Does this do what you need?

=SUMPRODUCT(INDIRECT("'"&G$1&"'!$C$8:$Y$100"),(INDIRECT("'"&G$1&"'!$C90:$Y90")=G$2)*(INDIRECT("'"&G$1&"'!$B$8:$B$100")=$C3))

 

[babettedv]

Hi Jason, thanks for you quick reply.
Sadly I get an #DIV/0!, so its not working
Is it possible to leave the start of "my formula" the same; SUMIFS(INDEX(INDIRECT("'"&G$1&"'!$C$8:$Y$100").....

Thanks,

 

[jasonb75]

If you're getting a #DIV/0! error then that would be caused by the source data, not this formula.

You said that you need multiple rows in the formula, but are there multiple applicable columns as well? If not, you could simply use index and match with columns instead of rows.

With multiple rows and columns, you need sumproduct.

 

[babettedv]

Hi Jason. To clarify
I would like to keep my formula in G3 as is, except for one part
As is:
=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)

To be:
=SUMIFS(INDEX(INDIRECT("'"&G$1&"'!$C$8:$Y$100"),0,MATCH(G$2,INDIRECT("'"&G$1&"'!$C90:$Y95"),0)),INDIRECT("'"&G$1&"'!$B$8:$B$100"),$C3)

the bold part is updated. But this formula doesn’t work so was hoping dat someone knows how to find a value over multiple rows (row 90-95) and columns (C-Y), so find value G2 in C90 OR C91 etc.

 

[jasonb75]

I would like to keep my formula in G3 as is, except for one part

It will never work regardless of what you try, which is why I suggested an alternative that will work (based on the example and the information provided).
The only reason for the suggested formula to fail is if you have defective data, which is beyond my control.