# Error when using Index Match formula

#### Preacherman771

##### New Member
I am stumped when using an INDEX MATCH formula which I have used many times. I am trying to find the value of two given items, "2" & "Bal". When using =INDEX(\$AA\$8:\$AP\$8,MATCH(1,(AD\$15=\$AA\$7:\$AP\$7)*(\$Z8=\$Z\$7:\$Z\$14),0)), as I have always, I receive "N/A" as the result. I would appreciate a fresh set of eyes to find what I am missing and/or writing wrong. I intend to once corrected, to include IFERROR in order to catch the blank cells.

Cell Formulas
RangeFormula
AA7AA7=IF(T7<>T6,FILTER(\$R\$7:\$R\$22,\$T\$7:\$T\$22=\$T7),"")
AB7AB7=IF(T8<>T7,FILTER(\$R\$7:\$R\$22,\$T\$7:\$T\$22=\$T8),"")
AC7AC7=IF(T9<>T8,FILTER(\$R\$7:\$R\$22,\$T\$7:\$T\$22=\$T9),"")
AE7AE7=IF(T11<>T10,FILTER(\$R\$7:\$R\$22,\$T\$7:\$T\$22=\$T11),"")
AF7AF7=IF(T12<>T11,FILTER(\$R\$7:\$R\$22,\$T\$7:\$T\$22=\$T12),"")
AG7AG7=IF(T13<>T12,FILTER(\$R\$7:\$R\$22,\$T\$7:\$T\$22=\$T13),"")
AH7AH7=IF(T14<>T13,FILTER(\$R\$7:\$R\$22,\$T\$7:\$T\$22=\$T14),"")
AI7AI7=IF(T15<>T16,FILTER(\$R\$7:\$R\$22,\$T\$7:\$T\$22=\$T15),"")
AJ7:AJ8AJ7=IF(T16<>T15,FILTER(\$R\$7:\$R\$22,\$T\$7:\$T\$22=\$T16),"")
AK7AK7=IF(T17<>T16,FILTER(\$R\$7:\$R\$22,\$T\$7:\$T\$22=\$T17),"")
AL7AL7=IF(T18<>T17,FILTER(\$R\$7:\$R\$22,\$T\$7:\$T\$22=\$T18),"")
AM7AM7=IF(T19<>T18,FILTER(\$R\$7:\$R\$22,\$T\$7:\$T\$22=\$T19),"")
AN7AN7=IF(T20<>T19,FILTER(\$R\$7:\$R\$22,\$T\$7:\$T\$22=\$T20),"")
AO7AO7=IF(T21<>T20,FILTER(\$R\$7:\$R\$22,\$T\$7:\$T\$22=\$T21),"")
AP7AP7=IF(T22<>T21,FILTER(\$R\$7:\$R\$22,\$T\$7:\$T\$22=\$T22),"")
AA15:AP15AA15=IF(COLUMNS(\$AA\$15:AA15)+COUNTIF(\$AA\$7:AA7,"")>=17,"", IF(AA\$7<>"",(IF(COUNTIF(\$Z\$15:Z15,AA\$7)<>1,AA\$7,INDEX(\$AA\$7:\$AP\$7,SMALL(IF(\$AA\$7:\$AP\$7<>"",COLUMN(\$AA\$7:\$AP\$7)-COLUMN(\$AA\$7)+1),AA6)))), INDEX(\$AA\$7:\$AP\$7,SMALL(IF(\$AA\$7:\$AP\$7<>"",COLUMN(\$AA\$7:\$AP\$7)-COLUMN(\$AA\$7)+1),AA6))))
AA16:AL22AA16=INDEX(\$AA\$8:\$AP\$8,MATCH(1,(AA\$15=\$AA\$7:\$AP\$7)*(\$Z8=\$Z\$7:\$Z\$14),0))
Dynamic array formulas.

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Fluff

##### MrExcel MVP, Moderator
Not entirely sure, what you are tring to do with that formula, but does this do what you want.
Excel Formula:
``=INDEX(\$AA\$8:\$AP\$14,MATCH(\$Z8,\$Z\$8:\$Z\$14,0),MATCH(AA\$15,\$AA\$7:\$AP\$7,0))``

#### RoryA

##### MrExcel MVP, Moderator
Match only works with a 1 column/row range (or array). Your criteria is multiplying a vertical array by a horizontal one, which creates a 2 d array. It looks to me like you want a regular INDEX(..,MATCH(), MATCH) formula?

#### Preacherman771

##### New Member
Walked away and relooked at my situation and discovered the error. Since I'm indexing the row which is the value "2", I don't need to MATCH it. INDEX(\$AA\$8:\$AP\$8,MATCH(AA\$15,\$AA\$7:\$AP\$7,0)). Duh!!!

#### Preacherman771

##### New Member
Thanks for all of the replies.

#### Fluff

##### MrExcel MVP, Moderator
Glad you sorted it & thanks for the feedback.

Replies
13
Views
231
Replies
5
Views
116
Replies
0
Views
253
Replies
5
Views
74
Replies
2
Views
119

1,147,960
Messages
5,744,047
Members
423,843
Latest member
alex2022

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