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

babettedv

New Member
Joined
Jan 27, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
I would like to Match to a criteria that is in a cell that is an array of multiple columns AND rows, instead of one row.
How can I do my Index Match formula?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
An example:
search in matrix.xlsx
ABCDEFG
1col 1col 2col 3searchceladdress
2ABBC$C$5Row5
3BDDColumn3
4BDE
5DEC
6EFA
Blad1
Cell Formulas
RangeFormula
E2E2=IF(COUNTIF(A2:C6,D2)=0,"Not Found",IF(COUNTIF(A2:C6,D2)>1,0,ADDRESS(MAX(INDEX(ROW(A2:C6)*(A2:C6=D2),0)),MAX(INDEX(COLUMN(A2:C6)*(A2:C6=D2),0)))))
G2G2=MAX(INDEX(ROW(A2:A6)*(A2:C6=D2),0))
G3G3=MAX(INDEX(COLUMN(A2:C2)*(A2:C6=D2),0))
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,056
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
The question is a bit vague but perhaps something like the below (please make sure that you enter it as an Array formula)

Book1
ABCDEFGH
1Crit col 1Crit col 2Crit col 3Result ColumnLookup criteriaResult
269637619746180
3974422193
4906998163
5855152124
6363641145
7786966125
8436295163
9704657180
10875015123
11997731154
12518595139
13543312117
14625363136
15509787155
16864859187
Sheet1
Cell Formulas
RangeFormula
H2H2=INDEX($E$2:$E$16,MATCH(1,MMULT(--($A$2:$C$16=G2),TRANSPOSE(COLUMN($A$2:$C$16)^0)),0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

babettedv

New Member
Joined
Jan 27, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Thanks for your help, to further clarify -

Is it possible to look for F2 (bold) in a range over multiple columns AND multiple rows --> C7:Y12 (instead of C7:Y7)
I know this is not working, but can I use a different equation to get this result / search area

(see below by current working formula)
=SUMIFS(INDEX(INDIRECT("'"&F$1&"'!$C$8:$Y$100"),0,MATCH(F$2,INDIRECT("'"&F$1&"'!$C$7:$Y$7"),0)),INDIRECT("'"&F$1&"'!$B$8:$B$100"),$C3)
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,056
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

If neither of the answers provided is is giving the answer that you require can you post a small sample of your data including a manually entered result using the boards XL2BB addin please.
 

babettedv

New Member
Joined
Jan 27, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
The current formula:
- Formula G3 is working but only linked to one row (MATCH(F$2,INDIRECT("'"&F$1&"'!$C$7:$Y$7"))
- Formula H3 is a one I tried but not working.

Product Costing Master 251120.xlsx
ABCDFGH
1Mince GivMince Giv
2COSRaw Material CodeDescription 1Description 2Cost/kgMFPC050.V1MFPC048
3IngredientsRM008554M - EXCEL FIT HI-CEL 554M (METHYL CELLULOSE SOLUTION)10.950.1752#N/A
4IngredientsRM011.001SUPRO 500 - SOYA PROTEIN ISOLATE - SUPRO 500Soya protein2.95
CoS FLAT
Cell Formulas
RangeFormula
G3G3=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)
H3H3=SUMIFS(INDEX(INDIRECT("'"&H$1&"'!$C$8:$Y$100"),0,MATCH(H$2,INDIRECT("'"&H$1&"'!$C90:$Y90")&INDIRECT("'"&H$1&"'!$C91:$Y91")&INDIRECT("'"&H$1&"'!$C92:$Y92")&INDIRECT("'"&H$1&"'!$C93:$Y93")&INDIRECT("'"&H$1&"'!$C94:$Y94")&INDIRECT("'"&H$1&"'!$C95:$Y95"),0)),INDIRECT("'"&H$1&"'!$B$8:$B$100"),$C3)



And the same recipe in the column apply to multipe product codes
Product Costing Master 251120.xlsx
ABCDEFGHIJKLM
88
89
90 Product codes MFPC050.V1MFPC048
91MFPC057
92MFPC131
93MFPC060
94MFPC104
95MFPC106
96
97
98
Mince Giv
 

babettedv

New Member
Joined
Jan 27, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi Mark, your formula doenst seem to work here. Could you please help out somehow?
Would be amazing! Really stuck on this one!
 

babettedv

New Member
Joined
Jan 27, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Hi Mark,
Could you please check by formula in H3:

I am trying to find a product code in a different sheet which could be in multiple columns (of which the ingredients should SUM.

CoS FLAT
ABCFGH
Product Costing Master 251120.xlsx
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-
RangeFormula
Cell Formulas
G3:G5G3
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)


Mince Giv
BCDEFGHIJKL
Product Costing Master 251120.xlsx
88
89
90Product codesMFPC050.V1MFPC048
91MFPC057
92MFPC131
93MFPC060
94MFPC104
95MFPC106


Thanks!!
 

babettedv

New Member
Joined
Jan 27, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Please, is there anyone that could help me apply the following formula to my own data, as I dont get a value if I try
=INDEX($E$2:$E$16,MATCH(1,MMULT(--($A$2:$C$16=G2),TRANSPOSE(COLUMN($A$2:$C$16)^0)),0))

See my data above (column H3 doesnt give a value)

Highly appreciated!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,904
Messages
5,638,907
Members
417,058
Latest member
BRYCEPIETROWIAK

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