Error when using Index Match formula

Preacherman771

New Member
Joined
Jun 15, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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),"")
AD7:AD10AD7=IF(T10<>T9,FILTER($R$7:$R$22,$T$7:$T$22=$T10),"")
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
Joined
Jun 12, 2014
Messages
65,850
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 2, 2008
Messages
37,776
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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?
 
Solution

Preacherman771

New Member
Joined
Jun 15, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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!!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,850
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Forum statistics

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