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

#### babettedv

##### New Member
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### mart37

##### Well-known Member
An example:
search in matrix.xlsx
ABCDEFG
2ABBC\$C\$5Row5
3BDDColumn3
4BDE
5DEC
6EFA
Cell Formulas
RangeFormula
G2G2=MAX(INDEX(ROW(A2:A6)*(A2:C6=D2),0))
G3G3=MAX(INDEX(COLUMN(A2:C2)*(A2:C6=D2),0))

#### MARK858

##### MrExcel MVP
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
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

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
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

Would be amazing! Really stuck on this one!

#### babettedv

##### New Member
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
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!

Replies
3
Views
347
Replies
6
Views
104
Replies
3
Views
80
Replies
1
Views
173
Replies
13
Views
68

1,130,129
Messages
5,640,286
Members
417,135
Latest member
zeusmining

### 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.

### Which adblocker are you using?

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

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