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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,949
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,562
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
63,949
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Forum statistics

Threads
1,144,281
Messages
5,723,473
Members
422,499
Latest member
think say

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