Index Match on multiple columns

Status
Not open for further replies.

babettedv

New Member
Joined
Jan 27, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
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
ABCFGH
1Mince GivMince Giv
2COSRaw Material CodeDescription 1Cost/kgMFPC050.V1MFPC048
3IngredientsRM008554M - EXCEL FIT HI-CEL 554M (METHYL CELLULOSE SOLUTION)10.950.1752-
4IngredientsRM011.001SUPRO 500 - SOYA PROTEIN ISOLATE - SUPRO 5002.950.2643-
5IngredientsRM014R00003 - REFINED RAPESEED OIL1.2336960.1875-
CoS FLAT
Cell Formulas
RangeFormula
G3:G5G3=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:H5H3=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
BCDEFGHIJKL
88
89
90 Product codes MFPC050.V1MFPC048
91MFPC057
92MFPC131
93MFPC060
94MFPC104
95MFPC106
Mince Giv



Thanks,
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
What is your expected answer in H3?...and have you entered the H column formula as an array formula, confirmed with Control-Shift-Enter?
 
Last edited:

babettedv

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
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.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,129,931
Messages
5,639,064
Members
417,067
Latest member
rohitbabshet

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