# 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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### 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
5
Views
144
Replies
4
Views
55
Replies
1
Views
481
Replies
5
Views
420
Replies
1
Views
192

1,181,449
Messages
5,929,978
Members
436,711
Latest member

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