How to OR in my Index/Match/Indirect formula

babettedv

New Member
Joined
Jan 27, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHI
1Mince GivMince GivMince Giv
2COSRaw Material CodeDescription 1Description 2SupplierCost/kgMFPC050.V1MFPC048MFPC057
3IngredientsRM008554M - EXCEL FIT HI-CEL 554M (METHYL CELLULOSE SOLUTION)FIT10.950.1752
4IngredientsRM011.001SUPRO 500 - SOYA PROTEIN ISOLATE - SUPRO 500Soya proteinFIT2.950.2643
5IngredientsRM014R00003 - REFINED RAPESEED OILOilKerfoot1.23370.1875
6IngredientsRM018ORAFTI HPKreglinger4.780.0956
7IngredientsRM010.0014410 - TEXTURED SOYA PROTEIN CONCENTRATE - DUPONTSoya proteinFIT3.38-
8IngredientsRM010.003CONTEX 18OtherSolbar1.84-
CoS FLAT
Cell Formulas
RangeFormula
G3:G8G3=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
ABCDEFGHIJKLMN
87
88
89
90 Product codes MFPC050.V1MFPC048MFPC071
91MFPC057
92MFPC131
93MFPC060
94MFPC104
95MFPC106
96
97
98
99
100
101
102
103
Mince Giv
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,676
Office Version
  1. 365
Platform
  1. Windows
Does this do what you need?
Excel Formula:
=SUMPRODUCT(INDIRECT("'"&G$1&"'!$C$8:$Y$100"),(INDIRECT("'"&G$1&"'!$C90:$Y90")=G$2)*(INDIRECT("'"&G$1&"'!$B$8:$B$100")=$C3))
 
Solution

babettedv

New Member
Joined
Jan 27, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,676
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 27, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,676
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,175
Messages
5,640,607
Members
417,156
Latest member
Ciupanezul21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top